You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q253817
SUMMARY
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.
Properties

Article ID: 253817 - Last Review: 09/11/2006 20:39:24 - Revision: 6.6

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • kbhowtomaster KB253817
Feedback