MS SQL Database Full Recovery from the TRANSACTION LOGS ( Tested with MsSQL - 2005, 2008 )

MsSQL Database full Recovery from transaction logs

Spl.Note :-

I am assuming that your Database recovery mode is set to FULL and not the SIMPLE.

You have a database which is set to FULL recovery and u have a full Database backup on say Dec 2009 after

that u have regularly backed up its transactional logs and suddenly your database crashed or by any chance it is being altered.,

in this scenario u need to get the changes made to your database on a previous day.

in this conditions you must be having database complete backup which is taken on Dec 2009 or any latest full database backup and transactional logs till today, in our need we assume that the database restore requirement is till yesterday and not today as i discussed above.

Now its time to start the Restore Database operations.

Step:-1

RESTORE DATABASE YOUR_DATABASE_NAME

FROM DISK = 'D:\YOUR_DATABASE_PATH\YOUR_FULL_DATABASE_BACKUP.bak'

WITH NORECOVERY

The above command will results in following output

Processed 184 pages for database 'YOUR_FULL_DATABASE_BACKUP', file 'YOUR_FULL_DATABASE_BACKUP' on file 1.

Processed 6 pages for database 'YOUR_FULL_DATABASE_BACKUP', file 'YOUR_FULL_DATABASE_BACKUP_log' on file 1.

RESTORE DATABASE successfully processed 190 pages in 0.205 seconds (7.233 MB/sec).

Once you restore the full backup using the NORECOVERY option, you can begin applying the transaction log backups or the differential backup as given below.

Step:-2

RESTORE LOG YOUR_DATABASE_NAME

FROM DISK = 'D:\YOUR_DATABASE_PATH\Transaction_Logs_1.trn' WITH NORECOVERY

The above command will results in following output

Processed 0 pages for database 'YOUR_DATABASE_NAME', file 'YOUR_DATABASE_NAME' on file 1.

Processed 7 pages for database 'YOUR_DATABASE_NAME', file 'YOUR_DATABASE_NAME_log' on file 1.

RESTORE LOG successfully processed 7 pages in 0.017 seconds (2.900 MB/sec).

Step:-3

RESTORE LOG YOUR_DATABASE_NAME

FROM DISK = 'D:\YOUR_DATABASE_PATH\Transaction_Logs_2.trn' WITH NORECOVERY

The above command will results in following output

Processed 0 pages for database 'YOUR_DATABASE_NAME', file 'YOUR_DATABASE_NAME' on file 1.

Processed 3 pages for database 'YOUR_DATABASE_NAME', file 'YOUR_DATABASE_NAME_log' on file 1.

RESTORE LOG successfully processed 3 pages in 0.018 seconds (1.247 MB/sec).

Step:-4

i m assuming Transaction_Logs_3.trn is the file which we want to get the database data till yesterday,

one more file Transaction_Logs_4.trn is there but our requirement is till file Transaction_Logs_3.trn only

RESTORE LOG YOUR_DATABASE_NAME

FROM DISK = 'D:\YOUR_DATABASE_PATH\Transaction_Logs_3.trn' WITH RECOVERY

The above command will results in following output

Processed 0 pages for database 'YOUR_DATABASE_NAME', file 'YOUR_DATABASE_NAME' on file 1.

Processed 2 pages for database 'YOUR_DATABASE_NAME', file 'YOUR_DATABASE_NAME_log' on file 1.

RESTORE LOG successfully processed 2 pages in 0.064 seconds (0.190 MB/sec).

after the successfull operations of the above step the database is now ready for use,

till the time it is in RECOVERYMODE the same will not be available for use.

In the example above, we restore the database to the end of the 2nd last transaction log.

If we want to recover our database to a specific point in time before the end of that transaction log,

then we must use the STOPAT option.

The script below restores the fourth transaction logs in the log sequence to 3:00 AM - time just before the database gets currupted.

RESTORE LOG YOUR_DATABASE_NAME

FROM DISK = 'D:\YOUR_DATABASE_PATH\Transaction_Logs_4.trn'

WITH STOPAT = N'8/30/2010 3:00:00 AM', RECOVERY