How to back up the last transaction log when the master and the database files are damaged in SQL Server

You can back up the currently active transaction log even if the database file is damaged, provided that the transaction log file is still available. On Microsoft SQL Server 7.0, both the primary data file and the transaction log files must be available in order for you to back up the last active transaction log. If the master database is also damaged, you can rebuild and restore the master database first, and then back up the last active transaction log of the unavailable database. For example, both the data file and the master device might be on damaged media. However, if a master database backup is not available, you can back up the last active transaction log of the database by using the method that is discussed in this article, provided that both the primary data file and the transaction log files are available on SQL Server 7.0.

Note To do to this in Microsoft SQL Server 2000 or in Microsoft SQL Server 2005, only the transaction log file must be available.

Note The Rebuild Master utility is implemented differently in Microsoft SQL Server 2000 64 bit. In SQL Server 2000 64 bit, SQL Server takes advantage of the self-repairing features that are provided by Microsoft Windows Installer. Additionally, the REINSTALL property and the REINSTALLMODE property on the Setup command line are available to explicitly rebuild the registry, reinstall corrupted files or missing files, reinstall corrupted shortcuts or missing shortcuts, and rebuild the master databases. These properties can be specified on the command line or in an .ini file.

Back up the last transaction log in SQL Server 2000 or in SQL Server 2005

In SQL Server 2000 and in SQL Server 2005, if the master database and the data file of a user database are bad, but the transaction log file of the database is still available, you can still back up the last active transaction log of the database to reduce the loss of data. If the master database is not bad, see the "Backup log" topic in SQL Server Books Online.

To back up the last transaction log in SQL Server 2000 or in SQL Server 2005, follow these steps:

1. Rename the transaction log files.
2. Rebuild the master database.
3. Create a similar database. The new database does not have to be the same size; however, it must contain the same number of data and log files.
4. Stop SQL Server 2000 or SQL Server 2005.
5. Delete all the data files of the newly created database so that it will fail recovery. Replace the log files of the new database with the original log files so that you can back up the transaction log.
6. Restart SQL Server 2000 or SQL Server 2005.
7. Run this command to back the tail of the log:

   Backup Log <database_name> to Disk = <device_name> With NO_TRUNCATE

8. Use the sp_dbremove stored procedure to remove the database.

Back up the last transaction log in SQL Server 7.0

To back up the last active transaction log in SQL Server 7.0, follow these steps:

1. Rename the primary data file and transaction log files.
2. Rebuild the master database.
3. Create a similar database. The new database does not have to be the same size; however, it must contain the same number of data and log files.
4. Stop SQL Server 7.0.
5. Delete all the data files of the newly created database so that it will fail recovery. Replace the primary data file and log files of the new database with the original one so that you can back up the transaction log.
6. Restart SQL Server 7.0.
7. Run this command to back the tail of the log:

   Backup Log <database_name> to Disk = <device_name> With NO_TRUNCATE

8. Use the sp_dbremove stored procedure to remove the database.
9. Restore the database with all the log files.