How to backup the Transaction log in SQL Server?

In this article, you can read about “how to backup the last transaction log” in Microsoft SQL server database 2000 & 2005, and importance of transaction log file.

A transaction log file in SQL server is a file that contains all the information related to the changes made on the database, like update, delete, rename, insert, drop, and all. The file extension of the transaction log file is .ldf. In MS SQL server 2000 and 2005, if the master database and data files of user database are corrupt, and the transaction log file of the database is safe then a database administrator is still able to backup the last active transaction log of the database. Transaction log file backup reduces the amount of data loss. It also plays an important role in SQL server database recovery process.

A database administrator can make the backup of last transaction log in MS SQL server 2000 & 2005 by following these steps:

• Rename the transaction log files of SQL server database.
• Rebuild the master (system) database.
• Create a new database with same number of data and log file.

Note:  The new database does have the same size.

• Stop the SQL server database.
• Delete all the data files of new database.
• Replace the log files of newly created database with old log files.
• Backup the transaction log files.
• Restart the SQL server database.
• Run given below command.

“Backup Log to Disk = With NO_TRUNCATE”

• Remove the database by using sp_dbremove stored procedure.
• Now you have backup of last transaction log.

Importance of Transaction Log: There are so many importance of transaction log in SQL server database. Some of them are as follows:

1. You can undo all the changes made on the data files with the help of transaction log.
2. It is helpful in restoring the database.
   Note: It helps in the restoring process but not restore database without data files.
3. You can reduce the size of transaction log easily.