Thursday, August 11, 2011

Restore SQL Database After Broken Mirror

Q: During a recent event a Database Mirror was broken using steps previously discussed.  Now we needed to re-establish the mirror.
A: The easiest way was to
  1. Detach database on 2nd SQL Server
  2. Archive mdf/ldf files
  3. Obtain nightly BAK from 1st SQL Server
  4. Obtain every hourly TRN from 1st SQL Server since BAK was generated
  5. Transfer all files to 2nd SQL Server
  6. Restore .bak to 2nd SQL Server - selecting the radio button "RESTORE WITH NORECOVERY"
  7. Restore the .trn file(s) using this script: (updating DBNAME & DBPATH):
  8. RESTORE LOG [DBNAME] FROM  DISK =
    N'D:\DBPATH\Backup_201108110600.trn'
    WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
    GO

  9. Connect to 1st SQL Server and establish the mirror as before.