First, the error situation
Sometimes when you restart the database service, you will find that some databases are recovering, doubtful, suspicious, and the DBA will be very nervous, and here are some proven methods in practice.
Once the database service is restarted, the database display is recovering, after a long time or this state, offline not too long, so think of a way to stop the database service, the data file MDF and LDF copy out, delete the LDF file, according to previous experience, It seems that you can use MDF to recover a database without LDF. Creating a database with the same name, stopping the database service, overwriting the MDF file, and then starting the database service is still in a suspicious state.
The use of MDF to attach the database is not attached, has been the error.
second, the resolution steps
Method One: Use scripts for database recovery.
Copy Code code as follows:
--databasename for the repaired data name
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 Tutorial:
1 force reconfiguration using the specified value: (1, 0 is 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 Fix the found error:
DBCC CHECKDB (' DataBaseName ', ' Repair_allow_data_loss ')
5) set to online, online:
ALTER DATABASE [DataBaseName] SET ONLINE
method Two: This method has not been tried, we can try.
Copy Code code as follows:
CREATE DATABASE DataBaseName
On (FILENAME = ' D:\DataBase\Name.mdf ')
For Attach_rebuild_log;
Go
