June 22, 2012 at 7:40 am
Hey Guys,
This is more of a best practice question...
There is some debate among our DBA group and was wondering if you guys had any insight on the topic.
We are contemplating restoring some of our backups onto a test box every day just to verify that we CAN actually restore them. Our SQL Server DBAs (myself included) feel this a good idea - better safe than sorry. However, some of our more seasoned (DB2) DBAs have never even considered doing this and feel this is a waste of time. Their argument is why would you even take these nightly backups in the first place if there was a chance that they would not work - and if the Backup job completes successfully then it SHOULD be able to restore with no problems.
I have heard stories of some shops test-restoring every SQL server backup they take just so they know for sure they can restore from that backup if called to do so.
I personally don't think we NEED to test-restore EVERY backup, but I do think we could develop a system that randomly selects x number of backups a day (from a predetermined sample size) and test restore them to a dev or test box to have a certain confidence level that ALL backups were successful.
What are your feelings on this? Is it necessary to test restore backups or is it just a waste of time?
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 22, 2012 at 7:52 am
Testing is important. Remember; backups are worthless, restores are priceless.
Ages ago I worked as a computer operator. We backed up our databases to 9 track tapes. I have seen a backup tape fail from the time a backup was taken to when it was verified 15 minutes later, and our verification was a restore to a reporting server.
Same thing can happen to files on disk, a sector can go bad corrupting the file. You can also get IO errors that cause the file to be corrupted while it is being written. Should you test every backup, that would depend on how paranoid you are or how critical it is to know that a backup can be restored.
June 22, 2012 at 9:39 am
So DB2 never, ever has a backup that they're unable to restore? Wow! that's pretty cool.
Oh, wait[/url]. Well that was just one... ummm... Well it's... ahhh...[/url] .... Nevermind.
And that was just 20 seconds of Boogling or Gingling or whatever we're calling it these days.
Yes, backups can fail, files can be corrupted, users can make mistakes when they haven't run restores in recent memory. All these things absolutely can occur. Setting up tests for your restores is a good way to ensure that the backups are good and that you know how to restore the databases. I've written[/url] a lot about this topic[/url]. Failed backups have put people out of business[/url].
Personally, I test mine.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 22, 2012 at 9:58 am
Lynn - thanks for the input. I'm going to use this quote going forward: "backups are worthless, restores are priceless."
Grant - Awesome articles and examples. Gives me some ammunition to use to argue my side.
We are probably going to start just by restoring 5 or so different random backups a day and then build from there.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 25, 2012 at 3:03 am
In addition to the valid points put out above, test restoring your databases also gives you a very good idea as to how long it actually takes to do a restore of your databases which can be great information to have if you ever have to do an emergency restore.
June 25, 2012 at 8:27 am
They mentioned on the webinar number 18 (See Training Section - Previous Webinars) that performing test restores also gives you the practise at the task as well as verifying the backup. If you need to perform a restore on your live database you will be more confident on the process.
June 25, 2012 at 9:01 am
Depending on the work schedule doing every day idea(if it is possible) is not a bad idea,I would recommend weekly test restores.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
June 25, 2012 at 9:35 am
We restore the bulk of our DBs daily from a DR copy - might be my pessimistic nature but I'd prefer to know my backups are good rather than assuming it.
_____________________________________________________________________
- Nate
June 26, 2012 at 7:10 am
Someone -- Grant Fritchey maybe? -- once said something that has stuck with me: you don't need a backup strategy or plan....
You need a restore plan!!
I get failed backups more often than I'd like: not from SQL server, but from our POS tape backup software that fails to run the network backup job about 4-8 times a year. Now that may not be your scenario that you'd be testing, but I would suggest that you might also consider restoring the SQL backups from tape and then doing a DB restore to SQL, at least occasionally. Your sysadmin runs that tape system for a reason, right? I've had to develop my own CLR to check that the SQL backups actually made it onto tape, and that is a daily check in one of my SQL agent jobs.
I like to think of backups like electrical wiring: a fault anywhere in the circuit means the circuit fails. Period. And by circuit, I mean both legs of the circuit: the backup (+) and the restore (-). If you only have the (+) leg, you don't really have a circuit, do you? 😀
When I was starting out, I also found the restore process reassuring, to know I could wipe out a production database and restore it properly, to a specific point in time, from the command line or with the GUI. You don't want to be doing this for the first time when someone drops your production DB, do you?
This isn't really about SQL Server vs. DB2. It's about systems testing. It's about a mindset that, until you prove it all works as designed and as intended, it might as well not exist, b/c you cannot rely on it. Doveryai, No Proveryai!
Good luck, and post back with what happens,
Rich
June 26, 2012 at 1:16 pm
Thanks everyone - all good advice.
One of the issues we face is that we only have a 1Gbit network connection for our backup servers. It can take quite a long time to backup/restore some of our larger databases (I'm talking many hours). We are in talks to try and get moved onto a 4Gbit line that would greatly increase the speeds. Have a meeting coming up soon to discuss this issue with some of the server people.
I determined that we can test-restore 5 random backups a day and have a 95% confidence level that ALL backups can be successfully restored (This is assuming that generally 99% of all restores will work - and we are making this assumption for the time being). If we are able to increase the network speed limitation we will probably start to test-restore more databases.
How long does it typically take you guys to backup a 1 TB database? And then how quickly can you restore that backup?
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 26, 2012 at 1:22 pm
J Good (6/26/2012)
Thanks everyone - all good advice.One of the issues we face is that we only have a 1Gbit network connection for our backup servers. It can take quite a long time to backup/restore some of our larger databases (I'm talking many hours). We are in talks to try and get moved onto a 4Gbit line that would greatly increase the speeds. Have a meeting coming up soon to discuss this issue with some of the server people.
I determined that we can test-restore 5 random backups a day and have a 95% confidence level that ALL backups can be successfully restored (This is assuming that generally 99% of all restores will work - and we are making this assumption for the time being). If we are able to increase the network speed limitation we will probably start to test-restore more databases.
How long does it typically take you guys to backup a 1 TB database? And then how quickly can you restore that backup?
The largest database I worked on was approximately 70GB and the backup to a local SAN resource was under 10 minutes. Restore from same was also under 10 minutes.
July 25, 2012 at 8:35 am
What are some of the ways that you guys go about test-restoring your backups?
Is it automated or is it all manual? If it is automated what tools or scripts do you use to accomplish the restores?
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply