Introduction

What a day! Our Backups Work  🙂  Not the way I would like to of tested them though! …. Today we made a mistake, instead or recovering a set of database backup files to a drive from tape, we managed to start the recovery of the live app database from tape. As a result Syteline stopped working for all the users – no surprise there!

The good news is out backups happen every night, and our tlog backup happen every 30 minutes, so we knew we should be able to get out of the situation, we just have never done it before. We have regularly recovered from tape to ensure we have the database, but we have always restored to a test location. The unknown was the transaction that occurred post backup – we really did not want to loose half a days work – after all this happened at 12:15 and people have been working since 07:30.

After a frantic search around Google, and about 2 hours (due to a few false starts) we did it.

The first thing to note is the “no recovery” option after each statement but the last. In simple terms, setting this keeps the database offline and keeps it in a state that allows more work to be carried out on it.

The replace switch is obvious enough. It effectively drops the current database.

The transaction logs then need to be recovered In sequence, from the point of the backup. You can do this via the GUI, but it is long winded and easy to make a mistake in my opinion. We also have our transaction log backups stored on a different server (in case of disk failure) …… Move them to a local drive before restoring them.

Once each transaction has been restored, run the restore for the database with recovery.

The Code

RESTORE DATABASE
FROM DISK = ‘D:\MSSQL\Backup\ServerName–20131028.210637.520-FULL.bak’
WITH replace, NORECOVERY;

RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131028.212546.636-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131028.213020.741-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131028.223024.784-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131028.223114.364-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131028.230022.160-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131028.233024.603-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.000021.492-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.063116.267-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.070021.190-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.073020.758-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.080021.268-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.083021.011-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.090022.175-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.093021.793-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.100023.081-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.103031.358-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.110033.395-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.113042.156-TLOG.bak’ WITH NORECOVERY;
RESTORE LOG DatabaseName FROM disk = ‘D:\MSSQL\Data\temp\ServerName-DatabaseName-20131029.120036.020-TLOG.bak’ WITH NORECOVERY;
RESTORE DATABASE DatabaseName WITH RECOVERY

Thoughts

I’d be really interested if there is a better way! Comments and feedback greatefully received.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.