SQL Server database file Recovery Technology)

SQL Server database file Recovery Technology
Yzhshi (yzhshi@etang.com)

There are two ways to back up SQL Server databases: one is to back up database files using backup database, and the other is
Directly copy the MDF and log files of the database.
The following describes the backup and recovery of the latter. This document assumes that you are familiar with SQL Server Enterprise Manager
(SQL Server Enterprise Manager) and SQL Server quwey analyser (SQL Server Query analyzer)

1. Normal backup and recovery methods
Normally, we need to back up a database. First, we need to disconnect the database from the running data server, or stop the entire number.
Database server, and then copy the file.
Command for removing the database: sp_detach_db Database Name
Command for connecting to the 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 ′
This method can be used to correctly Restore database files of SQL sever7.0 and SQL Server 2000. The key point is that you must
Both MDF and LDF files are backed up. MDF files are database data files and LDF files are database log files.
Example:
Assume that the database is test, the data file is test_data.mdf, and the log file is test_log.ldf.
Next we will discuss how to back up and restore the database.
Detach a database: sp_detach_db 'test'
Connect to the 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. Only MDF file Restoration Technology
For various reasons, if we only backed up the MDF file at that time, it would be very troublesome to restore it.
If your MDF file is generated by the current database, you may be lucky to use sp_attach_db or
Sp_attach_single_file_db can restore the database, but a message similar to the following appears.

Device activation error. Physical file name 'C:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf'
It may be incorrect.
You have created a new log file named 'C:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf.

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

Server: Message 1813, level 16, status 2, Row 1
Failed to open the new database 'test '. Create database will be terminated.
Device activation error. The physical file name 'd:/test_log.ldf' may be incorrect.

What should we do? Don't worry. The following is an example of the restoration method.
A. We use the default method to create a database for recovery (such as test ). You can go to SQL Server Enterprise Manager
.
B. Stop the database server.
C. Delete the log file test_log.ldf of the database you just generated and overwrite the generated data with the MDF file of the database you want to restore.
Database Data File test_data.mdf.
D. Start the database server. The database test status is "Suspect ". At this time, you cannot perform any operations on this database.
E. Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, select database server.
Server, right-click and select "properties". On the "Server Settings" Page, select "allow direct modification to system directory. Yes.
Use the following statement.
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 this case, you can see in SQL Server Enterprise Manager that the database is in "Read-Only/suspicious/offline/emergency mode ".
See the table in the database, but only the system table
G. Execute the true recovery operation below to recreate the database log file.
DBCC rebuild_log ('test', 'c:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf ')
If the following prompt is displayed during execution:

Server: Message 5030, level 16, status 1, Row 1
The database cannot be locked for this operation.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

This indicates that other programs are using this database. If you used SQL Server Enterprise Manager in step f just now
Open the system table of the test database, and exit SQL Server Enterprise Manager.

The prompt for correct execution should be similar:

Warning: the log of the database 'test' has been rebuilt. Transaction consistency is lost. DBCC checkdb should be run to verify physical consistency
. The database must be reset and redundant log files may need to be deleted.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

In SQL Server Enterprise Manager, the database status is "for DBO only ". You can
The User table in the database is accessed.
H. Verify Database Consistency (omitted)
DBCC checkdb ('test ')

The general execution result is as follows:

Checkdb finds 0 allocation errors and 0 consistency errors (in the database 'test ).
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

I. Set the database to normal
Sp_dboption 'test', 'dbo use only', 'false'
If no error occurs, Congratulations. Now we can use the recovered database normally.
J. In the last step, we need to restore the "allow direct modification to system directory" set in Step E. Because the operating system table
It is a dangerous thing. Of course, we can recover the data in SQL Server Enterprise Manager or use
The following statement is completed:
Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go