SQL Server log file is lost. Recovery method

The following article is mainly about the correct recovery method for SQL Server log file loss. In actual operation, the loss of SQL Server log files is a very difficult thing. The following articles are mainly for the answer given by this question. The following is the main content description of the main text.

I. Overview

In the application system, the database is often the core part. Once the database is destroyed or damaged, it will bring huge losses, so the management of the database is more and more important. . In the database management and maintenance work, we will inevitably have various errors. This paper studies how to use the MDF file to recover the database when the database SQL Server log file is lost.

Second, the recovery of the database

When the database main data MDF file is intact, how to use the MDF file to recover the database in the case of losing the LDF file? We divide the SQL Server log files into two categories: one is the log with no active transactions, and the other is the log with active transactions. According to different logs, different methods are used to recover the database.

1. Log Recovery Without Active Transactions

When logs with no active transactions are lost, we can easily recover the database directly using MDF files, as follows:

1. Separate the suspected database, use the "detach database tool" in the enterprise manager, or use the stored procedure sp_detach_db to separate the database;

2 use the MDF file to attach the database to generate a new log file, available in the Enterprise Manager The "attach database" tool, or attach the database with the stored procedure sp_attach_single_file_db.

If the database log file contains active transactions, this method can not recover the database from the SQL Server log file loss.

2. Log Recovery with Active Transactions

When the log containing active transactions is lost, the above method will appear "The database and log files do not match, you cannot attach the database". For this In the case, we use the following method:

1 Create a database with the same name AAA, and set it to emergency mode

Stop the SQL Server server;

Move the database master data MDF file

Start SQL Server server, create a new database with the same name AAA;

Stop the SQL Server server and overwrite the removed MDF file;

Start SQL Server Server, set AAA to emergency mode, but by default, the system table can not be modified, you must first set it to be modified, run the following statement:

The following is a code snippet: < Br>

Use Master

Go

sp_configure 'allow updates',1

reconfigure with override

Go

Run the following statement to set the AAA database Emergency mode, that is, the status attribute Sysdatabases AAA database table is set to '37268', it says the AAA database is in emergency mode.

update sysdatabases set status=32768 where hame='AAA'

If no errors are reported, you can do the following.

2Set the database AAA to single-user mode, and check the database

Restart the SQL Server server;

Set the database AAA to single-user mode

Sp_dboption 'AAA', 'single user', 'true'

Run the following statement to check the database AAA

DBCC CHECKDB('AAA')

If there is no big problem You can change the state of the database back.

3Restore the state of the database

Run the following statement to restore the state of the database:

The following is the code snippet:

update sysdatabases set status =28 where name='AAA'

sp_configure 'allow updates',0

reconfigure with override

Go

If there are no big problems, Refresh the database, the database AAA will appear in front of you, but the recovery work has not been completed yet, the database still can not work, but also the following processing, in order to truly recover.

4Using DTS import and export wizard, import database AAA into a new database BBB

Create a new database BBB;

Right click BBB, select IMPORT function, open Import Wizard;

Target Source Select "Copy Objects and Databases Between SQL Server Databases" so that table structures, data views and stored procedures can be imported into BBB

Replace the BBB library with the original AAA library.

At this point, the database AAA is fully restored.

SQL Server log file loss is a very dangerous thing, it is very likely that your database is completely destroyed. Recovery of SQL Server databases is done by log files, so it is important to ensure the existence of log files anyway. In order to make our database foolproof, it is best to use a variety of backup methods, so we must pay attention to the management and maintenance of the database.