Backup and Restore are the common tasks that a DBA needs to perform almost daily. In this blog I’ll talk about how to restore a transaction log in SQL Server.
A transaction log backup can only be restored when a database is in restoring database. That means a full backup or a differential backup has been restored with NO RECOVERY option. A T-SQL script to restore transaction log backup is shown below.
The below error occurs if you try to restore transaction log on a recovered/online database.
A T-SQL script to restore a single transaction log backup is given below.
RESTORE LOG [AdventureWorks2014] FROM DISK='e:\ahmad\AdventureWorks2014_log.trn'
A T-SQL script to restore multiple transaction log backup is given below.
RESTORE LOG [AdventureWorks2014] FROM DISK='e:\ahmad\AdventureWorks2014_log.trn' WITH NORECOVERY GO RESTORE LOG [AdventureWorks2014] FROM DISK='e:\ahmad\AdventureWorks2014_log_2.trn'
When restoring multiple T-Log files the log files are restored in sequence the backup was taken with NORECOVERY option so as to allow restoring other log backups and the last backup is restored with RECOVERY option to recover the database and bring it online.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
