How Often Should I Test My SQL Server Backups?

“Backing up your SQL Server database is not really important,” I used to tell students. I’d pause, letting that sink in for a moment while the look of disbelief slowly overtook their faces. Then, I’d add “It’s the ability to restore a database that’s important. You’ve got to test your SQL Server backups. Unless you do that, you cannot be confident in your ability to restore if it becomes necessary.”

It’s like what Harvard Business School Professor Theodore Levitt said about marketing: “People don’t want to buy a quarter-inch drill. They want a quarter-inch hole!” In the world of databases, stakeholders don’t care about SQL backups. They care about the ability to restore a SQL database.

Testing your SQL Server backups

How do you know if your SQL Server backups are good? We recommend three levels of testing.

1. Checking your SQL backup jobs
2. Verifying the backup file
3. Proving your backups with a test restore.

Checking your database backup job

The ability to restore a database starts with having a good backup. We prefer native backups and in our minds, there’s no better way to manage your native backups than using Ola Hallegren’s Award winning scripts. It’s our tool of choice when it comes to SQL backups. (Note: when/if you run a SQL Server backup manually, you can use this script to see how much longer the backup will take.)

Schedule full, differential, and transaction log backups to occur regularly. Then make sure that they actually run and complete successfully.

Job failure notifications are good, and you should set those up, but it’s reassuring to actually see for yourself that the jobs complete successfully. The absence of a failure notification doesn’t automatically mean success. It’s better to check. For our customers, we look at the SQL Server Agent job histories daily.

You can use SQL Server Management Studio or Azure Data Studio to look at job histories. Of course, scripting it out is faster and more detailed. You can use the following script as starting point for your own query to check job history. This query looks for all failed jobs within the past week.

USE msdb;
GO

SELECT j.name AS Job_Name,
    h.step_name AS Step_Name,
    CONVERT(CHAR(10), CAST(STR(h.run_date, 8, 0) AS DATETIME), 111) AS RunDate,
    STUFF(STUFF(RIGHT('000000'+CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Time,
    h.run_duration AS Step_Duration_In_Seconds,
    CASE h.run_status
		 WHEN 0 THEN 'Failed'
		 WHEN 1 THEN 'Succeeded'
		 WHEN 2 THEN 'Retrying'
		 WHEN 3 THEN 'Canceled'
		 WHEN 4 THEN 'In-progress'
	  END AS Execution_Status,
    h.message AS [Message]
FROM sysjobhistory AS h
    JOIN sysjobs AS j ON j.job_id = h.job_id
WHERE CAST(STR(h.run_date, 8, 0) AS DATETIME) > DATEADD(ww, -1, GETDATE())
    AND h.step_name = '(Job outcome)'
    AND h.run_status != 1
ORDER BY j.name ASC,
	    h.run_date DESC,
	    h.run_time DESC;

In this example, the database backup job failed recently.

Another script we frequently use to look for backup job failures is:

USE MSDB;

SELECT DISTINCT
    s.[Database_Name],
    f.logical_device_name AS LogicalDeviceName,
    f.physical_device_name AS PhysicalDeviceName,
    s.expiration_date AS ExpirationDate,
    s.name AS Name,
    s.[description] AS [Description],
    s.user_name AS UserName,
    s.backup_start_date AS StartDate,
    s.backup_finish_date AS EndDate,
    DATEDIFF(mi, s.backup_start_date, s.backup_finish_date) AS DurationInMinutes,
    CAST(CASE s.type 
        WHEN 'D' THEN 'Database' 
        WHEN 'L' THEN 'Log' 
        WHEN 'I' THEN 'Differential' 
        WHEN 'F' THEN 'File' 
        WHEN 'G' THEN 'Diff File' 
        WHEN 'P' THEN 'Partial' 
        WHEN 'Q' THEN 'Diff Partial' 
        END AS NVARCHAR(128)) AS BackupType,
    ISNULL(s.compressed_backup_size, s.backup_size) / 1048576 as SIZE,
    GetDate() AS DateChecked
FROM msdb.dbo.backupmediafamily AS f
    JOIN msdb.dbo.backupset AS s ON f.media_set_id = s.media_set_id
WHERE (CONVERT(datetime, s.backup_start_date, 102) >= GETDATE() - 1)
    AND s.server_name = @@servername --Filters out databases that were restored from other instances.
    --AND s.[Database_Name] = 'DBA'
    AND s.type = 'D'
ORDER BY StartDate DESC 

Verifying your database backup file

Your weekly, daily, and sub-daily backups are scheduled and running like clockwork. You’re checking to make sure that they are actually completing successfully. But, what if the SQL backup file is bad or incomplete? What if a file was corrupted while being written to disk? The job completed successfully, but you don’t have a valid backup file to restore from.

To help with this, you should verify your SQL backup files regularly. Use the RESTORE VERIFYONLY TSQL command to check the completeness of the backup file, as well as to make sure the entire file is readable by SQL Server.

In the following example, we’re verifying that the BaseballData.bak file is valid.

RESTORE VERIFYONLY FROM DISK='/var/opt/mssql/backup/BaseballData.bak'

If it verifies successfully, you’ll see a message similar to the following.

RESTORE VERIFYONLY helps provide confidence that the backup files are usable without having to actually restore the database. We recommend running a RESTORE VERIFYONLY regularly, at least monthly on select SQL backup files.

Testing your database backup

Once you’ve have confidence that SQL backups are occurring, and that the backup files are well-formed and complete. There is still one more check that should be done periodically. To have complete confidence you can restore when needed, it’s good to actually go through the restore process.

It’s like a grade school fire drill. You can talk to students about what to do during a fire, showing them exit plans and rallying points, but until you walk through the process, it’s all theoretical. And, in an emergency, you don’t want to work from theory; you want to have practiced.

Take a SQL backup file and restore it another SQL Server instance. Apply differential backup files and transaction log files. Document the steps if you haven’t already. Creating scripts that automate it the process is a good idea, as well. In the following script, I’m restoring a copy of the BaseballData database to another instance.

RESTORE DATABASE BaseballData_restored  
   FROM DISK = '/var/opt/mssql/backup/BaseballData.bak' 
   WITH RECOVERY,  
   MOVE 'Baseball' TO '/var/opt/mssql/data/Baseball_restored.mdf',   
   MOVE 'Baseball_log' TO '/var/opt/mssql/data/Baseball_restored.ldf';  

Once you’ve restored the SQL backup, test the integrity of the database by running DBCC CHECKDB, checking the output for errors. Also see: When Was the Last Known Good DBCC CHECKDB Integrity Check?

DBCC CHECKDB (BaseballData_restored);

For our DBA as a Service customers, we recommend going through this exercise quarterly.

How often should you test SQL Server backup files?

Each environment is different and has it’s own setup of unique requirements. So there isn’t a one-size-fits-all answer here. We generally recommend the following as a good starting point and then adjusting as needed.

1. Checking your SQL backup jobs. Do this daily. Without a backup file, you cannot restore. Don’t rely exclusively on job failure notifications. Make sure the backup jobs completed successfully.
2. Verifying the backup file. Spot check a few key backup files at least monthly to ensure that the files are well-formed and complete.
3. Proving your backups with a test restore. Depending on your systems and the storage requirements, performing a test restore and running an integrity check against the restored database every three or four months is worthwhile exercise.

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.