SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Steven Bouffard / When is a Backup Considered Successful? / Latest Posts

RE: When is a Backup Considered Successful?

vliet — Fri, 12 Jun 2009 05:19:31 GMT

Great article! I will use it to uncover flaws in the back-up procedure at the various companies I perform regulare DBA tasks, as most of it not only applies to database back-ups but also to file back-ups as well. Thank you for all those sweet reminders.

RE: When is a Backup Considered Successful?

kathy-1110468 — Tue, 09 Jun 2009 11:40:58 GMT

Great article with considerations that should be obvious, but are good reminders.Kathy MirandaInStock, Inc.www.instockinc.com

RE: When is a Backup Considered Successful?

djjwu — Tue, 09 Jun 2009 08:38:13 GMT

very good article. it helps dba to streamline the backup and recovery process. thanks

RE: When is a Backup Considered Successful?

Chadwick-788357 — Tue, 09 Jun 2009 07:17:27 GMT

I don't have to worry about this because at my company we have a separate Disaster Recovery location with twin servers for all our production servers. I transfer and restore the weekly Full backups to the DR SQL servers. Every other day of the week, I transfer and restore Differential backups. Therefore, if there's ever a problem with restoring any of the backups, I'll know right away! So far, I haven't seen any issues. Typically, SQL Server backs up DBs reliably.

RE: When is a Backup Considered Successful?

Ray Mond — Tue, 09 Jun 2009 00:38:13 GMT

Use DBCC CHECKDB. Preferably before the backup too. If after the restore, DBCC CHECKDB returns consistency errors, you can at least tell if the error was because the database was inconsistent to start with, or because the backup file was damaged between the time of backup and restore. Without a DBCC CHECKDB before the backup, you can't tell.Or if using SQL 2005 or later, using the CHECKSUM option during the backup process will also cause SQL Server to verify page checksums where present. Not as informative as DBCC CHECKDB, but still checks the consistency of your data.

RE: When is a Backup Considered Successful?

Bombardier — Tue, 09 Jun 2009 00:24:25 GMT

Thanks for this good article. How can i made a restore test process and certify that my restoration is ok ? A sp_helpdb ?

RE: When is a Backup Considered Successful?

Ray Mond — Tue, 09 Jun 2009 00:21:24 GMT

Things you should be aware of re. RESTORE VERIFYONLY [url=http://www.sqlbackuprestore.com/verifyingbackupfiles.htm]here[/url].Verifying SQL Server backup files on a file server on a budget [url=http://www.sqlbackuprestore.com/verifyingbackupfiles_budget.htm]here[/url].

RE: When is a Backup Considered Successful?

Andrew Peterson-472853 — Mon, 08 Jun 2009 19:11:50 GMT

All good ideas. A full onsite and offsite process should be developed, documented and implemented, including testing. And not just for the database, but for the entire application(s).depending on the business nature of the database, the size, and any reporting needs, I setup a report server where I can both test the backups automatically, and build reporting databases.

RE: When is a Backup Considered Successful?

george sibbald — Mon, 08 Jun 2009 16:50:18 GMT

[quote][b]SQL Noob (6/8/2009)[/b][hr]there is a constant tape/disk battle here and i've used both. i'll take tape any day over disk.in the last 9 years i've seen less than 5 tapes go bad over time. in the same time i've seen servers and disk systems die a lot more often. and with disk if the disk dies than all backups on the disk are gone.we backup all of our SQL to tape via netbackup and the database agents. works very nice. for periodic restores we do them to QA and once in a while we call back tapes from offsite and restore them as well[/quote]I think most people would prefer to backup to disk and then copy that backup to tape. Either way you want an on-site and an off-site copy.

RE: When is a Backup Considered Successful?

Steve-3_5_7_9 — Mon, 08 Jun 2009 13:22:54 GMT

The RedGate backup will also validate the file.

RE: When is a Backup Considered Successful?

alen teplitsky — Mon, 08 Jun 2009 12:50:40 GMT

not sure about native SQL backups, but with Netbackup you can do page verification during the backup and restore if you have it set up on the database

RE: When is a Backup Considered Successful?

cy-dba — Mon, 08 Jun 2009 12:19:05 GMT

Also, it's usually a good idea to run DBCC CHECKDB after restoring databases from backups.

RE: When is a Backup Considered Successful?

alen teplitsky — Mon, 08 Jun 2009 09:18:09 GMT

there is a constant tape/disk battle here and i've used both. i'll take tape any day over disk.in the last 9 years i've seen less than 5 tapes go bad over time. in the same time i've seen servers and disk systems die a lot more often. and with disk if the disk dies than all backups on the disk are gone.we backup all of our SQL to tape via netbackup and the database agents. works very nice. for periodic restores we do them to QA and once in a while we call back tapes from offsite and restore them as well

RE: When is a Backup Considered Successful?

Steve-3_5_7_9 — Mon, 08 Jun 2009 08:32:56 GMT

Thanks for the value added comments. They're all worth appending into the article.

RE: When is a Backup Considered Successful?

OCTom — Mon, 08 Jun 2009 07:41:52 GMT

Nice article. What good does a backup do if it can't be restored? None. A disaster is not the time to find your backup plan is useless. I would also add figuring out the timings so you know how long it will take to get the tapes and do the restore. Let everyone know it so there are no expectations that can't be met. And, test it at the most inconvenient time to see how long it takes to get it from the offsite vendor.

RE: When is a Backup Considered Successful?

Noel McKinney — Mon, 08 Jun 2009 07:41:41 GMT

Good job mentioning the offsite tape testing. It's a good idea to get at least an annual test in the budget and make sure that the time to recovery doesn't increase over time. If it took 14 hours last year and takes 18 hours this year, then you need to analyze what happened in the chain... did someone forget where they had the contact information, were the restore instructions out-of-date because of server changes, etc.

RE: When is a Backup Considered Successful?

Grant Fritchey — Mon, 08 Jun 2009 05:36:24 GMT

Good article.You could have carried it a bit further. Including RESTORE VERIFYONLY as a part and parcel of your backup routines adds to the overall confidence you have in your backups. You also didn't mention getting consistency checks in place to run before full backups to ensure what's being backed up is more likely to restore.Still, this is important information to get out to people and you made a good start. Well done.

RE: When is a Backup Considered Successful?

SAT_SQL — Mon, 08 Jun 2009 04:38:46 GMT

Hi It is a really a good Article for the biginers as like me . and good ideas u have given Regardsksr

When is a Backup Considered Successful?

Steve-3_5_7_9 — Mon, 08 Jun 2009 00:18:25 GMT

Comments posted to this topic are about the item [B]When is a Backup Considered Successful?[/B]