Backup and Recovery

Backup and Recovery, Company News, Index Maintenance, Production Database Administration

“Time for a smoke break.” You manage growing SQL Server databases with shrinking nightly maintenance windows. You just don’t have enough time left each night to do the necessary backups, corruption checking, index maintenance, and data jobs that your users and apps want to run. Cloud storage isn’t helping the problem, either. Stop playing Tetris…

SQL Server Suddenly Frozen? You Might Be Snapshotting Too Many Databases.

Last Updated November 27, 2020

Snapshot backup tools like Azure Site Recovery and Veeam are great for sysadmins. They let you quickly replicate a virtual machine to somewhere else without knowing too much about the server’s contents. To pull it off, they use the Windows Volume Shadow Copy Service to do snapshot backups – often referred to as VSS snaps.…

3 Ways to Run DBCC CHECKDB Faster

Last Updated August 27, 2020

In my last post, I talked about how you can get 43%-67% faster backups. Today, I’m using that same SQL Server setup to discuss how throwing more CPU cores might help you check for corruption faster if you’ve already got modern fast storage. I don’t expect everyone to go to this level of detail by…

Back Up SQL Server 43%-67% Faster by Writing to Multiple Files.

Last Updated August 23, 2020

Even if you’re not writing to different physical drives, you can get faster backups with Microsoft SQL Server when you back up to multiple files. Your exact numbers are going to vary based on your server and your utter inability to rub two sticks together to make fire, but let’s see how I do. Here’s…

Can I Offload DBCC CHECKDB To Another Server?

Last Updated May 13, 2020

You want to check for corruption, but you don’t want to slow down your primary production server. In this post, I’m specifically talking about offloading the corruption checking process. I’m not talking about doing corruption checking on both the primary and other servers – that’s wonderful, and if you’re doing that, you should hug yourself. You’re doing…

The First Time I Had to Restore a Database

Last Updated March 24, 2020

Not the exact same time frame, but not that far off. I remember it really clearly. In the mid 1990s, long, long before I went into IT as a career, I was working at a photo studio in Muskegon, Michigan. They specialized in high school class photos, and they did a LOT of ’em. Every morning,…

#TSQL2sday: I Just Wish SQL Server Could Restore a Single Object from Backup.

Last Updated September 10, 2019

For this month’s T-SQL Tuesday, Kevin Chant asked us to dream up our fantasy SQL Server feature. I’m sure people are gonna ask for flying cars and gene splicing, and that’s awesome. I’m excited to hear the kinds of things they dream up. Me? I’ve got a really simple ask. (When you’re talking to Microsoft…

Research Paper Week: Constant Time Recovery in Azure SQL DB

Last Updated August 11, 2019

Azure SQL DB, Backup and Recovery

Let’s finish up Research Paper Week with something we’re all going to need to read over the next year or two. I know, it says Azure SQL DB, but you boxed-product folks will be interested in this one too: Constant Time Recovery in Azure SQL DB by Panagiotis Antonopoulos, Peter Byrne, Wayne Chen, Cristian Diaconu, Raghavendra…

DBA Training Plan 4: Checking for Corruption

Last Updated August 13, 2019

CHECKDB and Corruption, DBA Training Plan

You would think that when SQL Server writes your data to disk, it’s totally safe. Forever. You would be wrong. It’s almost like there are gremlins out to get you, purposely trying to trash your data. In the really old days, it was problems with magnetic hard drives. Later, it was buggy shared storage arrays.…

DBA Training Plan 3: Treat Your Servers Like Cattle, Not Like Pets

Last Updated August 13, 2019

Backup and Recovery, DBA Training Plan

In our last post in this series, I talked about why you should be using Ola Hallengren’s maintenance script for backups and sp_DatabaseRestore for faster restores. These two tools have implications for how you build, back up, recover, and fail over your SQL Server. There’s a saying in the cloud: treat your servers like cattle,…

Nines don't matter if users aren't happy

DBA Training Plan 2: Backups (And More Importantly, Restores)

Last Updated August 13, 2019

Backup and Recovery, DBA Training Plan

When I first started out as a SQL Server DBA, I thought things were going well as long as the backup jobs ran successfully. I’d go into SQL Server Agent every now and then, make sure they were still running, and … that was the end of it. I figured if disaster ever struck, I’d…

An Idea For Improving DBCC CHECKDB

Last Updated January 2, 2019

Erik Darling

Hard Enough At this point, we’ve all probably got a database that’s a terabyte or more in size. If you’re anything like I was, you’ve got a ton of multi-terabyte databases, and the SAN admin has a special folder for your emails. When you’ve got a lot of large databases, normal maintenance is out the…

It’s Time to Improve DBCC CHECKDB.

Last Updated August 13, 2018

Microsoft has been resting on Paul Randal’s laurels for far too long. From 1999 to 2007, Paul poured his heart and soul into rewriting SQL Server’s code to check for and repair database corruption. (For more about his illustrious career, read his bio and enjoy the infectious enthusiasm in his bio photo.) Paul did great…

Does your backup strategy achieve RPO and RTO goals of the business?

Last Updated July 20, 2018

Tara Kizer

When deciding on a backup strategy for a database, there are various things we must consider: Does this database need point-in-time recovery? What are the RPO (data loss) and RTO (downtime) goals? No one likes losing data or encountering unplanned downtime, but the business must decide on these goals so that we can setup an…

“But I don’t need to back up that database.”

Last Updated May 9, 2018

You’ve got this database that you swear doesn’t need to be backed up, like: Read-only databases Schema-only databases with no data in ’em Archive databases that haven’t changed in years Maybe even system databases like master/model/msdb And you think you’re going to save some time in your maintenance windows by skipping backups and corruption checks…

Azure SQL Managed Instances and Database Corruption

Last Updated March 18, 2018

Backup and Recovery, Managed Instances

Disclaimer: I love Azure SQL Managed Instances (MIs) so far. They’re really cool, and I’m having a fun time playing with them. I’m about to describe a bug, and I’m sure this bug will be fixed soon, so it shouldn’t stop you from previewing Managed Instances. This post is not “MIs are t3h suxxors” –…

How to Restore a SQL Server Database into Azure SQL DB Managed Instances

Last Updated March 16, 2018

Backup and Recovery, Managed Instances, Microsoft Azure

You’ve built your first Managed Instance, and now you wanna play around with real live data. You’re going to need a backup in the cloud to do it – SSMS doesn’t have a drag-and-drop option. Big picture, here’s what we’re going to do: Set up a container in the cloud to hold our backups In…

The Perils Of VSS Snaps

Last Updated December 23, 2017

Erik Darling