SQL Server disaster recovery
1. First confirm that the .mdf and .ldf files have been backed up.
2. Create a newly created database in SQL Server and stop SQL Server service.
3. Cover the new database corresponding to the new database with the original .mdf and .ldf file.
4. Restart SQL Server service, which should see this database in a suspect state.
5. Perform the following command in the SQL query analyzer to allow the system table:
Use master
Go
sp_configure 'allow updates', 1
Reconfigure with override
Go
6. Place this database as an emergency mode:
Update sysdatabases set status = 32768 where name = 'db_name'
Go
7. Use the dbcc checkdb command to check the error in the database:
DBCC Checkdb ('DB_NAME')
Go
8. If the dbcc checkdb command fails, go to step 10, otherwise set the database as a single user mode, and try to fix it:
Sp_dboption 'DB_NAME', 'Single User', 'True'
DBCC CHECKDB ('db_name', repair_allow_data_loss)
Go
If the database is not in a single user mode status while executing the DBCC CHECKDB ('DB_NAME', the REPAIR_ALLOW_DATA_NAME ', ", then restart the SQL Server service and continue attempt.
9. If DBCC Checkdb ('db_name', the repair_allow_data_loss) command fails, go to step 10, otherwise, if the error in the database is successfully fixed:
Re-execute the DBCC CHECKDB ('db_name') command, confirm that there is no error in the database.
Clear the status of the database: sp_resetstatus 'db_name'
Clear the single user mode status of the database: sp_dboption 'DB_NAME', 'Single User', 'False'
Restart the SQL Server service, if everything is normal, the database has successfully recovered.
10. If the above steps do not solve the problem, please refer to the document in the attachment attempt to restore the data in the database by rebuilding the transaction log.
If you only have MDF files, the problem is more complicated, we need to directly rebuild the transaction log:
1. Create a newly created database in SQL Server, and then stop SQL Server service.
2. Cover the new database corresponding to the new database with the original LDF file, delete its log file (.LDF).
3. Start the SQL Server service and set the database to emergency mode (ibid: Step 5 and Step 6).
4. Stop and restart SQL Server service.
5. Execute the following command to rebuild the database log file: (below is an example, you want to use your actual database name)
DBCC REBUILD_LOG ('CAS_DB', 'D: /CAS_DB/CAS_DB_LOG.LDF')
6. Reset the database as a single user mode. (http://support.microsoft.com/?id=264154)
7. Try again to check and fix errors in the database using DBCC CheckTable or DBCC checkdb commands.
