Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

Authors

Search

XML Feeds

Google Ads

« SQL Server 2008 R2 Editions Pricing AnnouncedDon't prefix your table names with tbl »
comments
Rate Post:
submit to reddit Digg!FacebookDotnetkicks

A major point of failure in a full recovery model is not testing your backups. You should not limit this to your FULL backups either. If you have full recovery model on any of your databases there is more than likely (or should be) log backups being run based on either DR strategies or transaction log management. If you also have differential backups to take full advantage of the quickest path to restore, then all of these stages must be tested out.

Key points

  1. Test restoring full backups
  2. Test applying differential and transaction log backups
  3. Check header information on all backups. To do this you can submit to a table for historic purposes the results from RESTORE WITH HEADERONLY, "RESTORE HEADERONLY FROM DISK = BackupFile.bak'"
  4. Push testing further than one time restore. Automate your RESTORE tests either daily, weekly or what your resources permit. Remember, just because you go on vacation does not mean your methods change and your duties go away.
  5. Document the results and let management know of those results with either simple reporting results of utilizing SSRS for subscription automation.

Testing your backups will expose problems that will save you when your backup strategies are put to the test in a real life disaster that your normal DR strategy fails to recover from. The last thing any DBA wants is to tell them the restore doesn’t work. As DBAs, securing the data is the number one priority in your list of duties and responsibilities. Backups are the fundamental method for doing just that.

Remember, if you don't test your backups regularly then you will end up testing them when their failure could hurt you the most.

About the Author

Ted Krueger has been working in development and database administration for 13+ years. Specialties range from High Availability and Disaster / Recovery setup and testing methods down to custom assembly development for SQL Server Reporting Services. Ted blogs and is also one of the founders of LessThanDot.com technology community. Some of the articles focused on are Backup / Recovery, Security, SSIS and working on SQL Server and using all of the SQL Server features available to create stable and scalable database services. Recent community contributions have been to organize SQL Saturday events in the Chicago-land to Milwaukee-land area for 2010 where he will also be speaking about various SQL Server topics. @onpnt
Social SitingsTwitterLinkedInLTD RSS Feed
534 views
submit to reddit Digg!FacebookDotnetkicks

Comments and Feedback

3 comments

Comment from: onpnt [Member] Email
Before my good friend Denis says it...

You should all know, "Backups are for sissies!!!" ;-)

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/backups-are-for-sissies
05/11/09 @ 07:47
Comment from: SQLDenis [Member] Email
Full recovery mode? What is that?
05/11/09 @ 08:21
Comment from: onpnt [Member] Email
blast typos! :-)
05/11/09 @ 08:24

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)