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