Teach you to easily recover / repair the MDF files of SQL server

Time:2022-4-28

First of all: if the backup database has two files, they are LDF and MDF, open the enterprise manager, right-click on the instance – all tasks – attach database, and then select that MDF file, you can.

Or enter in the query analyzer:

Copy codeThe code is as follows:
sp_ attach_ DB “database name”, “path \ filename. LDF”, “path \ filename. MDF”

There are two methods for SQL Server database backup: one is to use backup database to back up the database files, and the other is to directly copy the database file MDF and log file LDF. The following will mainly discuss the backup and recovery of the latter. This article assumes that you are proficient in using SQL Server Enterprise Manager and SQL Server quwey analyzer.

1. Normal backup and recovery methods 0

In normal mode, we need to back up a database. First, we need to disconnect the database from the running data server, or stop the whole database server, and then copy the files.

Command to remove database: SP_ detach_ DB database name

Copy codeThe code is as follows:
Command to connect to database: SP_ attach_ DB or SP_ attach_ single_ file_ db
s_attach_db [@dbname =] ‘dbname’,
[@filename1 =] ‘filename_n’ [,…16]
sp_attach_single_file_db [@dbname =]
‘dbname’, [@physname =] ‘physical_name’

Use this method to recover SQL sever7.0 correctly 0 and SQL Server 2000. The key point is that both MDF and LDF files must be backed up during backup. MDF file is the database data file and LDF is the database log file.

example:

Suppose the database is test and its data file is test_ data. MDF, log file is test_ log. ldf。 Let’s discuss how to back up and restore the database.

Copy codeThe code is as follows:
Removing a database: SP_ detach_ db ‘test’
Connect to database: SP_ attach_ db ‘test’,’
C:\Program Files\Microsoft SQL Server\MSSQL
\Data\test_data.mdf‘,’C:\Program Files
\Microsoft SQL Server\MSSQL\Data\test_log.ldf’
sp_attach_single_file_db ‘test’,’
C:\Program Files\Microsoft SQL Server\MSSQL
\Data\test_data.mdf’

2. Recovery technology of only MDF files

For various reasons, if we only backed up MDF files at that time, it would be very troublesome to restore them.

If your MDF file is generated by the current database, it’s lucky that you may use sp_ attach_ DB or SP_ attach_ single_ file_ DB can recover the database, but the following prompt message will appear:

Device activation error.

Copy codeThe code is as follows:
Physical file name ‘C: \ program files \ Microsoft SQL Server
\MSSQL\data\test_ Log. LDF ‘may be incorrect.
Created SQL server named ‘C: \ program files \ Microsoft
\MSSQL\Data\test_ log. New log file for LDF ‘.

However, if your database files are copied from other computers, unfortunately, the above method may not work. You may get an error message like the following:

Copy codeThe code is as follows:
Server: Message 1813, level 16, status 2, line 1
Server: Message 1813, level 16, status 2, line 1 failed to open the new database ‘test’. Create database will terminate.
Device activation error. Physical file name’d: \ test_ log. LDF ‘may be incorrect.

What shall I do? Don’t worry. Let’s illustrate the recovery method with examples.

A. We use the default method to establish a database for recovery (such as test). It can be established in SQL Server Enterprise Manager.

B. Stop the database server.

C. Test the log file of the database just generated_ log. Delete LDF and overwrite the generated database data file test with the database MDF file to be recovered_ data. mdf。

D. Start the database server. At this time, you will see that the status of database test is “doubt”. No operation can be performed on this database at this time.

E. Set the database to allow direct operation of system tables. For this operation, you can select the database server in SQL Server Enterprise Manager, right-click, select “properties”, and select “allow direct modification of system directory” on the “server settings” page. You can also use the following statements.

F. Set test to emergency repair mode

Copy codeThe code is as follows:
update sysdatabases set status=-32768
where dbid=DB_ID(‘test’)

At this time, you can see that the database is in “read only \ doubt \ offline \ emergency mode” in SQL Server Enterprise Manager. You can see the tables in the database, but there are only system tables

G. The following is a real recovery operation to rebuild the database log file:

Copy codeThe code is as follows:
dbcc rebuild_log(‘test’,’C:\Program Files\Microsoft
            SQL Server\MSSQL\Data\test_log.ldf’)

During execution, if you encounter the following prompt information:

Server: message 5030, level 16, status 1, line 1

Failed to lock the database exclusively to perform the operation.

DBCC execution completed. If DBCC outputs an error message, please contact the system administrator.

It indicates that other programs are using the database. If you just opened the system table of the test library using SQL Server Enterprise Manager in step f, you can exit SQL Server Enterprise Manager.

The prompt for correct execution completion should be similar to:

Warning: the log for database ‘test’ has been rebuilt. Transaction consistency has been lost. DBCC CHECKDB should be run to verify physical consistency. The database options will have to be reset, and extra log files may need to be deleted.

DBCC execution completed. If DBCC outputs an error message, please contact the system administrator.

At this point, open SQL Server Enterprise Manager and you will see that the status of the database is “only for dbo use”. At this point, you can access the user table in the database.

H. Verify database consistency (can be omitted)

dbcc checkdb(‘test’)

The general implementation results are as follows:

CHECKDB found 0 allocation errors and 0 consistency errors (in database ‘test’).

DBCC execution completed. If DBCC outputs an error message, please contact the system administrator.

1. Set database to normal state

Copy codeThe code is as follows:
sp_dboption ‘test’,’dbo use only’,’false’

If there is no error, congratulations. Now you can use the restored database normally.

J. Finally, we need to restore the item “allow direct modification of system directory” set in step E.