Skip to content

Auditing database restore in SQL Server instance

How to confirm if someone have restored a database? Which database backup file is restored? WHo did the restore? When was the database last restored?

To find the answer of all these questions you may have to run couple of queries shown below.
Please make sure you run these queries under MSDB database.

1) Below query will give us a details of which all databases has ben restored, when these were restored, who restored it and the backup set which was restored.

select restore_date,destination_database_name,user_name,
backup_set_id, restore_type, recovery
 from restorehistory

OUTPUT:

restore_date		destination_database_name	user_name	backup_set_id	restore_type	recovery

2012-08-07 13:35:29.013	TestDB_RC1		TestID_8181	358			D	1	1
2012-08-07 16:39:49.370	TestDB_RC1		TestID_5357	358			D	1	0
2012-08-07 16:41:19.680	TestDB_RC1		TestID_5357	372			I	0	1

D – Full database backup applied
I – DIfferential backup applied

2) Now to find when was these backups taken, just run the below query. Hre you may have to copy the desired backup set number from the first query.
NOTE: If no result is displayed then possibly the backup which was restored is not from this instance. That mean a backup was taken in some other environment and then restored in current.

 select backup_set_id,name, backup_start_date,backup_finish_date, 
 type, database_name from backupset
 where backup_set_id = 358 or backup_set_id = 372

OUTPUT:

backup_set_id	name						backup_start_date	backup_finish_date	type	database_name

358		TestDB_backup_2012_07_07_133002_2720281	2012-07-07 13:30:08.000	2012-07-07 13:30:22.000	D	TestDB
372		TestDB_backup_2012_07_08_230001_5563963	2012-07-08 23:00:17.000	2012-07-08 23:00:19.000	I	TestDB

3) Additionally you can look into the SQL Server error log to find when was database restored and the location where database backup file was placed for this restore.

2012-08-07 16:39:59.44 spid64      The database 'TestDB_RC1' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-08-07 16:39:59.90 Backup      Database was restored: Database: TestDB_RC1, creation date(time): 2012/05/14(12:51:48), first LSN: 11037:12735:86, last LSN: 11037:12771:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestServer\MSSQLSERVER\TestDB\TestDB_backup_2012_07_07_133002_2720281.bak'}). Informational message. No user action required.
2012-08-07 16:41:22.15 spid51      Starting up database 'TestDB_RC1'.
2012-08-07 16:41:22.15 spid51      The database 'TestDB_RC1' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-08-07 16:41:22.68 spid51      Starting up database 'TestDB_RC1'.
2012-08-07 16:41:23.16 spid51      CHECKDB for database 'TestDB_RC1' finished without errors on 2012-07-07 11:00:16.783 (local time). This is an informational message only; no user action is required.
2012-08-07 16:41:23.16 Backup      Restore is complete on database 'TestDB_RC1'.  The database is now available.
2012-08-07 16:41:23.18 Backup      Database changes were restored. Database: TestDB_RC1, creation date(time): 2012/05/14(12:51:48), first LSN: 11040:10549:56, last LSN: 11040:10573:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestServer\MSSQLSERVER\TestDB\TestDB_backup_2012_07_08_230001_5563963.diff'}). This is an informational message. No user action is required.