[轉錄] MSSQL How to restore a SQL Server transaction log backup

How to restore a SQL Server transaction log backup   

Ref: http://www.mssqltips.com/sqlservertutorial/111/how-to-restore-a-sql-server-transaction-log-backup/   (RESTORE LOG)

Overview

The RESTORE LOG command allows you to restore a transaction log backup.  The options include restoring the entire transaction log or to a certain point in time or to a certain transaction mark. 

Explanation

When restoring a transaction log you will need exclusive access to the database, which means no other user connections can be using the database.  If the database is in a restoring state this is not an issue, because no one can be using the database.

The RESTORE LOG option can be done using either T-SQL or using SQL Server Management Studio.

---

T-SQL

Restore a transaction log backup
To restore a transaction log backup the database need to be in a restoring state.  This means that you would have to restore a full backup and possibly a differential backup as well.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'

GO

Restore multiple transaction log files (NORECOVERY)
The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off. As was mentioned above the database needs to be in a restoring state, so this would have already been done for at least one backup file that was restored.

This shows restoring two transaction log backups, the first using NORECOVERY and the second statement does not which means the database will be accessible after the restore completes.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERY

GO

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'

GO

Restore a differential backup
To restore a differential backup, the options are exactly the same. The first thing that has to happen is to do a full restore using the NORECOVERY option. Then the differential can be restored.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY

GO

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'

GO

Restore multiple transaction log backups from the same backup file
Let's say we use the same backup file, AdventureWorks.TRN, to write all of our transaction log backups.  This is not a best practice, because if the file is corrupt then this could corrupt all of your backups in this file.  We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let's say that the restore headeronly tells us that we have 3 transaction log backups in this file and we want to restore all  three.  The restore commands would be.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 1

GO

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 2

GO

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH FILE = 3

GO