SQL Server Backup
Best Practices and Articles
Here’s what we’ve put together over the years to protect your data.
- SQL Server Backup Best Practices – our own best practices for backing up databases from the 10+ years I’ve been doing it.
- Free Scripts to Test Your Backups – find out if all databases are being backed up, if we’re backing up corrupt data, and more.
- Back Up To The Network, Not Local Disk – Brent explains why you shouldn’t write your SQL Server backups to the same disks your data lives on.
- Back Up Transaction Logs Every Minute – sounds crazy, but it’s true.
- 5 Questions About Your Backup Strategy – Kendra tests your plan.
Advanced Backups: Compression and Corruption
- Restoring a Subset of Tables – Kendra gives you several options to restore a few of your tables back to an earlier point in time.
- No, Compressed Backups Don’t Check for Corruption – Books Online says they do, but Brent’s demo code proves that they don’t.
- Improving Backup Performance – Jes shows how to use differentials and striping to get smaller maintenance windows. But before you implement them, read…
- The Danger of Differentials – Brent explains why differentials might be too much for your team to handle.
- Restoring and Refreshing Dev/Test/QA – Brent describes his ideal dev/test/QA/staging environments.
- Cosmo Quiz: Backups and Restores – 5 questions to find out how much you know about backups.
- MSDB is a Backup Bottleneck – if you’re not cleaning out your backup history tables, they could be slowing down your backups.
- How to BACKUP LOG with TRUNCATE_ONLY – this feature has been deprecated in SQL Server 2008 and R2, but it’s still available through a sneaky back door.
- Where to Run DBCC for AlwaysOn AGs – it’s not enough to run DBCC on the primary. It’s absolutely critical to run it on the node where you’re doing backups.
Backup Training Videos
- Backup to Basics – Jeremiah explains SQL Server’s backup options and makes you design recovery plans for a few sample companies.
- Log Shipping Part 1 – Preparing for Disaster – log shipping is a popular technique that gets you a warm standby server with no performance overhead in production.
- Log Shipping Part 2 – When Disaster Strikes
Real-Life Backup Stories
- StackOverflow’s Recovery Strategy – how they designed their backups.
- Why Dedupe is a Bad Idea for SQL Server Backups – if your sysadmins are pressuring you into using a dedupe appliance, understand the performance implications.
- Carbonite Failures – I talked about the problems with Carbonite, and what DBAs can learn from them. I also followed up on the Carbonite issues after David Friend (their CEO) commented on the blog.
- Journalspace Failure – another dot-com bites the dust because they didn’t have good database backups.
- Ma.gnolia Backup Failures – online bookmark sharing service couldn’t restore from backups, and went under.
- Browse All Posts Tagged “Backup”
SQL Server Backup & Recovery Training Class
There is no more critical task for a DBA than to back up the business data – except perhaps restoring it to ensure validity. A disaster – in many forms – can strike at any time. SQL Server offers many backup and restore options, to help you meet business requirements.
In Jes Schultz Borland’s 2-hour video class, you’ll learn about:
- Simple, Full, and Bulk logged recovery models
- Full, differential, and log backups
- Restore options
- Best practices
It always helps when you see something in action, so she’ll demonstrate
- Full backups
- Differential backups
- Log backups
SQL Server Backup & Recovery, Advanced
Once you’ve mastered the basics of backup and recovery, it’s time to work on a new set of challenges. You may need backups or restores to be faster, to find and fix corruption, or to be able to restore only certain parts of a database.
In this hour and a half video training, Jes Borland covers
- Striping and compressing backups
- Piecemeal restores
- Creating, finding, and fixing corruption
- Marked transactions