I. Error
Sometimes, when you restart the Database Service, you will find that some databases are being recovered, under suspicion, or suspicious. At this time, the DBA will be very nervous, the following are some methods that have been proved in practice.
After a restart of the Database Service, the database shows that it is being recovered. After a long time, it is still in this status and the offline time cannot be too long. So I think of a way to stop the database service, copy the data file mdf and ldf and delete the ldf file. Based on previous experience, it seems that you can use mdf to restore the database without ldf. A database with the same name is created, the database service is stopped, the mdf file is overwritten, and the database service is started again. This is still in a suspicious state.
In this example, mdf cannot be used to attach a database and an error is reported.
2. Solution steps
Method 1: Use a script to restore the database.
Copy codeThe Code is as follows:
-- DataBaseName indicates the data name to be repaired.
USE MASTER
GO
SP_CONFIGURE 'Allow updates', 1 RECONFIGURE WITH OVERRIDE
GO
Alter database [DataBaseName] SET EMERGENCY
GO
Sp_dboption 'databasename', 'single user', 'true'
GO
Dbcc checkdb ('databasename', 'repair _ ALLOW_DATA_LOSS ')
GO
Alter database [DataBaseName] SET ONLINE
GO
Sp_configure 'Allow updates', 0 reconfigure with override
GO
Sp_dboption 'databasename', 'single user', 'false'
GO
SQL explanation:
1) Use the specified value to force reconfiguration: (1. 0 indicates true or false)
Sp_configure 'Allow updates', 1 reconfigure with override
2) set to emergency:
Alter database DataBaseName set emergency
3) set to single-user mode:
Alter database [DataBaseName] set single_user
Or: Sp_dboption 'databasename', 'single user', 'true'
4) fixed the error:
Dbcc checkdb ('databasename', 'repair _ ALLOW_DATA_LOSS ')
5) set to online or online:
Alter database [DataBaseName] SET ONLINE
Method 2: This method has not been tried yet. You can try it.
Copy codeThe Code is as follows:
Create database DataBaseName
ON (FILENAME = 'd: \ DataBase \ Name. mdf ')
FOR ATTACH_REBUILD_LOG;
GO