August 28, 2015 Leave a comment
Interesting questions don’t always need to be hard to answer. Earlier this week I was asked “How can I see what backup was restored on environment X?”. The answer is as interesting as the question…
To see what backups are taken, you can take a look at the history of your maintenance job (depending on whatever type you use in your environment). But there is another way: just ask SQL Server.
First, let’s create a new database named Sandbox (before you run the scripts in this blog post, change or create the directories that are used!):
USE master GO CREATE DATABASE Sandbox ON PRIMARY (NAME = N'Sandbox', FILENAME = N'C:\Database\Sandbox.mdf', SIZE = 4096KB, FILEGROWTH = 1024KB) LOG ON (NAME = N'Sandbox_log', FILENAME = N'C:\Database\Sandbox_log.ldf', SIZE = 1024KB, FILEGROWTH = 1024KB) GO
Before we start to backup this database, I want to make sure the information I see is only for this backup. In order to do so, I’m going to cycle the SQL Server error log, and clean all my backup history from msdb. You can do that by running these statements:
EXEC sys.sp_cycle_errorlog EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '2016-01-01'
By setting the “@oldest_date” to a date in the future, all backup history will be deleted.
Now that we’ve done that, let’s take a backup of our database:
BACKUP DATABASE [Sandbox] TO DISK = N'C:\Database\Backup\Sandbox_2010828_0832.bak' WITH STATS = 10; GO
The backup will complete in an instant, because there is no data to backup:
The result of the backup can be found in 2 locations. The first one is the SQL Server error log:
If you search in the error log you can find the exact time the backup was taken, the amount of pages that were processed, the location of the backup file, etc (image clickable for larger version):
The SQL Server error logs are also available from the SQL Server Management Studio (SSMS). If you connect to your instance with the object explorer, and navigate to Management -> SQL Server Logs, you can find the same information.
The second location you can use to retrieve this information is msdb. The msdb contains the history of your backups:
You can retrieve this information by running a query on the backup-tables:
SELECT B.user_name, B.database_name, M.physical_device_name, B.backup_start_date, B.backup_finish_date, CASE B.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS BackupType FROM msdb.dbo.backupset B INNER JOIN msdb.dbo.backupmediafamily M ON M.media_set_id = B.media_set_id
The information for restored backups can also be found in the same 2 locations. Let’s take a backup of the Sandbox database first:
USE master GO RESTORE DATABASE Sandbox FROM DISK = N'C:\Database\Backup\Sandbox_2010828_0832.bak' WITH REPLACE, STATS = 10 GO
If we look at the SQL Server error log again we can find the restore time, used backup, etc:
We can also query the msdb again. But this time, we need to use the restore-tables:
SELECT destination_database_name, user_name, restore_date, destination_phys_name FROM msdb.dbo.restorehistory H INNER JOIN msdb.dbo.restorefile F ON F.restore_history_id = H.restore_history_id
This shows you all recent restores on your instance:
Being able to retrieve the information you need to determine which database was backed-up or restored can help you solve some weird issues. In my case, it explained why a certain record wasn’t restored on environment X: it was inserted after the backup was taken. Issues like that can be quite hard (or even impossible) to find without information about backups and restores.