Restore a SQL database with audit logs and a point in time.

If your reading this, you are having a bad day.  Lets say your day went to hell in a handbasket at 1.10 in the afternoon.

Here is how you recover your CRM,etc. database to a point in time...


--this restores the database, but leaves the database in a “in recovery’ state.

RESTORE DATABASE SolomonApplication
FROM DISK = 'F:\FryeRestore0913\SolomonApplication_backup_201009121930.bak'
WITH
MOVE 'SolomonApplication_Data' TO 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SolomonApplication.mdf',
MOVE 'SolomonApplication_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SolomonApplication_1.ldf',
NORECOVERY

--this takes the restored database and moves it forware in time to the time of the log. Again the database is not ready yet...

RESTORE LOG SolomonApplication
FROM DISK = 'F:\FryeRestore0913\SolomonApplication_backup_201009130900.trn'
WITH NORECOVERY
… do for each log….

RESTORE LOG SolomonApplication
FROM DISK = 'F:\FryeRestore0913\SolomonApplication_backup_201009131231.trn'
WITH RECOVERY

---or --- to a pont in time
RESTORE LOG SolomonApplication
FROM DISK = 'F:\FryeRestore0913\SolomonApplication_backup_201009131231.trn'
WITH RECOVERY ,
STOPAT = 'Sept 10, 2010 13:01:00 PM'


Hey, now your day is a bit better.