CRM Database Backup Restore Audit
CRM Database Backup Restore Audit
There are multiple instances where the CRM was no more accessible right after some database restore operations activity. Also, there are instances where in order to restore the databases to previous state or creating test environment you have accidently restored wrong database.
Let's say you have many automated backup files and manual backups in the backup folder location and you not sure if you or other admin user have restored wrong date backup file and CRM became inaccessible, then you can use below few steps to audit your database restore and backup history and isolate the issue.
Using below script you can keep a track of all the databases that were restored. With restore date-time, it will also show the date-time of the backup file that was used to restore. This will help you identify if you have by mistaken restored any wrong date-time database.
Also, using this report you can audit if any critical database was restored which might have caused the downtime. For example, in CRM all the connection related information is stored in MSCRM_CONFIG Database and each CRM environment will have a unique MSCRM_CONFIG database holding the configuration and setting information. If MSCRM_CONFIG database is restored using backup file of old date or backup of other CRM Environment, then it will break down the whole CRM environment. In such cases below steps will help you isolate and troubleshoot the issue.
Method-1: Using SQL Script
use msdb;
select
DBRestored = destination_database_name,
RestoreDate = restore_date,
SourceDB = b.database_name,
SourceFile = physical_name,
BackupDate = backup_start_date
from RestoreHistory h
inner join BackupSet b
on h.backup_set_id = b.backup_set_id
inner join BackupFile f
on f.backup_set_id = b.backup_set_id
order by RestoreDate
go
Method-2: You use Database management reports to pull Backup-Restore history