Backup and Recovery

Why Dedupe is a Bad Idea for SQL Server Backups

Last Updated February 13, 2017

Has your SAN admin or CIO been telling you not to compress your SQL Server backups because they’re doing it for you with a dedupe tool like EMC’s Data Domain? I’ve been hearing from a lot of DBAs who’ve been getting this bad advice, and it’s time to set some records straight. The Basics of…

Why Are You Backing Up?

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

We focus so much on the fine-grained details of exactly when and where we’re backing up our servers. Sometimes it helps to take a quick step back and ask what we’re trying to protect ourselves, because it’ll help us do a better job of designing a recovery strategy. Dude, Where's My Datacenter? From small problem…

Check All Your MSDB Cleanup Jobs With One Query

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

Are all of your servers set up to clean out their backup history periodically? Are you sure? If you looked back at the monitor with “WTF?” written all over your face, I’ll bring you up to speed. Here’s the problem: SQL Server stores information about completed backups in the MSDB database. In older versions of…

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

Last Updated February 17, 2020

Brent Ozar Unlimited Team

Backup and Recovery

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…

More On the Carbonite Backup Failures

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

David Friend, the CEO of Carbonite, left a comment on my blog entry about Carbonite. I’d like to applaud his efforts for taking the time to do that, but the comment raised some ugly questions. It appears that they were putting data on 15-drive RAID 5 arrays. RAID 5 is the most cost effective array…

Dev, Test and Production SQL Server Environments

Last Updated July 16, 2019

Brent Ozar

Backup and Recovery

In a perfect world, my test/QA servers get restored nightly from production. Let’s say every night at 9pm, the production full backups kick off, and they’re finished by 10pm. At 11pm, the QA box kicks off a restore job that grabs the latest full backups off the file share and restores them. The production backups…

Another backup failure: Carbonite

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

TechCrunch reports that Carbonite, an online backup company, lost customer data. But wait, this is different: it’s not their fault. They’re suing Promise Technology, makers of popular storage gear, for selling them bogus equipment. Bogus equipment? You mean, like hard drives that fail? That’s horrible! Who could expect something like that? Who could know about…

Backup Fail: Ma.gnolia goes under

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

Bookmark sharing site Ma.gnolia just had a big problem (not quite clear from the site what it was) and user data was trashed. They needed to restore from backup. Only one little problem – they didn’t have one. And now they’re out of business. We’ve talked about this before when JournalSpace failed, and I’m sure…

Mirrors aren’t backups

Last Updated February 9, 2017

Brent Ozar

Architecture, Backup and Recovery, Database Mirroring

Are you using any of these technologies as a method of database protection? Database mirroring (keeping two database servers synchronized with the same data via SQL 2005/2008) Replication (copying records between databases) Log shipping (copying log files to another server and restoring them immediately) SAN-based mirroring (storing two copies of your database on two different…

Back up your database to the network – not local disk

Last Updated July 16, 2019

Brent Ozar

Backup and Recovery

Database administrators: are you backing up your SQL Servers to local drives? If so, you need to stop for a minute to think about a few possible side effects, and maybe think about backing up to a network share instead. Why? I’m glad you asked! Get your data off the server faster The only reason…

Microsoft DPM 2007 review

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

I spent some time last week digging into Microsoft Data Protection Manager 2007, Microsoft’s solution for SQL Server backups, and I’m going to share some of my findings here with you, dear reader. “But You Work For Quest Software! You can’t write a Microsoft DPM review!” Yeah, disclaimer time – I work for the people…

SQL Backup Software: Part 5 – Justifying the Cost

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

I’ve talked about how a SQL backup compression program can help database administrators, but now it’s time to convince the boss. We have to sell management on why it’s worth the money. First, a few words about my approach to database administration, because I think it’ll help DBAs in general. Being a database administrator is…

SQL Backup Software: Part 4 – Features Still Missing

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

In this issue of my multi-part series about SQL Server backup compression software, I’m going to talk about some features that database administrators might find useful, but don’t yet exist in the current generation of backup software. In-Depth Backup & Restore Progress Monitoring On multi-terabyte data warehouses – especially those running new backup compression software…

SQL Backup Software: Part 3 – New Features for Backup/Restore

Last Updated August 14, 2018

Brent Ozar

Backup and Recovery

In the last decade, Microsoft has brought SQL Server a long way – bringing in things like .NET code, data encryption, partitioning, and a darned slick management user interface. But one of the things that hasn’t changed has been the process of backing up and restoring data. We still back up to a file, and…

SQL Backup Software: Part 2 – Quad Cores are Changing the Game

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

In my last post, Why Native SQL Backups Suck, I talked about the weaknesses of native SQL Server backups. Today, I’m going to extend that a little by talking about one of the greatest surprises for DBAs in recent history: the advent of dirt-cheap multi-core processors that don’t cost extra for SQL licensing. How SQL…

SQL Server Backup Software: Part 1 – Why Native SQL Backups Suck

Last Updated February 13, 2017

Brent Ozar

Backup and Recovery

Before we start looking at SQL Server backup compression software, we need to spend a few minutes looking at the weaknesses of the native SQL Server backup process. In order to judge the fixes, we have to know what’s broken. Native SQL Server backups take the same disk space as the data. When we back…

SQL Server Backup Best Practices

Last Updated September 18, 2017

Brent Ozar

Backup and Recovery, SQL Server

I’ve been backing up SQL Servers for almost a decade now, and it’s time to share the lessons I’ve learned. All of this is my own opinion – your mileage may vary – but I’ll try to explain the reasoning behind the choices I make. I won’t address log shipping or snapshots this time around.…