UPDATED: April 20, 2018 (fixed links and removed some outdated content)
A common discussion tends to be around backup/restore so I’ve generally found that there just isn’t a great single place where a bunch of resources are listed. Many of these are my resources or Paul’s (blog|twitter). Enjoy!
Classes to learn more about backups and disaster recovery:
- These resources are much more current (best in this order):
- Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log
- Pluralsight: SQL Server: Understanding and Performing Backups
- Pluralsight: SQL Server: Detecting and Correcting Database Corruption
- Pluralsight: SQL Server: Advanced Corruption Recovery Techniques
- 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
- 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
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 great resources out there – sometimes they can be difficult to find. In fact, it took me a few hours to bring everything together here… and, in all honesty, there’s even more out there!
Thanks for reading,