SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


When is a Backup Considered Successful?


When is a Backup Considered Successful?

Author
Message
george sibbald
george sibbald
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44318 Visits: 13705
SQL Noob (6/8/2009)
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


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.

---------------------------------------------------------------------
Andrew Peterson
Andrew Peterson
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 753
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.

The more you are prepared, the less you need it.
Ray Mond
Ray Mond
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2825 Visits: 1544
Things you should be aware of re. RESTORE VERIFYONLY here.

Verifying SQL Server backup files on a file server on a budget here.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Bombardier
Bombardier
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 135
Thanks for this good article.
How can i made a restore test process and certify that my restoration is ok ? A sp_helpdb ?
Ray Mond
Ray Mond
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2825 Visits: 1544
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.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Chadwick-788357
Chadwick-788357
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 170
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.
djjwu
djjwu
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 226
very good article. it helps dba to streamline the backup and recovery process. thanks
kathy-1110468
kathy-1110468
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
Great article with considerations that should be obvious, but are good reminders.

Kathy Miranda
InStock, Inc.
www.instockinc.com
vliet
vliet
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 790
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search