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 |
|
jazzcatone
Starting Member
12 Posts |
 Posted - 2010-06-16 : 16:33:21
|
| I am running a Full Recovery Model with Full backups every 12 hours, Differential Backups every hour, and transaction log back ups every 15 minutes. Scenario: Lets say at 3:48pm I find out something happened and I think I need to roll back to the state of the db at 3:13pm. What would the restore process be? (I think I know this already, but want to be sure) Then after doing that I find out I actually need to restore to 3:25pm. What is the process then ?Am basically trying to understand how to keep things transactionally consistent so my restore process works. Can anybody point me to a resource that explains the process in plain English as I am finding some of the stuff on msdn sort of confusing.Jason |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-16 : 18:00:54
|
| "I need to roll back to the state of the db at 3:13pm"Restore:Full backup from 12:00pmDifferential from 3pmTLog backups from 3PM and 3:15pm, using STOPAT 3:13Then use RESTORE RECOVERY to make the DB liveFor 3:25pm repeat as except:...TLog backups from 3PM, 3:15pm and 3:30pm, using STOPAT 3:25" Can anybody point me to a resource that explains the process in plain English "Here's what I wrote 6 years ago - Blimey! - when I realised how backups worked. Hopefully some use to you!http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37825 |
 |
|
|
jazzcatone
Starting Member
12 Posts |
Posted - 2010-06-16 : 19:31:39
|
| Thank you for your replies once again. Kristen the link was very helpful. Tara to your point concerning differentials I guess I would ask how do I know what is overkill then? The reason I was thinking hourly differentials was basically that msdn was saying that having differentials can greatly cut down on the time it takes to restore a database. Granted, those differentials would need to be managed and could take a lot of disk space but assuming I can manage this somehow, why would hourly diffs be too much? Again, just asking because I am new to this.Jason |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 02:44:22
|
| There is some CPU & Disk effort to make a DIFF backup. That may impact performance of your database.Perhaps make a test:Restore (to a new, temporary database!!) a Full Backup (WITH NORECOVERY) and then restore ALL Tlog backups for a 24 hour periodHow long does the TLog restore take?That's your "recovery time"Probably better would be to time how long the restore of each hour's TLogs takes - your business probably has busy times of the day - so that would tell you the recovery time for each hour of the day.Then restore the Full backup again (WITH NORECOVERY), and restore the Diff backup from, say, 12 hours after the Full Backup. How long does that take?If the difference between the Differential backup Restore and the TLog Restores is small then no point having DIFF backups tooMy gut feeling is that a DIFF backup at midpoint between daily FULL backups is a good compromise ... but ... like many things! ... "It depends" and I think a test of recovery times is what will tell you for sure. |
|
|
|
jazzcatone
Starting Member
12 Posts |
Posted - 2010-06-17 : 11:10:30
|
| Thanks Kristen. One more question if possible, it maybe a stupid question so bare with me. I was considering having my maintenance plans append to the existing .bak when my full backups run. Was considering this primarily as a way to get my backups while managing the disk space. However, if this bak gets deleted, am I not pretty much screwed in terms of restoring to a point in time then ? Your example with the developers taking a "safety copy" (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37825) sort of suggests so I would think ???? Any advice would be much appreciated.Jason |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 19:06:26
|
| The only stupid question is the one you don't ask!We never append to existing backup files. As you say it makes it harder to know when a backup file can safely be deleted (as no longer containing useful data, or retention no longer required because it is on tape)I think it makes the restore a bit more complicated too - not only do you need to locate the right file, you need to locate the correct backup within it.My simple brain says it doesn't save you any disk space (just fewer files), but it does make the retention-housekeeping more complicated |
|
|
|
|
|
|
|
|
Understanding how SQL Server Back ups Works