Two methods of SQL Server disaster recovery

Two methods of SQL Server disaster recovery

If your MDF file is generated by the current database, it is a fluke that you may be able to recover the database using sp_attach_db or sp_attach_single_file_db, but you will receive a message similar to the following:

Device activation error. The physical file name ' C:\Program Files\Microsoft SQL Server\mssql\data\test_log.ldf ' may be incorrect.

A SQL server\mssql\data\test_log named ' C:\Program Files\Microsoft has been created. LDF ' new log file.

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

Server: Msg 1813, Level 16, State 2, line 1

Failed to open new database ' test '. The CREATE DATABASE terminates.

Device activation error. Physical file name ' D:\test_log. LDF ' may be wrong.

What do we do? Don't worry, here are some examples of how to recover.

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

B. Shut down the database server.

C. test_log.ldf delete the log file for the database you just generated, overwriting the database data file test_data.mdf that you just generated with the database MDF file you want to recover.

D. Start the database server. You will see that the status of the database test is "suspect". No action can be made on this database at this time.

E. Setting up a database allows direct

Operating system tables. You can select the database server in SQL Server Enterprise Manager, right-click, select Properties, and in the Server Settings page, select the Allow direct modification to system directory. You can also use the following statement to implement.

Use master

Go

sp_configure ' allow updates ', 1

Go

Reconfigure with override

Go

F. Set test to Emergency Repair mode

Update sysdatabases set status=-32768 where dbid=db_id (' test ')

In SQL Server Enterprise Manager, you can see that the database is in read-only \ suspect \ offline \ Emergency mode and you can see the tables in the database, but only the system tables.

G. Perform a real recovery operation below to rebuild the database log file

DBCC REBUILD_LOG (' Test ', ' C:\Program Files\Microsoft SQL Server\mssql\data\test_log.ldf ')

During the execution, if you encounter the following prompt message:

Server: Msg 5030, Level 16, State 1, line 1

Failed to lock the database in order to perform the operation.

DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

Indicates that your other program is using the database, and if you have just opened the system table for the test library using SQL Server Enterprise Manager in the F step, exit SQL Server Enterprise Manager.

The correct execution of the completion prompt should resemble the following:

Warning: The Log for database ' test ' has been rebuilt. The consistency of the transaction has been lost. You should run DBCC CHECKDB to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files.

DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

When you open the SQL Server Enterprise Manager, you see that the status of the database is "only for dbo use." You can now access the user tables in the database.

H. Verifying database Consistency (can be omitted)

DBCC CHECKDB (' test ')

General implementation results are as follows:

CHECKDB found 0 allocation errors and 0 conformance errors (in database ' test ').

DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

I. Setting the database to a normal state

sp_dboption ' test ', ' dbo use only ', ' false '

If there is no error, then congratulations, you can now normal use of the restored database.

J. In the final step, we will restore the "allow direct modification to the system directory" setting in step e. Because the direct operating system table is a relatively dangerous thing. Of course, we can recover from SQL Server Enterprise Manager, or we can do this using the following statement:

sp_configure ' allow updates ', 0

Go

Reconfigure with override

Go

Share from multi-backup forums

Two methods of SQL Server disaster recovery