Backups Not Performed Recently

Before we start working on anything, we need to know all our user databases are getting backed up. We check 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.

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:

CREATE TABLE #backupset (backup_set_id INT, database_name NVARCHAR(128), backup_finish_date DATETIME, type CHAR(1), next_backup_finish_date DATETIME);
INSERT INTO #backupset (backup_set_id, database_name, backup_finish_date, type)
SELECT backup_set_id, database_name, backup_finish_date, type
FROM msdb.dbo.backupset WITH (NOLOCK)
WHERE backup_finish_date >= DATEADD(dd, -14, GETDATE())
AND database_name NOT IN ('master', 'model', 'msdb');
CREATE CLUSTERED INDEX CL_database_name_backup_finish_date ON #backupset (database_name, backup_finish_date);

UPDATE #backupset
SET next_backup_finish_date = (SELECT TOP 1 backup_finish_date FROM #backupset bsNext WHERE bs.database_name = bsNext.database_name AND bs.backup_finish_date < bsNext.backup_finish_date ORDER BY bsNext.backup_finish_date)
FROM #backupset bs;

SELECT bs1.database_name, MAX(DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date)) AS max_minutes_of_data_loss,
'SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date ORDER BY bsPrior.backup_finish_date DESC), ''1900/1/1''), bs.backup_finish_date) AS minutes_since_last_backup, DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes, CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) WHEN 0 THEN 0 ELSE CAST(( bs.backup_size / ( DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) ) / 1048576 ) AS INT) END AS throughput_mb_sec FROM msdb.dbo.backupset bs WHERE database_name = ''' + database_name + ''' AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date' AS more_info_query
FROM #backupset bs1
GROUP BY bs1.database_name
ORDER BY bs1.database_name

DROP TABLE #backupset;
GO

CREATE TABLE #backupset (backup_set_id INT, database_name NVARCHAR(128), backup_finish_date DATETIME, type CHAR(1), next_backup_finish_date DATETIME);

INSERT INTO #backupset (backup_set_id, database_name, backup_finish_date, type)

SELECT backup_set_id, database_name, backup_finish_date, type

FROM msdb.dbo.backupset WITH (NOLOCK)

WHERE backup_finish_date >= DATEADD(dd, -14, GETDATE())

AND database_name NOT IN ('master', 'model', 'msdb');

CREATE CLUSTERED INDEX CL_database_name_backup_finish_date ON #backupset (database_name, backup_finish_date);

UPDATE #backupset

SET next_backup_finish_date = (SELECT TOP 1 backup_finish_date FROM #backupset bsNext WHERE bs.database_name = bsNext.database_name AND bs.backup_finish_date < bsNext.backup_finish_date ORDER BY bsNext.backup_finish_date)

FROM #backupset bs;

SELECT bs1.database_name, MAX(DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date)) AS max_minutes_of_data_loss,

'SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date ORDER BY bsPrior.backup_finish_date DESC), ''1900/1/1''), bs.backup_finish_date) AS minutes_since_last_backup, DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes, CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) WHEN 0 THEN 0 ELSE CAST(( bs.backup_size / ( DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) ) / 1048576 ) AS INT) END AS throughput_mb_sec FROM msdb.dbo.backupset bs WHERE database_name = ''' + database_name + ''' AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date' AS more_info_query

FROM #backupset bs1

GROUP BY bs1.database_name

ORDER BY bs1.database_name

DROP TABLE #backupset;

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

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

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.

SET @BackupDirectory     = N'\\Make\Me\Yours'

1	SET @BackupDirectory = N'\\Make\Me\Yours'

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.

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

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:

And where you would set up notifications for failures:

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.