When the primary data MDF file of the database is intact, how do you use the MDF file to recover your database when you lose your LDF file? We divide SQL Server's log files into two categories: one is a log that is active, and the other is a log that contains active transactions. Different methods are taken according to different logs to restore the database.
1. Log restoration without active affairs
When there is a log loss without active transaction, we can easily use the MDF file to directly recover the database, the specific method is as follows:
1. Separate the database, the "separation database tool" in the Enterprise Manager, or separate the database with the stored procedure sp_detach_db;
2 MDF file additional database Generate new log files, the "Additional Database" tool in the Enterprise Manager, or uses a stored procedure sp_attach_single_file_db to attach a database.
If you contain an active transaction in the log file of the database, you cannot recover the database.
2. Logging in an event-containing affairs
When the log is lost, the "database and log files do not match, and the database cannot be attached" with the above method. In this case, we use the following methods:
1 Newly build the same name database SSS, and set it to emergency mode
· Stop SQL Server Server;
· Remove the database master data MDF file;
· The SQL Server server, newly created a database of database SSS;
· Stop the SQL Server server, cover the removed MDF file;
· Start the SQL Server server, set the SSS as an emergency mode, but by default, the system table cannot be modified casually. You must first set it to be modified, run the following statement:
Use mastergosp_configure 'allow updates', 1Reconfigure with overridego
The following statement is then run, set the SSS database to emergency mode, that is, set the STATUS attribute of the SSS database in the MyData table to '37268', indicating that the SSS database is in urgent mode.
Update mydata set status = 32768 where hame = 'sss'
If there is no error report, you can do the following.
2 Set the database SSS as a single user mode, and check the database
· Restart the SQL Server server;
· Set the database SSS as single user mode
SP_DBOPTION 'SSS', 'Single User', 'True'
· Run the following statement, check the database SSS
DBCC Checkdb ('sss')
If there is no big problem, you can change the status of the database.
3 Restore the status of the database
Run the following statement, you can restore the status of the database:
Update mydata set status = 28 where name = 'sss'sp_configure' allow updates', 0Reconfigure with overridego
If there is no big problem, refresh the database, the database SSS will appear in front of you, but the current recovery work has not been completed. At this time, the database still does not work, but also the following processing can be truly recovered.
4 Import the database SSS into a new database BBB using DTS import wizard
· Create a new database BBB;
· Right-click on BBB, select the import function, open the import wizard;
· Target Source Select "Replication Objects and Databases between SQL Server Database", which can import table structure, data view, and stored procedures to BBB. Use this function to replace the BBB library to the original SSS library.
The database SSS is completely recovered.
