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.
