Backup and Recovery Archives - Brent Ozar Unlimited®

An Idea For Improving DBCC CHECKDB

January 3, 2019

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.

August 22, 2018

Brent Ozar

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…

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

July 25, 2018

Tara Kizer

Backup and Recovery

No Comments

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.”

March 30, 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

March 19, 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

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

January 4, 2018

Erik Darling

Backup and Recovery

backup, dba, vss

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.

December 22, 2017

Erik Darling

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

dba, 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

November 10, 2017

Brent Ozar

CHECKDB and Corruption

No Comments

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.

September 21, 2017

Brent Ozar

Backup and Recovery, Breaking News

backups, compression, tde

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

August 28, 2017

Erik Darling

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

availability groups, dba, recovery, 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

August 10, 2017

Erik Darling

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

No Comments

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

July 25, 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

July 21, 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?

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?

June 30, 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

June 29, 2017

Brent Ozar

Backup and Recovery, First Responder Kit, kCura Relativity

faux paas

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

June 21, 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

June 20, 2017

Brent Ozar

Amazon Web Services (AWS), Backup and Recovery

builder day

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.

June 12, 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…