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 |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2015-02-08 : 00:28:15
|
Ok, so here is a question that after all my years I overlook.I do backups of all types, have point in time recovery on at least two different media devices going back years, and in a worst case scenario I have offsite data for each month... ALl sounds great right...Well, it all goes back to the source though of I am assuming my backup is good, as if my backup source (i.e. my t-sql script that backs up) is producing corrupted files than all this redundancy is well... crap!:)So... what is the answer, and how do I automate this. I started getting nervous when I had a crash the other day, and needed to do a restore, and in that 15 minute timeframe waiting for it to complete I started sweating going "Wait if all these backups are bad, I'm screwed"... Well thankfully the backup worked as it was intended and I quickly realized the source producing everything is a scary scenario. I am now going to script a 1x a week restore to a backup database to make sure the files restore properly. What is the best practice though, how do we all really know our backups are good... Same thing goes with my network data sure I have windows backups, raid drives, etc, but if lets say lighting hits my box and fries all the raids and now I need to RESTORE... Well sure it says it backedup and I have copies, but how do we really know that they work?Is the answer to setup dummy boxes to restore to, move virtual machines around and load on different computers? It's truly a scary scenario when you think about how much of your RESTORE options depend on those backup files that completed successfully to work 100% of the time.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-08 : 07:23:40
|
| Yes the answer is to automate a restore to a test machine. You should also do an occasional restore from tape or whatever your long term backup solution is. I would probably setup a few different types.1. Restore full + random point-in-time, couple of times per week2. Restore full + random point-in-time from tape, monthlyIn addition to the restore, I would run DBCC CHECKDB there. You can remove the process from production if you do the integrity checks on a restored test machine.This is an old blog post of mine that shows what I've used in the past (does not include PIT or tape): http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|
|
How do I know my Backups are good?