Backup and Recovery

Home
Backup and Recovery

How to Run DBCC CHECKDB on a Log Shipping Subscriber

Last Updated February 13, 2017

Backup and Recovery, CHECKDB and Corruption, Log Shipping, SQL Server

At first glance, if you set up log shipping the way most folks do, it looks like you can’t run CHECKDB on a subscriber: Database cannot be opened. It is in the middle of a restore. Please close the bathroom door. On SQL Server Enterprise Edition, you can try working around it by creating a…

Updated High Availability and Disaster Recovery Planning Worksheet

Last Updated February 23, 2021

Brent Ozar

Always On Availability Groups, Backup and Recovery, Clustering, SQL Server

One of the most popular things in our First Responder Kit is our HA/DR planning worksheet. Here’s page one: Page 1 – how our servers are doing now, versus what the business wants In the past, we had three columns on this worksheet – HA, DR, and Oops Deletes. In this new version, we changed “Oops”…

Availability Group Direct Seeding: Mind Your Backup Jobs

Last Updated February 9, 2017

Erik Darling

Always On Availability Groups, Backup and Recovery, SQL Server

I’ll get right to the point While you’re Direct Seeding, you have to be careful with any other full or differential backup jobs running on the server. This is an artifact of the Direct Seeding process, but it’s one you should be aware of up front. In the screencap below, courtesy of sp_whoisactive there’s a…

Triage Quiz: Is Your SQL Server Safe?

Last Updated February 13, 2017

Angie Rudduck

CHECKDB and Corruption, SQL Server

Contrary to popular belief, we spend a lot of time with clients when we’re not blogging, answering questions in Office Hours, or working on new features for the download pack. Something we hear a lot is, “How do we compare to other clients?” or “Is this the worst/best setup you’ve seen?”. This got me thinking,…

SQL Server 2016 Maintenance Plan Improvements

Last Updated February 13, 2017

Brent Ozar

CHECKDB and Corruption, SQL Server

Let’s just get one thing out of the way first. Yes, I understand that you, dear reader, probably hate maintenance plans. You associate them with everything dumb that you did early on in your career, like setting up a single plan that reorganized indexes, then rebuilt them, then updated their statistics. You are older and…

What to Do When DBCC CHECKDB Reports Corruption

Last Updated April 13, 2018

Brent Ozar

Backup and Recovery, CHECKDB and Corruption, Processes and Practices, SQL Server

The instant you encounter corruption in a production SQL Server database, stop. Read this entire article first to understand the big picture, and then come back here to take action. First, understand that you shouldn’t start by trying to repair the corruption. You may be facing a storage subsystem problem where your storage is corrupting…

My Favorite Database Disaster Stories

Last Updated February 11, 2017

Brent Ozar

Backup and Recovery, Clients and Case Studies, Processes and Practices

The statute of limitations has passed, so this week on SQL Server Radio, I get together with Guy Glantser and Matan Yungman to talk about our favorite oops moments. I talked about my very first database disaster ever – done back when I was in my 20s and working for a photo studio, using Xenix, long…

How to Run DBCC CHECKDB for In-Memory OLTP (Hekaton) Tables

Last Updated February 15, 2019

Brent Ozar

CHECKDB and Corruption, In-Memory OLTP (Hekaton), SQL Server

tl;dr – run a copy-only full backup of the Hekaton filegroup to nul. If the backup fails, you have corruption, and you need to immediately make plans to either export all your data, or do a restore from your last good full backup, plus all your transaction log backups since. Yeah, that one’s gonna need a…

My Favorite System Column: LOG_REUSE_WAIT_DESC

Last Updated February 9, 2017

Brent Ozar

Backup and Recovery, SQL Server

Hidden away in master.sys.databases is one of the coolest diagnostic tools you might ever need: SELECT name, log_reuse_wait_desc FROM sys.databases; 1 SELECT name, log_reuse_wait_desc FROM sys.databases; This spiffy little snippet will tell you why each database’s log file isn’t clearing out. Possible reasons include: Log backup needs to be run (or if you could lose…

How often should I run DBCC CHECKDB?

Last Updated February 13, 2017

Erik Darling

CHECKDB and Corruption, SQL Server

There’s an old DBA saying… May you already have a backup restored A half hour before your boss knows there’s corruption What? There’s no such thing as old DBA sayings? Well, maybe if you all said something other than “no” once in a while, you’d be more quotable. Hmpf. Anyway, this is a serious question! And…

The Top 4 Job Tasks DBAs Forget

Last Updated February 13, 2017

Brent Ozar

CHECKDB and Corruption, SQL Server

It’s easy to get caught up in daily incidents, tickets, and special projects. Like a good scout, though, a core task in a DBA’s job is to be prepared. You have to set time aside in your schedule to: 1. Practice responding to corruption. Gail Shaw’s “Help, my database is corrupt, now what?” post should…

How to Make SQL Server Backups Go Faster

Last Updated February 9, 2017

Brent Ozar

Backup and Recovery, SQL Server

At its core, backup speed boils down to just two numbers. Question 1: How fast can SQL Server read data from disk? You can measure this by doing a full backup to disk, but use the NUL: option: BACKUP DATABASE MyDb TO DISK='NUL:' 1 BACKUP DATABASE MyDb TO DISK='NUL:' This does a backup, but doesn’t actually…

SQL Server 2016 CTP2.4: Maintenance Plan Changes

Last Updated February 13, 2017

Brent Ozar

CHECKDB and Corruption, SQL Server

You can download the next preview of SQL Server 2016 today, and the announcement blog post touted a few improvements to maintenance plans. Oddly, the screenshots in the announcement were apparently done with Microsoft Paint, so I fired up a VM to see how things really look. The DBCC CHECKDB step in the wizard now has…

Why RPO and RTO Are Actually Performance Metrics Too

Last Updated February 23, 2021

Brent Ozar

Architecture, Backup and Recovery, CHECKDB and Corruption, SQL Server

Most companies come to us saying, “The SQL Server isn’t fast enough. Help us make it go faster.” They’re kinda surprised when one of the first things we fill out together is a variation of our High Availability and Disaster Recovery Planning Worksheet: Download the full PDF in our First Responder Kit They say things like, “Wait,…

Synchronous Always On Availability Groups Is Not Zero Data Loss

Last Updated February 9, 2017

Brent Ozar

Always On Availability Groups, Backup and Recovery

In theory, when you configure AlwaysOn Availability Groups with synchronous replication between multiple replicas, you won’t lose data. When any transaction is committed, it’s saved across multiple replicas. That’s the way it works, right? I mean, except when you restart your synchronous replicas, or patch them, or they just stop working for any number of reasons.…

Getting the last good DBCC CHECKDB date

Last Updated February 13, 2017

Erik Darling

CHECKDB and Corruption, SQL Server

whether it’s a new job or a new (old) server If you’re reading this, you’ve at some point in your career stared at a server, for the first time, with great trepidation. The smarter you are, the greater your trepidation is. The 2nd century mathematician Trepidatius the Wimpy had an equation that described this, but…

Should You Put Things in the master Database?

Last Updated May 16, 2019

Brent Ozar

Always On Availability Groups, Backup and Recovery, SQL Server

Growing up as a young boy, my elementary teachers always warned me about the perils of putting stuff in master. But what’s the real deal? Why is this such a bad idea? System databases are often located on space-limited drives. The default installation parameters for SQL Server throw master, model, and msdb all on the…

How to Configure Always On AG Backups with Ola Hallengren’s Scripts

Last Updated March 6, 2019

Brent Ozar

Always On Availability Groups, Backup and Recovery, Ola Hallengren's Database Maintenance Scripts

With SQL Server AlwaysOn Availability Groups, you can offload backups to a replica rather than running them on the primary. Here’s how to do it: 1. Install Ola Hallengren’s utility scripts on all of the replicas. During the install, it creates a laundry list of SQL Agent jobs, but doesn’t set up schedules for any of them.…

How Do You Manage DBAs? Part 1: Measuring Backups

Last Updated February 9, 2017

Brent Ozar

Backup and Recovery, Processes and Practices

When the DBA reports to you, ask yourself two questions: “What would I fire the DBA for?” “How can I prove that they’re not doing that?” There’s a lot of possible answers to #1, many of which involve cocaine and the office copier, but let’s focus on one I hear a lot: an inability to restore…

Who’s Backing Up That Database?

Last Updated February 9, 2017

Kendra Little

Backup and Recovery

A question came up on Twitter recently– how you can you figure out where a rogue backup is coming from? I’ve run into this question with clients before. In an enterprise environment where backup tools come and go, it’s not unusual to find backups being run by some mysterious account. By the way, when’s the…