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 |
|
teepee
Starting Member
6 Posts |
 Posted - 2006-01-17 : 12:21:29
|
| I have a SQL2000 database, with the following backup strategy:Time/type:0 complete db backup1 diff. db backup (overwrite)2 diff. db backup (overwrite)3 diff. db backup (overwrite)4 Transaction log backup5 diff. db backup (overwrite)6 diff. db backup (overwrite)7 diff. db backup (overwrite)8..repeat 0-7... 0=8, 1=9 etc.What to restore if the system fails at 2.5? 4.5? 6.5? (2.5 means between 2 and 3, etc.))Do you always start at the latest complete backup (time: 0,8,16 in this example)?How do I do the restore from Enterprise Manager?Is the backup strategy ok? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-01-17 : 23:47:15
|
| 0 - full restore2.5 - full restore, then diff4.5 - full restore, diff, then transaction log backup6.5 - full restore, then diffetc, etc, etcThere's one big factor here. If you are overwriting your complete db backup with a diff, then you are screwing yourself basically. You CANNOT restore a diff without the previous full (complete). You can overwrite diffs, but with your current strategy, you are making it so you can only restore the latest diff since the rest are overwritten. That is unless you are not overwriting. If you are appending your files (not overwriting), I would change the strategy anyway. Just use seperate files for everything. If you have one corruption with an append strategy, you lose the entire backup set usually. That's not a smart strategy.It's hard to say if the rest of the backup strategy is okay. It depends on what your business has signed off on as an acceptable risk level for the amount of money the would need to spend.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-18 : 01:37:14
|
Note that if your DIFF is broken you can also do:4.5 - full restore, diff, then all subsequent transaction log backup(Or use Full Restore, then an earlier DIFF [i.e. earlier than the "most recent"] and restore "more" Transaction backups)Kristen |
|
|
|
teepee
Starting Member
6 Posts |
Posted - 2006-01-18 : 03:28:01
|
| Super. Thx. My diffs are placed in a different directory (1 db = 1 file only) and we are actually using overwrite because merge takes too much space on our disks. Any reason to backup transaction log then? Seems like anything can be saved with diff. backups and a complete backup? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-18 : 04:10:08
|
"Any reason to backup transaction log then"The differential will get bigger each time - until you do the next FULL.Transaction Backup will just include the transactions since the previous Trans backup (i.e. incremental). So you should be able to store more "elapsed time" backups using transaction backup.Transaction Backups can be restored to a "point in time", which you can't do with Full/Diff alone. So say you have 10 transaction backups since the last Full/Diff you can choose to restore just 8 of them, say, and then final 8th one you can restore just up to a particular time (e.g. when you accidentally did "DELETE FROM MyImportantTable"  )Kristen |
|
|
|
teepee
Starting Member
6 Posts |
Posted - 2006-01-18 : 06:26:35
|
| Thx. This has been more helpful than any book I have on the subject. The books all say how to backup/restore but not really any details one what to do when aso. Thx. |
|
|
|
|
|
|
|
|
Restore Sql 2000