Restoring the MS SQL Server database using transaction logs

Restoring the MS SQL Server database using transaction logs

It becomes more crucial when we loose our important information, that too if lost from the production database.Oops we go panic!!!!!!!

We have a method of restoring the SQL server database using the transaction logs.The restoration process is illustrated below, assuming the database recovery mode is set to FULL .

Restoration process

The primary step in this process is to perform the back up of the existing database, which has to be included in the restoring process.
The secondary step is to locate database back up files in the machine or in the network.Else it can also be copied to the target server if restoring of the database takes place in the different server.The script below applies the full backup file to the New database database.

RESTORE DATABASE SampleDatabase
FROM DISK = 'D: \BackupFiles\TestDatabaseFullBackup.bak'
WITH
MOVE 'PreviousDatabase' TO 'D:\DataFiles \TestDatabase.mdf',
MOVE 'PreviousDatabase_log' TO 'D:\DataFiles \TestDatabase_Log.ldf',NORECOVERY

The above code specifies the location of the full back up database is on the server's D drive and we are restoring files to the database named sampledatabase.The above code also moves both the data file and the log file from the full backup tonew files for the TestDatabase database.The last statement in the above script,NORECOVERY, is actually one of the modes implemented regarding the status of the restoration.

NORECOVERY : Informs SQL Server that the restoring the database is not finished and subsequent restore will occur.When the database is in this state, the database is not available for access and no connections are allowed.

RECOVERY: Informs SQL Server that the restoring the database is finished and ready to use.

STANDBY:Informs SQL Server that current database is not ready and yet sot be recovered and further log files has to be restored.

Once restoring the fullback using the NORECOVERY option is done, then the transaction log back ups or the differential backup

Differential Backup : This is a backup of any changes to the database that have occurred since the last full database backup.Suppose if there are multiple differential backups,the last one is restored.In this situation, as there are no differential backups, so it can be moved directly to the transaction log back ups

Transaction log backups: This keeps in track of all the transactions that have occurred since last transaction log backup.

RESTORE LOG SampleDatabase
FROM DISK = ''D: \BackupFiles\TestDatabase_TransactionLogBackup1.trn'WITH NORECOVERY
RESTORE LOG SampleDatabase
FROM DISK =''D: \BackupFiles\ TestDatabase_TransactionLogBackup2.trn'WITH NORECOVERY
RESTORE LOG SampleDatabase
FROM DISK =''D:\BackupFiles\ TestDatabase_TransactionLogBackup3.trn'WITH NORECOVERY
RESTORE LOG SampleDatabase
FROM DISK = ''D: \BackupFiles\TestDatabase_TransactionLogBackup4.trn'WITH RECOVERY

In the above situation, restoring of the database occurs at the end of the last transcation log.If the database recovery has to occur to a specific point before the end of the transaction log, we can utilize the STOPAT option

RESTORE LOG SampleDatabase
FROM DISK =''D: \BackupFiles\ TestDatabase_TransactionLogBackup4.trn'
WITH STOPAT = N'6/28/2007 4:01:45 PM', RECOVERY

In the above situation the restoring the database occurs at the fourth transaction log in the log sequence to 4:01 PM.