Tail Log Backup in SQL Server 2000 or in SQL Server 2005 when Master DB or User DB is corrupted\Not available 

AS we know, we can take the Tail Log Backup if the database file is damaged, provided that the transaction log file is still available. But what will you do if the master database is also damaged; then 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 is not available, you can take Tail Log Backup of the database by using the below mentioned method.

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.

To take Tail Log Backup 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 <your DB Name> to Disk = <path\device name> With NO_TRUNCATE

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

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.

Regards,

Sarabpreet Singh