Tag Archive: backup

My Weekly Bookmarks for October 16th

Here’s my bookmarked links for the week ending Friday, October 16th:

PASS Election Links

PASS Summit Links

SQL Server Links

IT Links

The Junk Drawer

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Bad News, Good News, Worse News

Ran across a funny situation in my test lab, and it’s only funny because it was my test lab.

The bad news: backups started failing a few days ago. One of the databases had a filegroup that wasn’t online, and as my maintenance plan looped through the list of databases, it died when it couldn’t back up that database.  Unfortunately, it was going in alphabetical order, and that database started with a B.

Jesus Saves - and he always uses the shared drive.

Jesus Saves - and he always uses the shared drive.

The good news: the separate cleanup jobs still worked great. They were dutifully cleaning out any backups older than a few days.

The worst news: database mail had failed – of course, a few days before the backups started failing.  My DNS servers in my lab had decided to take the week off, so email wasn’t making it out of my lab.  I didn’t get notified about the backup jobs that started to fail.

The cleanup jobs worked better than the backup jobs, and the right hand didn’t know what the left hand was doing.  In this particular case, the left hand had been amputated at the wrist.  In a perfect world – or at least, in a world where my job depended on this data – the maintenance plan jobs would be interconnected so that they wouldn’t delete backup files if the backup job failed.  That perfect world would not be my server lab.

No real data was harmed in the making of this blog post, but times like this remind me of just how hard it is to be a good database administrator, and how easy it is to lose data.  Have you tested your restores lately?  Do you really think you’ve got something more important to do?

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

My Weekly Bookmarks for October 9th

Here’s my bookmarked links for October 2nd through October 9th:

SQL Server Links

Tech Links

The Junk Drawer

  • I Love That Game – Brilliant criminal minds at work.
  • Twitter Data Analysis: An Investor’s Perspective – A bunch of oddball stats about Twitter users and their histories.
  • Will Work for Whuffie? – Why you have to charge fees for speaking engagements when you hit a certain level of fame. (No, I’m not there yet, hahaha, but even if I was, my speaking engagements are free because I’m a service of Quest Software. No, not that kind of “service,” buddy.)

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

My Weekly Bookmarks for October 2nd

Here’s my bookmarked links for September 25th through October 2nd:

SQL Server, Cloud, and Tech Links

Writing, Blogging and Networking Links

The Junk Drawer

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Why Are You Backing Up?

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, Wheres My Datacenter?

Dude, Where's My Datacenter?

From small problem to big disaster, we need to protect ourselves from:

  • Someone deleting a few rows of data
  • Someone deleting a database
  • A hard drive (or an entire array) failing
  • A server failing
  • The entire datacenter turning into a smoking crater

In all of these events, we need to bring up the most recent copy of the data, as fast as we can.  But that’s not the only time we need protection, because sometimes we need to step back in time:

  • Someone deleted some data, but didn’t tell us right away
  • Hardware (particularly storage) started going bad and gradually corrupted more and more data over time
  • Financial auditors need to see the data the way it looked a year ago

Some backup vendors sell their solution as everything you need in one single package.  Before you sign on the dotted line, step through each of these scenarios with your vendor and ask, “With your solution, how would I protect myself from this particular problem?”

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

My Weekly Bookmarks for September 14th

Good news and bad news – the good news is that everybody’s been going wild and crazy for the SQLServerPedia PASS contest.  The bad news is that editing the articles & setting up the bloggers has eaten up every moment of my spare time, hahaha.  As a result, I had to do the unthinkable this weekend: scan through Google Reader and then hit mark-all-as-read.  Here were the survivors, and I’m sure I missed some good stuff:

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

My Weekly Bookmarks for September 4th

Here’s my bookmarked links for September 1st through September 4th:

Tech Links

The Junk Drawer

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Check All Your MSDB Cleanup Jobs With One Query

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 SQL Server, that database isn’t indexed very well, and it can become a performance bottleneck during backups and restores.  I’ve written about one particular case where I cut the company’s backup times by 2/3 just by cleaning out their backup history.

To make sure your servers are keeping their MSDB clean, you can use SQL Server Management Studio 2008′s new multi-server-query-execute feature to quickly query all of your servers.  Go read my article on how to query multiple servers at once in SSMS, and then run this query against your SQL Servers:

SELECT TOP 1 backup_start_date
  FROM msdb.dbo.backupset WITH (NOLOCK)
  ORDER BY backup_set_id ASC

The query runs almost instantaneously since it’s sorted by the primary key of the table.  The results of the query are shown here:

Consolidated MSDB Backup History

Consolidated MSDB Backup History

In that screenshot, I can see right away that three of the servers in my lab have problems.  They don’t have cleanup tasks properly configured.

If you see results like this in your own environment, drop what you’re doing and go add MSDB cleanup tasks to your maintenance plans or add sp_delete_backuphistory to your T-SQL maintenance scripts.  The first time it runs, you may run into some horribly long runtimes and locks, unfortunately.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008

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.

I’ve blogged about why backup log with truncate_only is like a bear trap, and I meant it: you shouldn’t be running this command except during extreme emergencies.  When you truncate transaction logs, you lose the ability to recover to a specific point in time.

Microsoft recommends that instead of truncating logs, you switch to simple recovery mode instead.  That way you don’t generate logs you won’t be using, and you won’t incur performance impacts from repeatedly filling and truncating the logs.  You also remove the need to regularly back up the transaction log.  This has plenty of drawbacks – if something goes wrong with your database, your only option will be to restore the previous full backup.  You could lose hours – maybe even days – of data.

Microsoft recognized the problems with TRUNCATE_ONLY and removed this capability completely from SQL Server 2008.  If you try to use this command:

BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY

You get an error:

?Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.

The only workaround in SQL Server 2008 is to switch the database’s recovery model to simple as shown in Books Online.  This empties out the transaction log, thereby letting the DBA run a DBCC SHRINKFILE afterwards, then switch the recovery model back to full.

That solution still suffers from most of the same problems as using TRUNCATE_ONLY – the database’s recoverability is compromised.  It’s just as bad of a solution, but unfortunately Microsoft can’t remove that workaround since we do need to put databases into simple recovery mode for other reasons.

Method 1: How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008

We can fake it by not writing our backup to a real device.  SQL Server lets us use the NUL: location as a backup target, so the following will do a log backup without actually saving the contents anywhere:

BACKUP LOG MyDb TO DISK=’NUL:’

Remember, we’re still not fixing anything here: whatever caused the log file to grow in the first place can happen again and put us right back where we started.

Method 2: Use Quest LiteSpeed to Fake a Log Backup

If you don’t want to switch into simple recovery mode, and if you’re using Quest LiteSpeed for SQL Server, there’s a back door workaround.

Don’t try what you’re about to see at home. We’re what you call experts.  We’ve got years of experience that keeps us safe.  Just like TRUNCATE_ONLY, this solution has a ton of drawbacks and compromises your recoverability.  This should only be done in cases where a log has grown out of control and must be erased or else the system may crash.  In any other situation, you should consider backing up the log with conventional means.

Open a command prompt, go to the folder where the LiteSpeed engine is installed (typically \Program Files\Quest Software\LiteSpeed\Engine) and type:

sqllitespeed.exe -B log -D AdventureWorks -F e:\test.bak --nowrite

The parameters are:

  • -B log = the type of backup we’re doing, database or log.
  • -D AdventureWorks = the name of the database we’re backing up
  • -F e:\test.bak = the name of the file we’re backing up to, except that…
  • –nowrite = don’t actually write the file.

LiteSpeed will make SQL Server think it’s doing a backup, but since LiteSpeed controls what gets written to disk, it just smiles and nods while SQL Server keeps shoving information across.  “Uh-huh, yeah, I got it.  Keep that data comin’.”

Your data is not actually backed up. This is a giant problem.  Don’t leave this script lying around where a junior DBA might see it and reuse it for regular backups.  This should only be used to impress your friends with your useless knowledge of SQL Server, much like I’m doing here.  Well, heck, it’s not even my knowledge – big thanks to David Gugick and James Deen of Quest Software for pointing out this trick.

P.S.: yes, I shouldn’t have even written this technique in public, but there’s a lesson here.  We had some controversy earlier this week about shrinking databases, and I wanted to show how you can convey dangerous information with proper warning labels.  If you don’t explain this stuff, someone can stumble across your instructions in a web search, think it’s completely okay, and do it without understanding the consequences.

And before you shrink your logs, read my thoughts on that too.

Brent Ozar PLF Team

Brent Ozar PLF is a boutique consulting firm focused on understanding your environment and strategy. We partner with you to objectively identify pain points and develop solutions that align to your business goals. Your experience comes first; we share our knowledge and expertise to help you.

More Posts

Follow Me:
TwitterFacebookGoogle PlusYouTube

Brent’s Backup Bottleneck: MSDB

Backup speed isn’t the sexiest thing DBAs spend time on during the day. It’s kind of boring.

Backups don’t complain that they’re not running fast enough.  Users, those are the ones who complain: “My query’s not fast enough.  Why can’t I do a cross join between data warehouse tables?”  The squeaky wheel gets the lube, so we bend the users over the – wait, where was I going with that?

Backup speed also worsens very gradually over time, taking longer and longer to complete each night.  DBAs assume the time increase is caused by more database data, but you know what they say about assume: there’s no lube involved.  What if the time has nothing to do with the database size?

In the next couple of posts, I’ll explore some backup bottlenecks I found when doing a Backup Health Check recently for a company with a couple dozen SQL Servers.  Today, put your lifejackets on, because we’re in for…

The Perfect MSDB Storm

Any one or two of these things wouldn’t have been a problem, but combine them all together and we had a disaster on our hands:

  • SQL Server 2000 – which by itself isn’t a big deal.
  • MSDB was on the C drive – which in this case, happened to be a very slow pair of ancient drives.  The rest of the server was on a pretty quick SAN with RAID 10 arrays for data and logs, but MSDB was still stuck.
  • The server had over 100 databases – again, no big deal.
  • Transaction log backups ran every 15 minutes – of all 100 databases.  That’s a lot of backup history data pouring into MSDB.
  • MSDB cleanup jobs weren’t set up initially – which meant that weeks of backup history data started turning into months, and then into years.
  • MSDB had grown to 3gb – on SQL Server 2000, this is a huge problem because it’s not indexed well.

Combine these factors, and we had The Perfect MSDB Storm.  I couldn’t add MSDB cleanup jobs at this point because they couldn’t finish in any reasonable time.  Search the web for problems like this, and you’ll find people in forums beating their chests and wailing for lifejackets.  I tried a couple of stored procs and an ordered-delete trick, but doing these deletes meant holding a lock on the MSDB backup tables.  The business wasn’t comfortable not being able to back up while I ran these deletes.

Database Administrators

Database Administrators

Kids – don’t try this at home.  We’re what you call experts.  We’ve got years of experience that keeps us safe.

  1. I scripted out the foreign key constraints on the relevant MSDB tables (backupset, backupfile, backupmediaset, etc)
  2. I disabled the backup jobs and waited for them to stop
  3. I dropped the constraints
  4. I truncated the tables
  5. I created the foreign key constraints again
  6. I enabled the backup jobs

The good news: all the backup history was instantly gone without waiting for slow logged deletions.

The bad news: all the backup history was instantly gone.  This wasn’t a problem for this client because they used Quest LiteSpeed, and it has its own backup history repository anyway.  (Shameless product plug: when your server goes down, what good does a local MSDB backup history do you anyway?  LiteSpeed’s repository is centralized on another server, so you can take action on restoring backups faster.)  This is not a great solution, and I wouldn’t post the code here for that reason, but it did work.

The bottom line: backup times dropped by 2/3! The nightly backups had been taking two hours, but a whopping 90 minutes of that was spent just updating MSDB tables with the backup history.  They now take under 40 minutes.  This certainly isn’t a typical result, but take a minute to make really sure that you’re cleaning up your MSDB history regularly.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube