Backup and Recovery

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

Last Updated July 20, 2018

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

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

Backup and Recovery

So much, so often Ah, backups. Why are they so tough to get right? You start taking them, you find out you’re not taking enough of them, or that they’re not the right kind, or that you’re not using checksums or compression, or that you’re not storing them in the right place, or that the…

Sizing A New Server? Start With Maintenance.

Last Updated December 16, 2017

Erik Darling

Backup and Recovery, CHECKDB and Corruption, First Responder Kit, SQL Server

Clientele Most of out customers are on slightly (ahem) older (AHEM) hardware, and they have big questions: How do we know what kind of hardware we need? How do we load test it? How do we know what to change to make it better? The load test question is interesting, particularly because getting a reliable…

How to Test Your Corruption Alerts

Last Updated November 8, 2017

Brent Ozar

CHECKDB and Corruption

You’ve been such a good database administrator. You followed the setup checklist in our First Responder Kit. You ran sp_Blitz. You set up email alerts for common issues. You run CHECKDB as frequently as practical – weekly, or maybe even daily. But you just assume it’s all working. There’s an easy way to test: go…

Breaking News: Using TDE on 2016? Doing Backups? Time to Patch.

Last Updated September 21, 2017

Brent Ozar

Backup and Recovery, Breaking News

Normally, when you use Transparent Data Encryption (TDE) to secure your database at rest, you can’t get backup compression. Encrypted data basically looks like random data, and random data doesn’t compress well. SQL Server 2016 introduced the ability to compress your TDE databases. Yay! Unfortunately, it has bugs that can leave you unable to restore…

An Expensive Reason To Avoid AGs In Azure

Last Updated August 27, 2017

Erik Darling

Always On Availability Groups, Backup and Recovery, CHECKDB and Corruption, Cloud Computing, High Availability, Microsoft Azure, SQL Server

Cash Rules Most people, when they get through paying for Azure, and SQL Server Enterprise Licensing, are left with a hole in their wallet that could only be filled with something that says “Bugatti”, and has a speedometer with an infinity sign at the end. Recently, while working with a client, I found out that…

sp_AllNightLog: Creating Jobs Like I’m The President

Last Updated August 11, 2017

Erik Darling

Backup and Recovery, Clients and Case Studies, ISV and SaaS Database Design, kCura Relativity, Log Shipping, SQL Server

Look, we need these things The setup for sp_AllNightLog creates jobs for four separate activities 1 job to poll for new databases to back up (primary) 1 job to poll for new databases to restore (secondary) 10 jobs to poll for backups to take (primary) 10 jobs to poll for restores to… whatever (secondary) And,…

Let’s Corrupt a Database Together, Part 3: Detecting Corruption

Last Updated July 26, 2017

Brent Ozar

CHECKDB and Corruption

So far in this series, I’ve shown you how to corrupt a clustered index, then how nonclustered indexes can be corrupted independently. If you haven’t read those, you should start there first. Let’s start again with our 50Ways database, but this time we’re going to be good and make sure that we’ve got checksums enabled,…

sp_AllNightLog: Poll Vaulting

Last Updated July 12, 2017

Erik Darling

Backup and Recovery, Clients and Case Studies, ISV and SaaS Database Design, kCura Relativity, Log Shipping, SQL Server

Carry on It turns out that the only thing harder than checking for new databases restored to a SQL Server, is checking a folder for a backup of a database that doesn’t exist on another SQL Server. These are both part of what sp_AllNightLog has to do. The other components, which use workers and a…

sp_AllNightLog: ¿Por que los queues?

Last Updated July 7, 2017

Erik Darling

Backup and Recovery, Clients and Case Studies, ISV and SaaS Database Design, kCura Relativity, Ola Hallengren's Database Maintenance Scripts

Building stuff I sometimes really hate coming up with ideas, and much prefer someone to just say “I want this” so I can go out into the world and figure out how to do that. Occasionally though, I realize that I’m not going to get all that much direction. That’s what happened with sp_BlitzQueryStore. It…

Why Is Tracking Restores So Hard?

Last Updated June 21, 2017

Erik Darling

Backup and Recovery, SQL Server

Phantom of the Nopera Let’s say you have a server. Let’s go one step further and say SQL Server is running on it. Crazy, I know. But hey, we specialize in crazy, here. If you want to track CREATE, ALTER, or DROP DATABASE commands, that’s easy enough with DDL triggers. But guess what? None of…

Introducing sp_AllNightLog: Log Shipping at Scale, Open Source

Last Updated June 30, 2017

Brent Ozar

Backup and Recovery, First Responder Kit, kCura Relativity

In our Faux PaaS project, we need a backup plan – or rather, a restore plan. On each SQL Server instance, clients can create as many databases as they want, anytime they want, with no human intervention. We need those databases covered by disaster recovery as quickly as practical. SQL Server’s newer disaster recovery options – Always On Availability Groups and…

Builder Day: Doing a Point-in-Time Restore in Amazon RDS for SQL Server

Last Updated June 13, 2017

Tara Kizer

Amazon Web Services (AWS), Backup and Recovery

Brent had an idea that we should set aside a day per month where we could build things in the cloud and blog about our experiences. I was tasked with Amazon RDS for SQL Server: create an instance, configure backups and test point-in-time restores. After grabbing some coffee, I logged into the AWS console. We…

Builder Day: Doing a Point-in-Time Restore in Azure SQL DB

Last Updated June 20, 2017

Brent Ozar

Amazon Web Services (AWS), Backup and Recovery

We’re trying something new at the company: Builder Day. We define a slightly-out-of-the-norm task, and then the team splits up and tackles the task on different cloud platforms, writing it up as we go. These posts aren’t going to make you an expert on the topic – they’re just meant to let you skim through…

Ola Hallengren’s Maintenance Scripts are Now on Github.

Last Updated June 17, 2017

Brent Ozar

Backup and Recovery, Ola Hallengren's Database Maintenance Scripts

For some of you, the headline is self-explanatory, and you just want the URL, so here it is: https://github.com/olahallengren/sql-server-maintenance-solution For the rest of you, here’s what it means. What are Ola Hallengren’s maintenance scripts? Ola Hallengren’s maintenance scripts are a widely used replacement for maintenance plans. His backup, integrity check, and index optimization stored procedures are…

5 Signs Your SQL Server Isn’t Wearing Pants

Last Updated March 24, 2017

Brent Ozar

CHECKDB and Corruption, SQL Server

We see a lot of SQL Servers. During our sales process, prospective customers run sp_Blitz on their SQL Server, and we talk through the results. I see a lot of horrifying stuff. Over time, I’ve built up a pretty good sense of signs that a SQL Server is running around loose in the data center,…

From The Mailbag: DBCC CHECKDB And Read Only Databases

Last Updated March 23, 2017

Erik Darling

CHECKDB and Corruption, SQL Server

We got an email!!! It was weird. It was from an Australian, and it was right side up. I could hardly believe it. Normally we encourage people to ask us questions during Office Hours, or over on Stack Exchange. That way lots of people can benefit from the answer. But since this reader is on…