February 24, 2009 at 7:30 am
I've always believed that backups are the most important thing for any computer system, and that restores are the second most important. Being a DBA / database developer over the last 15 year for various companies both in house and in a contracting/consulting role, it never ceases to amaze me how many people don't understand that.
Where I currently work, we had a client call us to help them recover their SQL Server database because the data on their RAID array had becomce corrupt. Upon investigating, we learned they kept their database backups on the same drive as the data files. Not only did they not have any verify / restore policy for testing their backups, but they didn't even keep them in a safe place.
February 24, 2009 at 7:34 am
Jeff Moden (2/21/2009)
I absolutely agree... It's not what you can backup... it's what you can restore. I do at least one "practice" recovery a week and I'm not the System DBA. I do it because I'm pretty sure that they don't do it.
I don't know how feasible it is to do a practice restore on all our databases but it's something I should be doing more, I agree, maybe at least once a month and just draw up a schedule.
That being said, I'm curious as to how many people, do or do not check the verify backup box when setting up the maintainance plan or use RESTORE VERIFYONLY in their scripts. It has the potential to double my backup time sometimes (a bit less with third party backup software), but it's a given that all backups have verifications automatic with the job. But I bet that some folks skip that step (either accidentally or on purpose).
Gaby A.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
February 24, 2009 at 8:26 am
James Stover (2/24/2009)
We don't need no DBA 'round here checkin' our backups. We got that fancy Squeal Server that takes care of itself. Now git.
Isn't that the truth!
----------------------------------------------------------------------------
"No question is so difficult to answer as that to which the answer is obvious." - George Bernard Shaw
February 24, 2009 at 8:28 am
Present day:
system database backups are performed with the maintenance plan 'verify' on. We do not regularly restore the system databases because in our environment it is just as quick to reinstall.
As for user databases, my primary application databases are just a tad shy of 1 Tb each and the 2 ancillary databases are 1/4 Tb and 1/3 Tb respectively. We use LiteSpeed and do not verify - however there is an automated refresh to another server environment that occurs weekly. Next there are monthly scheduled refreshes of other environments and finally we receive adhoc refresh requests rather frequently.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 24, 2009 at 8:43 am
For SOX audit purposes, we are required to demonstrate a successful restore (from an offsite backup tape) every quarter.
But we restore data from disk for testing environments every 1-2 weeks. It's good practice too.
February 24, 2009 at 9:08 am
I agree with Andy that SQL Server backups are very reliable, and not usually the problem.
It is our backup/restore procedures that need to be tested.
You want to be confident that when a restore is called for you can do it easily because you do it regularly. I just restored a production database to test, not ten minutes before I started reading this article. We get these requests almost every day and many times more than one a day. I cannot remember when one failed on last night’s backup and I had to go to an older one.
Again it is the procedures that need to be tested and this will test the backups themselves well enough. Everybody’s situation is different, but we all need to have backup and restore procedures in place and work in our environment.
I’m surprised that nobody has talked about DBCCs.
We also must be sure that we are not backing up a corrupt database.
February 24, 2009 at 9:11 am
We have somewhat of a system in place where our staff (primarily project managers) request a backup restore for validation purposes on a quarterly basis. There are obvious drawbacks with this approach, so I am curious if anyone out there knows of a tool to automate the validation of backups on a regularly scheduled basis? I started to work on a script in Powershell to automate the restore of a recent backup and send the results to my email. Unfortunately, because of other collateral duties, I have not been able to complete this script, but am curious if anyone knows of a tool that already does this...
Anyone?
--
Juan F. Coy
February 24, 2009 at 9:53 am
juanfcoy (2/24/2009)
... I have not been able to complete this script, but am curious if anyone knows of a tool that already does this...Anyone?
--
Juan F. Coy
I actually used a script off of SQLServerCentral (modified for using Litespeed) and scheduled a job on our SQL 2005 Server to restore our production db nightly, from the disk backup, to a datawarehouse server. You might want to look in the archives on this site. I think the script required turning on the xp_commandshell, however, another recent article talks about using SQLCLR instead. I haven't got that far yet, but when I have time (ha!) I will.
Alan
Here is the article on SQLCLR:
http://www.sqlservercentral.com/articles/SQLCLR/65656/
and for the restore script, just search for dynamic restore and go through a few. I ended up piecing together a script from 3 different articles posted/linked on this site. Here is a link to one of those articles:
http://www.mssqltips.com/tip.asp?tip=1584
This site has a lot of info. I try to listen a little everyday.
February 24, 2009 at 10:15 am
In every place I've been a DBA, we're restored to test pretty regularly. Historically, I'd say the biggest hole in the backup process has been backing up to tape reliably. I've gone 3 months without a tape backup without knowing about it until after the fact.
February 24, 2009 at 10:20 am
JStiney (2/24/2009)
I agree with Andy that SQL Server backups are very reliable, and not usually the problem.
Don't forget media failures. Most of my restore failure experiences have been due to bad media.
February 24, 2009 at 10:23 am
Most places that I consulted for in my tours of duty @ various consulting agencies did not do verification... until it was time to do a restore For Real™. Most of the time, it worked so they didn't have to learn a painful lesson. Occasionally, they wound up going further back upstream for a less-than-ideal backup.
When it was my job to do the backups, they always got verified as part of the maintenance routine. Were I in an operations-oriented DBA position today, I'd still be doing exactly that. The test doesn't have to happen during your maintenance window (unless you really, really need to know it failed and run another backup immediately, even during prime time). The verification can (and should) be done on another non-production box.
If it's important enough to backup, it's important enough to test the restore as often as you backup...
😎
February 24, 2009 at 11:15 am
I am fairly strict on maintaining backups and verifying such. Unfortunately, in the last 5 years, I have run into a rash of clients and/or employers who do not supply test machines to be able to test restores. Some have. Those that don't invariably have been mixed DB server shops: MySQL based, with MS SQL Server for analysis only. Nearly all of the MySQL admins I have run into do not verify anything, and seem to rely heavily on restoration from logs, which happens far more frequently than it should... these are due to the fact that they are usually LINUX-based admins, and not DBA's.
I also have always never trusted tape, and always insisted on hard disk backups, duplicated via either RAID, hot swappable hard disks (for off site storage), or copy to a storage server (this as last resort as it is bandwidth intensive). It makes sense as hard disk storage is as (or more) cheap as tape (gig to gig), and considerably more reliable, speedier, greater storage space per item.
February 24, 2009 at 11:51 am
raymond.glaser (2/24/2009)
For us newbies - Would someone please give a hint as to how you verify a database backup ?
The easiest way to test a backup is to simply restore it on another SQL Server and see if you can access it. If you don't have another SQL Server, you can always restore it to the same server using a different database name. Of course, this is just the very basics. Backing up and protecting your data should be part of a larger disaster recovery plan.
Brad M. McGehee
DBA
February 24, 2009 at 11:55 am
For of those who use Redgate's SQL Backup product, would you be interested in seeing a new feature added to the product that would automate the full verification of backups (possibly including a DBCC CHECKDB test of the restored backup on another server)? Of course, such a feature couldn't possible test every possibility, but it could at least verify that you have a good backup immediately after it was made. I ask this because I have been talking to the SQL Backup deveopment team about this idea for a while, but so far, it hasn't gotten a lot of traction.
Brad M. McGehee
DBA
February 24, 2009 at 12:09 pm
raymond.glaser (2/24/2009)
For us newbies - Would someone please give a hint as to how you verify a database backup ?
Add a step to your maintenance plan/script:
RESTORE FILELISTONLY
FROM DISK = 'C:\Build_Automation\AdventureWorks2008Data.bak';
Be sure to change the file spec to match the path you backed up to...
If that works, you've at least got a backup file that you can open. It's a trivial cost, even for large backup files. Not as good as a full restore + DBCC, but it's better than nothing!
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply