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:
- Rename the transaction log files.
- Rebuild the master database.
- 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.
- Stop SQL Server 2000 or SQL Server 2005.
- 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.
- Restart SQL Server 2000 or SQL Server 2005.
- Run this command to back the tail of the log:
Backup Log <database_name> to Disk = <device_name> With NO_TRUNCATE
- 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:
- Rename the primary data file and transaction log
files.
- Rebuild the master database.
- 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.
- Stop SQL Server 7.0.
- 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.
- Restart SQL Server 7.0.
- Run this command to back the tail of the log:
Backup Log <database_name> to Disk = <device_name> With NO_TRUNCATE
- Use the sp_dbremove stored procedure to remove the database.
- Restore the database with all the log files.