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
Hi Pinal,
thank you very much for this solution !!! It was very useful for us … and perhaps it is possible for you to integrate the things which Jim Rickards responses!
Wish you success and all the best for 2010!
Bob
When I do step 10
I got this message :
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Please your advice.
Thanks
Hi,
To fix this issue login with admin privilege and execute below statements:
SP_CONFIGURE ‘ALLOW UPDATES’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
Regards,
Pinal Dave
I am logged in as user sa still it gives me error while updating sysdatabases as “Ad hoc updates on system tables are not allowed”.
Due to this i cannot set my database in emergency mode, my production database’s log file is corrupted and it is not allowing me to attach mdf file to create .ldf file.
I tried the process mentioned by you but updating system table is not allowed, i tried in sql server management studio express 2005 and also in sql server edition 2005 but same error is popped out.
Please can u help me to re-build ldf file since it is my production database and it needs to be sorted out fast.
Your response will be appreciated………..
Thanks in advance.
Hi,
This is simply great!!
Your solution helped us to recover from a serious matter.
Really thx a lot.
Keep up the good work pal.
Regards,
Dinesh Hettiarachchi.
Hi Pinal,
Followed steps above – However when I try and query data table I receive error below
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x0000005ec80000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\Deal.MDF’.
Connection Broken
If I try and open tables through Enterprise Manager Receive message
Error 601 Could not continue scan with NOLOCK due to data movement
Would you have any ideas ?
Thanks
Hi, I have the same problem…did you get any answeer for that? if so, please share it…thanks in advance.
Best Regards!
Jesus
Dear Pinal Dave,
I’m sorry, please help me.
When I do step ‘—-Following statement will update only one row in database’
I found message “Msg 259, Level 16, State 1, Line 3
Ad hoc updates to system catalogs are not allowed.”
I don’t know what ‘s happen?
Best regards,
Surachai S
You are wonderful gift from god. My database back, for those who have errors this solutions is applicable in SQL Server 2000
Oh, yes, I am another DBA helped by this fix!
By the way, it’s now 10:50PM at the office. LOL!
thanks u are the man. save my day
Thanks, you saved my life
Hi,
I am logged in as user sa still it gives me error while updating sysdatabases as “Ad hoc updates on system tables are not allowed”.
Due to this i cannot set my database in emergency mode, my production database’s log file is corrupted and it is not allowing me to attach mdf file to create .ldf file.
I tried the process mentioned by you but updating system table is not allowed, i tried in sql server management studio express 2005 and also in sql server management studio 2005 but same error is popped out. I also tried by creating a user with all privileges but still i didn’t work
Please can u help me to re-build ldf file since it is my production database and it needs to be sorted out fast.
Your response will be appreciated………..
Thanks in advance.
I m using sql_2008
When I run this stmt.
DBCC TRACEON (3604)
DBCC REBUILD_LOG(Campus,’D:\Campus_log.ldf’)
I got error
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2526, Level 16, State 3, Line 2
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
Do you have any solution???
Thank in advance
We are SQL server 2000 with SP4 patch. On one of my database whenever we run a select query we get following error
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x0000005ec80000 in file ‘D:\Program Files\Microsoft SQL Server\MSSQL\Data\MSSQL\Data\MediaBin Repository_Data.MDF’.
Connection Broken
We had tried checkdb with repair allo data loss but did not help. I don’t have good backup also.
Please suggest
Thank you
We are using SQL server 2000 with SP4 patch. On one of my database whenever we run a select query we get following error
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x0000005ec80000 in file ‘D:\Program Files\Microsoft SQL Server\MSSQL\Data\MSSQL\Data\MediaBin Repository_Data.MDF’.
Connection Broken
We had tried checkdb with repair allow data loss but did not help. I don’t have good backup also.
Please suggest
Thank you
We were getting the following error:
Could not open FCB for invalid file ID 0 in database ‘Local1′.
Turns out that after running:
SELECT *
FROM sysdatabases
WHERE name = ‘yourdatabasename’
the database (MDF) file we thought was mounted was not, so we stopped the SQL services and renamed the file to match the one in the record produced by the query directly above this paragraph.
Also, we executed step number 10 before 9 because of a permission issue.
Thank you. Om Shanti Om.
ref:wavedrop
thanks so much, you did a great job. Four years since you post this, it ‘s still useful.
Hi dave,
I could solve just by going through the first three steps.
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
Hi Pinal,
Simply brilliant, worked like a charm!!!!
Many thanks for this post!!!
Regards,
Joshua
Thank you very much !
You saved my life.
Thank you so much for this fix it tip, my client could not work for over a week because of the database crash, six months of data loss was at at stake.