Friday, April 02, 2010

Backup Resources - Where, oh where, can they be?

by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/2/2010 8:48:00 AM

In our Boston SQL Training workshop today we're discussing disaster recovery and minimizing data loss. One of the key discussions is around backup/restore so we starting discussing resources and found that there just isn't a great single place where a bunch of resources are listed (ok, maybe there is but we didn't find one) so, while Paul's (blog|twitter) lecturing for a bit - I thought I'd put together a list for you to use!

Webcasts to learn more about backups and disaster recovery:

• Check out my 10-part MSDN series and/or my 11-part TechNet series that both have some topics around backup/restore. Both series (and their associated blog posts) can be found here. Specifically, check out:
  • MSDN Series, Part 1: Creating a Recoverable Database (Level 200) [NOTE: This is recorded for SQL Server 2005 but still applies to SQL Server 2008.] 
  • MSDN Series, Part 2: Creating a Reliable and Automated Backup Strategy (Level 200) [NOTE: This is recorded for SQL Server 2005 but still applies to SQL Server 2008.]
  • TechNet Series, Part 10: Recovering from Human Error (Level 200) [NOTE: This is recorded for SQL Server 2005 but still applies to SQL Server 2008.]
• Check out some of our additional recordings that we've done at conferences such as TechEd EMEA (these are also on our webcasts page)
  • TechEd 2005: SQL Server 2005 VLDB Availability and Recovery Strategies (Level 300), presented by me - here.
  • TechEd IT Pro 2008: Surviving Database Corruption (Level 300), presented by Paul - here. 

Whitepapers:

• A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network
• Microsoft SQL Server 2008 Data and Backup Compression
• Protecting SQL Server with System Center Data Protection Manager 2007

Articles:

• TechNet: Understanding Logging and Recovery in SQL Server, written by Paul S. Randal
• TechNet: Understanding SQL Server Backups, written by Paul S. Randal
• TechNet: SQL Server: Recovering from Disasters Using Backups, written by Paul S. Randal
• Paul's entire TechNet articles series from his blog category TechNet Magazine
• SQL Server Magazine: Recovering from Isolated Corruption, written by me
• SQL Server Magazine: The Best Place for Bulk_Logged, written by me
• Kimberly's entire SQLMag articles from the author's page on SQL Server Magazine

Additional blog posts/topics:

• Yes, you can benefit from both data and backup compression by fellow SQL Server MVP, Aaron Bertrand (blog|twitter)   
• What about maintenance automation? Check out Ola Hallengren's "Backup, Integrity Check and Index Optimization" blog post here.
• Paul's blog category on backup/restore here.
• Kimberly's blog category on backup/restore here.
• SQLCAT: Tuning the Performance of Backup Compression in SQL Server 2008
• SQLCAT: Tuning Backup Compression Part 2
• SQLCAT: Backup More Than 1GB per Second Using SQL2008 Backup Compression
• SQLCAT: A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network

Some other interesting issues - what about the application ecosystem (if you're moving to another instance or even another physical server):

• Logins, etc.? Check out this KB article to help you migrate the logins: How to transfer logins and passwords between instances of SQL Server.
• What about server-level roles? While the KB article helps you migrate logins to the secondary server - what about any role membership that you've set? These are NOT migrated! This is something that you may want to prepare beforehand. (thanks to fellow SQL Server MVP, Aaron Bertrand (blog|twitter) for the great discussion!)
• However, even if you get the logins migrated and the system roles scripted, there any other issues that you could run into! What about jobs, operators, service accounts, external batch files, executables - anything and everything that's not *in* the database but keeps that database "ticking" that's NOT backed up nor restored (er, except for filestream - which is external but *IS* backed up by default). So, these are additional things to prepare and test so that your disaster recovery strategy succeeds. 

So, while there are a lot of resources out there - sometimes they can be difficult to find. In fact, it took me about 2 hours to bring everything together here and most of the resources were mine or Paul's... and, in all honesty, there's even more out there!

Enjoy!

Thanks for reading,
kt