3 ways to verify that a backup has taken place

As data professionals responsible for SQL Server, it is drilled into our heads that we need to take backups. But, how do we know we actually have backups available to us when we need them? How can we verify that a backup has been taken? Some types of auditing that an employer has to undergo might require proof of backups. How will you provide it?

Here are 3 ways I can think of off the top of my head.

1. Query msdb:

SELECT  d.[name] AS DatabaseName ,
COALESCE(CAST(MAX(b.backup_finish_date) AS VARCHAR(25)),'No Backup Ever Made') AS LastFullBackup
FROM master.sys.databases AS D
LEFT OUTER JOIN msdb.dbo.backupset AS B ON D.name COLLATE SQL_Latin1_General_CP1_CI_AS = B.database_name COLLATE SQL_Latin1_General_CP1_CI_AS

AND B.type = 'D' /*Full backup*/
AND B.server_name = SERVERPROPERTY('ServerName') /*Backupset ran on server you're currently connected to. */

WHERE D.[name]<> 'tempdb'  /* Eliminate TempDB. No need to back that up */

GROUP BY d.name;

And here are the results, which show that I took a full backup just before writing this blog post.

Last Database Backup Query Results

2. Look in the database properties in SQL Server Management Studio

For this one, open SSMS and connect to SQL Server. Click the “+” next to the Databases folder. Now right click the database you want to check and left click on “Properties”. Now you will see something like this:

last database backup time

This information updates when you take a full backup. As a test, I took a differential backup  and this field did not update. As you can see, there is a place on this properties page for the last date and time a transaction log backup was taken.

3. View the target directory listed in your backup script.

Here is the script I used to take the full backup earlier.

BACKUP DATABASE [AdventureWorks2017] TO  DISK = N'F:\SQL Backups\SQL 2017\AdventureWorks2017-Full.bak'
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2017-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

So, I’ll need to crack open Windows explorer and browse to that location on my PC to see if there’s actually a backup there.

Last Database Backups in Windows Explorer

If you say to me, “Why would I want to manually open the directory to look? Isn’t there a different way?” Well, yes! There is!

Get-ChildItem -Path 'F:\SQL Backups\SQL 2017'

Last Database Backups Via PowerShell

Now, I know that’s not a full solution using PowerShell. I just wanted to show you that it is possible to get the answer to your question about what files are in the SQL Backup directory by using PowerShell. If you want to build a full solution with PowerShell, great! Go do that, and when you do, share it with the community, please.

That’s it for this blog post. Short, sweet, to the point!