Plan A - Create the database with ATTACH_REBUILD_LOG option:
As it seems the option is available in SQL Server 2017, so I attempted to create the database via the following script:
CREATE DATABASEON (FILENAME='I:\Data\ .mdf' ) FOR ATTACH_REBUILD_LOG
And as expected I run into the first error:
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "I:\Data\
Msg 1802, Level 16, State 7, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
It looked like a permissions problem, though I wasn’t entirely sure which account is causing the problem. In the past I had problems with the Administrator account, so it was the first thing to try. Once I removed the permissions for Administrator account to the folder containing the database and gave it full control permissions again, I tried to create the database anew using the above script, running into the next error:
File activation failure. The physical file name "D:\Logs\
Msg 1813, Level 16, State 2, Line 1 Could not open new database '
This approach seemed to lead nowhere, so it was time for Plan B.
Plan B - Recover the database into an empty database with the same name:
Step 1: Create a new database with the same name, stop the SQL Server, then copy the old file over the new file, and delete the new log file manually. Then restarted the server. After the restart the database will appear in Management Studio with the SUSPECT state.
Step 2: Set the database in EMERGENCY mode:
ALTER DATABASESET EMERGENCY, SINGLE_USER
Step 3: Rebuild the log file:
ALTER DATABASE <database_name>
REBUILD LOG ON (Name=’_Log ',
FileName='D:\Logs\.ldf ')
The rebuild worked without problems.
Step 4: Set the database in MULTI_USER mode:
ALTER DATABASESET MULTI_USER
Step 5: Perform a consistency check:
DBCC CHECKDB () WITH ALL_ERRORMSGS, NO_INFOMSG
After 15 minutes of work the database was back online.
Warnings:
Always attempt to recover the data for production databases from the backup files! Use the above steps only if there is no other alternative!
The consistency check might return errors. In this case one might need to run CHECKDB with REPAIR_ALLOW_DATA_LOSS several times [2], until the database was repaired.
After recovery there can be problems with the user access. It might be needed to delete the users from the recovered database and reassign their permissions!
Resources:
[1] In Recovery (2008) Creating, detaching, re-attaching, and fixing a SUSPECT database, by Paul S Randal [Online] Available from: https://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
[2] In Recovery (2009) Misconceptions around database repair, by Paul S Randal [Online] Available from: https://www.sqlskills.com/blogs/paul/misconceptions-around-database-repair/
[3] Microsoft Blogs (2013) Recovering from Log File Corruption, by Glen Small [Online] Available from: https://blogs.msdn.microsoft.com/glsmall/2013/11/14/recovering-from-log-file-corruption/
No comments:
Post a Comment