Product has been added to your cart.

Backup and Recovery

“Oops, I Lost My Indexes in Azure SQL DB.”

Last Updated September 12, 2023

Azure SQL DB, Backup and Recovery

I got an interesting request for consulting, and I’m going to paraphrase it: We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but… we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which… dropped the indexes…

How to Configure Ola Hallengren’s Database Maintenance Scripts for Backups

Last Updated May 4, 2023

Backup and Recovery

Someone told you that you should be using Ola Hallengren’s Maintenance Scripts. You’ve downloaded his Maintenance Solution.sql, you ran it in the master database, and … now you’re not sure what’s supposed to happen next. In this post, we’ll cover how to configure backups. First, configure the Agent jobs. When MaintenanceSolution.sql ran, it created a…

Breaking News: SQL 2019 CU16 Changes Backup Formats, Can Break Log Shipping.

Last Updated May 25, 2022

Backup and Recovery, Breaking News

The newest Cumulative Update for SQL Server 2019 has this gem in the release notes: It’s not a known issue, it’s an improvement This is a massive problem if: You have databases encrypted with TDE You take backups WITH COMPRESSION (which has a history of bugs already), or you have the server’s default compression option…

Check Your SQL Server Backup Performance & Safety with sp_BlitzBackups

Last Updated December 18, 2021

Backup and Recovery, First Responder Kit

Out of all of the scripts in our free First Responder Kit, sp_BlitzBackups is probably the one you’ve used the least. Let’s talk about what comes out of it, and why you should use it more often. First, let’s define two terms: Recovery Point Objective (RPO) – measured in time, it’s how much data you…

And it looks bad even in shades.

Free Webcast: Help! My SQL Server Maintenance is Taking Too Long!

Last Updated January 22, 2021

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

Backup and Recovery

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

CHECKDB and Corruption

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…

CrystalDiskMark

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

Last Updated August 23, 2020

Backup and Recovery

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

CHECKDB and Corruption

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…

Mophead

The First Time I Had to Restore a Database

Last Updated March 24, 2020

Backup and Recovery

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

Backup and Recovery

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…

Constant Time Recovery

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…

Brent Ozar reading

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…

DBCC CHECKDB improvement

An Idea For Improving DBCC CHECKDB

Last Updated January 2, 2019

CHECKDB and Corruption

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

CHECKDB and Corruption

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…

Tara Kizer

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

Last Updated July 20, 2018

Backup and Recovery

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

Backup and Recovery

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” –…