Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Zoroaster
Aged Yak Warrior
702 Posts |
 Posted - 2007-08-29 : 11:24:01
|
| If you are using full recovery mode and have a scheduled weekly backup and multiple daily transaction log backup. What effect does it have in your ability to restore the database if someone comes in and does an adhoc backup? Does this disrupt the sequence, and what if the backup is removed from the server and placed locally on that persons machine, would I still be able to restore? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 11:30:57
|
If the ad hoc backup is a Full or Differential you are fine.You could go aback and restore a FULL from, say, a Year ago and EVERY Transaction Backup since and that would be OK.However, if they made a Transaction Backup you would be hosed.Also, if they made a FULL backup and you were relying on DIFFERENTIAL backups you are then hosed - in the sense you will have to go back to a FULL & DIFF backup BEFORE their Full backup, and then ALL TLog backups thereafter. That's fine, of course, but it could be more "restores" than you had planned for, and in a Disaster Recovery scenario might slow down your ability to get going again.We have a Stored Procedure that makes backups. It takes the DB name, backup type (Full, Diff and Tlog) and the retention period.Our developers etc. can use this to make a backup whenever they want to - e.g. if they want to take a DIFF or TLog backup just before some "dangerous"  procedure just-in-case ... usually so that Recovery is quicker/easier, if required.That Sproc is also used by the routine backups. All files generated are stored in a known-location, logged to a Logging table, and so on. So they never need to use Enterprise Manager etc. to make their backups.Also, all backups (and Restored) are logged in MSDB, by SQL Server, so you can interrogate that to find out if additional, unexpected, backups are being made. Someone posted a thread recently where some well-meaning 3rd party stuff was making backups, and kiboshing their disaster recovery plans!!See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,BackupKristen |
 |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-08-29 : 11:38:49
|
quote:
Originally posted by Kristen
If the ad hoc backup is a Full or Differential you are fine.You could go aback and restore a FULL from, say, a Year ago and EVERY Transaction Backup since and that would be OK.However, if they made a Transaction Backup you would be hosed.Also, if they made a FULL backup and you were relying on DIFFERENTIAL backups you are then hosed - in the sense you will have to go back to a FULL & DIFF backup BEFORE their Full backup, and then ALL TLog backups thereafter. That's fine, of course, but it could be more "restores" than you had planned for, and in a Disaster Recovery scenario might slow down your ability to get going again.We have a Stored Procedure that makes backups. It takes the DB name, backup type (Full, Diff and Tlog) and the retention period.Our developers etc. can use this to make a backup whenever they want to - e.g. if they want to take a DIFF or TLog backup just before some "dangerous" procedure just-in-case ... usually so that Recovery is quicker/easier, if required.That Sproc is also used by the routine backups. All files generated are stored in a known-location, logged to a Logging table, and so on. So they never need to use Enterprise Manager etc. to make their backups.Also, all backups (and Restored) are logged in MSDB, by SQL Server, so you can interrogate that to find out if additional, unexpected, backups are being made. Someone posted a thread recently where some well-meaning 3rd party stuff was making backups, and kiboshing their disaster recovery plans!!See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,BackupKristen
Thank you very much, this is good information. |
|
|
|
|
|
|
|
|
Backup question