backup

The Perils Of VSS Snaps

January 4, 2018

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…

This Is Your Backup Schedule On Drugs

March 27, 2017

Erik Darling

SQL Server

backup, badidea, dba, sql server

I’ve seen things Terrible things. I mean, not just working here (which, as I write this, I’m three days away from the two year mark, fingers crossed!), but also out there on the internet. Once you leave the safe confines of Brent Ozar Unlimited, you’re subject to the ravages of space. Strangers. Radiation. Strangiation. Your…

TDE and Backup Compression: Together At Last

July 5, 2016

Erik Darling

SQL Server

backup, dba, performance tuning, sql server

Note: THERE ARE BUGS IN THIS FEATURE. Make sure you read all the way through the post to catch the updates from Microsoft as more bugs were found. TDE is one of those things! You either need it, and quickly learn how many things it plays the devil with, or you don’t need it, and…

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

June 25, 2015

Brent Ozar

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

always on, backup, ola hallengren

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

9 Ways to Lose Your Data

February 19, 2015

Brent Ozar

CHECKDB and Corruption, SQL Server

backup, corruption, recovery

Every time someone tells me, “This database is mission critical – we can’t have data loss or downtime,” I just smile and shake my head. Technology is seriously difficult. To illustrate, here’s a collection of client stories from the last few years: The DBCC CHECKDB job ran every week just like it was supposed to…

How Many Databases Can I Put on One SQL Server?

February 4, 2015

Brent Ozar

Architecture, Backup and Recovery, ISV and SaaS Database Design, SQL Server

backup, dr, ha, recovery, rpo, rto

I’ve seen servers with thousands of databases on a single SQL Server, and it works. Sure, opening the databases list in SQL Server Management Studio is painful, and a lot of third party monitoring tools fall over, but it’s not so bad once you know how to work around these issues. But there’s two issues…

What If You Knew The Server Would Crash Today?

February 4, 2014

Brent Ozar

SQL Server

backup

This morning you woke up with a terrible premonition – you were absolutely sure your most important production database server was going to crash today. What would you do? Step 0: stop for coffee, because it’s gonna be a long day. Here’s a quick list of places to start: Identify where the most recent backups…

Always On Availability Groups, Backup Checksums, and Corruption

December 11, 2013

Brent Ozar

Always On Availability Groups, SQL Server

always on, availability groups, backup, checkdb, corruption

The latest version of sp_Blitz® alerts you if you haven’t been using the WITH CHECKSUM parameter on your backups. This parameter tells SQL Server to check the checksums on each page and alert if there’s corruption. But what about corrupt backups? Books Online says: NO_CHECKSUM – Explicitly disables the generation of backup checksums (and the validation…

Backups Gone Bad: The Danger of Differentials

December 4, 2012

Brent Ozar

Backup and Recovery, SQL Server

backup

As your databases get bigger, you’re going to run out of time to do nightly full backups. You’re going to be tempted by the siren song of differential backups. The idea is that you’ll do full backups on the weekends, and then differential backups each night of the week. Differential backups aren’t just the sum…

Long Term Backup Storage With Amazon Glacier

November 20, 2012

Jeremiah Peschka

Amazon Web Services (AWS)

aws, backup, sql server

A while back, Jes asked who’s taking your backups. Making sure you have good backups is important. How much thought are you giving to handling historical backups? Right now, there’s a really good chance that you’re using a solution based on tape. While tape backups work, there’s a better way. How Are You Archiving Backups Right…

How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008, R2, 2012, 2014, 2016, 2017, 2019

August 21, 2009

Brent Ozar Unlimited Team

Backup and Recovery

backup, log file, sp_blitz, truncate, vlf

BACKUP LOG WITH TRUNCATE_ONLY is a dangerous command: it empties out the contents of your SQL Server’s transaction log without really backing it up. Database administrators sometimes run this command right before shrinking their log file with a DBCC SHRINKFILE command, thereby freeing up drive space. Why ‘truncate_only’ is not a recognized backup option. When…