Backup and Recovery Archives - Brent Ozar Unlimited®

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

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

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

No Comments

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…

5 Signs Your SQL Server Isn’t Wearing Pants

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

March 23, 2017

Erik Darling

CHECKDB and Corruption, SQL Server

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

sp_DatabaseRestore: Open Source Database Restore Stored Procedure

March 7, 2017

Tara Kizer

Backup and Recovery, First Responder Kit

Welcome to the sp_DatabaseRestore documentation page. We were looking for a script that restores a database including the transaction log chain using the backup files in a folder. We also wanted it to be able to continue applying transaction log backups. In other words, we were looking for a custom Log Shipping solution. We could…