sp_Blitz® Result: Backups Not Performed Recently

Errr......

Errr……

Before we start working on anything, we need to know all our user databases are getting backed up.  This part of our SQL Server sp_Blitz® script checks to see if there’s been a full backup in the last 7 days.

Often, someone set up maintenance plans to back up specific databases by checking their names.  They checked all of the database names that were present at the time, but then down the road, other people added more databases – without knowing they needed to check more boxes to get ’em backed up.

Or maybe your users add databases all the time, but your backup tool doesn’t catch them until a weekend full backup. Good news, we can fix that too – keep reading.

In rare cases, like for multi-terabyte databases, sometimes there’s a full backup happening just once per month, and differential backups are done more frequently.  Our Blitz only checks the last 7 days, so you would see alerts here after the 7th of the month.

Are Your Backups Less Perfect Than You Thought? Check Here.

To see how much data you could lose per database over the last couple of weeks, run this query:

For each of your databases, it lists the maximum amount of time you went between backups over the last two weeks. To see the backup history for each database, copy/paste the query from the “more_info_query” column and run that – it’ll list the full backup history.

Time to ask some tough questions, eh?

To Fix the Problem

Choose your own adventure, and then scroll down

  1. Set up full backups (details on how to do this below)
  2. Increase your backup frequency/fix your backup schedules to close any gaps you have
  3. Use Ola Hallengren’s @ChangeBackupType to automatically do fulls for new databases

Return to sp_Blitz® or Ask Us Questions


1. How to Set Up Full Backups in SQL Server

For servers using maintenance plans, you can edit maintenance plans in SQL Server Management Studio under Management, Maintenance Plans.  Your server may have multiple maintenance plans, like one for system databases and one for user databases.

If you’re implementing backups for the first time, consider using Ola Hallengren’s free maintenance scripts.  They’re not quite as easy as maintenance plans, but they’re much more flexible.  Ola’s scripts even work with third party backup compression products.

Implementing Full Backups With Ola Hallengren’s solution

The first thing you’ll want to do is point your backups away from a local path that also hosts Windows system or other SQL data files. If you don’t have one of those, point it to a network share. This flag is on line 33 or so of the MaintenanceSolution.sql download. If you don’t see it immediately, just search for the @BackupDirectory parameter. This can be changed in the Agent Job as well, which I’ll cover later.

After running the script (just hit F5), you should have a set of brand spankin’ new jobs listed under SQL Agent:

The ones you want to focus on are the FULL backup jobs. You’ll want to schedule your Differential and Log backups differently, according to your RPO and RTO. You can check out Kendra’s overview on log backups here.

2015-04-08_12-53-53

Make sure these don’t already exist first. You might be overwriting important stuff.

Start off with a test run before scheduling by right clicking on the job, and selecting “Start Job at Step…”

2015-04-08_13-18-21

Ze testing is important ja!

You should see this when it completes. If it fails, investigate any errors. They usually have to do with disk space, or permission to write to the path specified.

2015-04-08_13-00-15

HUGE SUCCESS

If this works correctly, go ahead and schedule your job by right clicking and selecting properties. Select the Schedule page, then click “New” down at the bottom

2015-04-08_13-42-54

That should get you here:

2015-04-08_13-43-06

What is midnight on Sunday? I DON’T KNOW, EITHER.
That’s why I pick a time that’s not midnight on the nose to schedule these things.

This is also where you can alter the backup path and other job variables:

2015-04-08_13-51-19

And where you would set up notifications for failures:

2015-04-08_13-51-42

For the full list of tweaks, click on over here.


2. Increase Your Backup Frequency

If you’re reading this and thinking, “My backups should probably be running more often,” then you’re probably right. Management assumes you’re never going to lose data – which isn’t realistic. We just need to get our capabilities to match up with management’s goals.

Check out these posts:


3. Taking Full Backups On Demand

If you need to protect new databases quickly after they’re created, check out Ola Hallengren’s backup scripts.

He has a @ChangeBackupType flag you can pass in – when set to Y, then when it finds a new database during a log backup job, and it can’t take a log backup (since no full has been taken yet), it’ll automatically take a full backup for you.

Menu
{"cart_token":"","hash":"","cart_data":""}