This article covers the basics of Transaction Log backups and restores in SQL Server. The examples are from SQL Server 2008 however it applies to SQL Server 2005. If a database uses either the full or bulk-logged recovery model, you must back up the transaction log regularly enough to protect your data and to keep the transaction log from filling.
Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears. In the Backup type list box, select Transaction Log.
Execute the BACKUP LOG statement to back up the transaction log, specifying the following:
The name of the database to which the transaction log that you want to back up belongs. The backup device where the transaction log backup is written.
BACKUP LOG <database_name> TO <backup_device>
GO
Restore the database by using the most recent full database backup by selecting RESTORE WITH NORECOVERY option.
Then Restore the Transaction Lob Backup selecting RESTORE WITH RECOVERY option. In some cases, you can also use more than one transaction logs to restore a database to a specific point in time. In that case you need to keep the database in NORECOVERY Stage.
RESTORE DATABASE <database_name> FROM <backup_device> WITH NORECOVERY;
GO
RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY;
GO
Restoring a log backup rolls forward the changes that were recorded in the transaction log to re-create the exact state of the database at the time the log backup operation started. When you restore a database, you will have to restore the log backups that were created after the full database backup that you restore, or from the start of the first file backup that you restore.
Typically, after you restore the most recent data or differential backup, you must restore a series of log backups until you reach your recovery point. Then, you recover the database. This rolls back all transactions that were incomplete when the recovery started and brings the database online. After the database has been recovered, you cannot restore any more backups.
Related Posts
- (DBA TIPS) Maintenance Plan to avoid Transaction log file growth
- Split Backup Files while Backup and Restore SQL Server Database
- Backup SQL Server Database using SSMS
- How to Verify the SQL Server Backup
- Backup SQL Server database using T-SQL
Tags: backup transaction log, log backup, restore transaction log, SQL Server 2008, SQL Server Backup, SSMS Backup, Transaction log backup, TSQL Backup
