Fix : Error Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.
This errors happens when corrupt database log are attempted to attach to new server. Solution of this error is little long and it involves restart of the server. I recommend following all the steps below in order without skipping any of them.
Fix/Solution/Workaround:
SQL Server logs are corrupted and they need to be rebuilt to make the database operational.
Follow all the steps in order. Replace the yourdatabasename name with real name of your database.
1. Create a new database with same name which you are trying to recover or restore. (In our error message it is yourdatabasename). Make sure the name of the MDF file (primary data file) and LDF files (Log files) same as previous database data and log file.
2. Stop SQL Server. Move original MDF file from older server (or location) to new server (or location) by replacing just created MDF file. Delete the LDF file of new server just created.
3. Start SQL Server. Database will be marked as suspect, which is expected.
4. Make sure system tables of Master database allows to update the values.
USE MASTER
GO
sp_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
5. Change database mode to emergency mode.
–Following statement will return the current status of the database
SELECT *
FROM sysdatabases
WHERE name = 'yourdatabasename'
—-Following statement will update only one row in database
BEGIN
UPDATE sysdatabases
SET status = 32768
WHERE name = 'yourdatabasename'
COMMIT TRAN
6. Restart SQL Server (This is must, if it is not done SQL Server will through an error)
7. Execute this DBCC command in query window of Management Studio, this will create new log file. Keep the name of this file same as LDF file just deleted from new server :
DBCC TRACEON (3604)
DBCC REBUILD_LOG(yourdatabasename,'c:\yourdatabasename_log.ldf')
GO
DBCC accepts two parameters : first parameter is database name and second parameter is physical path of the log file. Make sure the path is physical, if you put logical file name it will return an error.
8. Reset the database status using following command.
sp_RESETSTATUS yourdatabasename
GO
9. Turn off the update to system tables of Master database running following script.
USE MASTER
GO
sp_CONFIGURE 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO
This should be resolve the problem mentioned above. I always check consistence of the database as well as I reset the status of the database to original status.
10. Reset the database status to previous status
–Following statement will update only one row in database
BEGIN
UPDATE sysdatabases
SET status = (value retrieved IN first query OF STEP 5)
WHERE name = 'yourdatabasename‘
COMMIT TRAN
GO'
Note : If during steps 8, 9 , 10 if there is error if database is in use.
Set the database to status single user.
sp_DBOPTION 'yourdatabasename', 'single user','true'
Once the steps 8,9,10 are completed if database is not already in multi user mode run this script.
sp_DBOPTION 'yourdatabasename', 'single user','false'
If there is any issue while doing above process to fix the error let me know. Make sure that you have done all the steps in order and restarted SQL Server where it is mentioned.
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL (many topics)
88 Comments. Leave new
There is fine tool-sql mdf recovery,repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension), can process huge databases, their file size can reach 16 TB, supports data extraction via the local area network, you can save recovered data as SQL scripts, it is also possible to split data into files of any size, can save extracted information directly to the SQL server. It allow to recovery SQL Server faster.
I had this same error. Turns out the mdf file was READ ONLY. After unchecking READ ONLY, I was able to attach my database on SQL 2005 SP2.
Hi, I experience the same problem as described and I able to recover the database using the step recommended by Howard above. The database is working for a while.
However, when i try to connect the database from my program, i encountered the following error:
Msg 211, Level 23, State 51, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Trying to run the DBCC CHECKCATALOG return no error.
Any idea?
Thx.
Hi,
THANKS! You saved my life!!!!
Just one thing:
Instead of point 10, I run
sp_DBOPTION ‘yourdatabasename’, ‘dbo use’, ‘false’
and
sp_DBOPTION ‘yourdatabasename’, ‘single user’, ‘false’
Ciao!
Hi,
Thank you very much for this procedure – I have successfully implemented on a damaged DB.
Just for the records, this procedure also works on MSSQL 2000.
Thanks
Patrick
Hi,
Thanks for the post. It worked great.
I got this error when attaching a db that has multiple log files, can I do the same process? Should I add the additional log file when I create the new db?
5. change below code and work perfectly! Thank you!
BEGIN
to
BEGIN TRAN
Many thanks for your great tutorial :) !
At:
DBCC TRACEON (3604)
DBCC REBUILD_LOG(yourdatabasename,‘c:yourdatabasename_log.ldf’)
GO
I get an error:
The database could not be exclusively locked to perform the operation.
I’ve tried forcing the db into Single User mode and still nothing. The log does not get created.
Any ideas?
THANK YOU!!!!!!!!!!!!! I LOVE YOU MAN!!!!!!!!!!!!! You saved my life. You are AWESOME AND DESERVE A GOLD STAR!!! If I knew your address I would send you cookies and wine and some other goodies.
Thank you very much. This exactly a perfect solution. You safe me today.
Regards
ucin
I have problem with MSSQL, i can’t attaching the DB… the error massege
Error 1813: Could not open new database ‘name of database’. CREATEDATABASE is aborted. Device activiation error. The physical file name ‘c:\program files\Microsoft SQL Server\MSSQL\data\name of database_log.LDF’ may be incorrect.
please hel[ me………
I have problem with MSSQL, i can’t attaching the DB… the error massege
Error 1813: Could not open new database ‘name of database’. CREATEDATABASE is aborted. Device activiation error. The physical file name ‘c:\program files\Microsoft SQL Server\MSSQL\data\name of database_log.LDF’ may be incorrect.
please help me………
thanks for this guidance , its really superb and very easily one.
thank u very much
hi,
I encounter the following error when execute the steps at
DBCC REBUILD_LOG(‘Local1′,’c:\Local1_Log.ldf’).
Error msg:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 0 in database ‘Local1’.
Connection Broken
I could not proceed, do you have any suggestion?
Thx a lot
Dear Pinal,
Thank you very much for this article. I could save a lot of job details for a whole week in a technical company, who has not backed up their database for long time.
This worked me fine.
I appreciate you for writing these kind of stuff.
– Thanks –
Hi Pinal
Thank you very much indeed for writing such a clear an concise article about a very technical matter. Well done :)
Thank you
The solution posted by Howard (1-apr-2008) worked for me on SQL 2008.
I had also got the ‘Ad hoc updates to system catalogs are not allowed’ message with Pinal’s script
Hi Pinal Sir,
Thank you very much for this article.Thanks a lot…
The best by the test