SQL SERVER – Fix : Error Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

« SQL SERVER – Fix : Error Msg 4214 – Error Msg 3013 – BACKUP LOG cannot be performed because there is no current database backup

SQLAuthority News – FQL – Facebook Query Language »

SQL SERVER – Fix : Error Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

July 21, 2007 by pinaldave

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 (http://blog.SQLAuthority.com), BOL (many topics)

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Documentation, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 61 Comments

61 Responses

on July 25, 2007 at 11:04 pm | Reply Chris Woods

You REALLY saved my butt with this fix! A friend of mine comes onto your sight for tips, fixes and the like and told me I should see if you had a fix for this. Sure enough, you did! You have helped us out tremendously! Thanks again! Keep up the great work!
on July 25, 2007 at 11:07 pm | Reply pinaldave

Chris,

Thank for comment. Yes, this was one of the most interesting problem I faced as well.

Glad you figured out the problem.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on July 31, 2007 at 6:01 pm | Reply Michael Wang

Hi Pinal,

Thanks for the script, you are a life saver :D

Just found couple of typo FYI

1. It should be RECONFIGURE not RECONFIGIRE
2 REBUILDLOG is REBUILD_LOG

But gain, Thanks a million!

Mick
on August 8, 2007 at 9:19 am | Reply Chris Chin-See

a solution that works….just follow the steps
very straight-forward and complete.

Time saver…Thanks a mil
on August 10, 2007 at 12:29 pm | Reply Brian

THANK YOU!!! Worked like a charm… thanks
on August 12, 2007 at 11:02 pm | Reply Jaideep

Hi Pinal,

While trying to attach Production (SQL2K) 20 datas and 2 logs to our Sandbox (SQL2K5) using the following script, I received an error… Can you please give me some suggestion on what might be wrong to my script?

USE [master]
GO
CREATE DATABASE [SBX] ON
( FILENAME = N’F:\DATA\SBXDATA1\SBXDATA1.mdf’ ),
( FILENAME = N’G:\LOG\SBXLOG1\SBXLOG1.ldf’ ),
( FILENAME = N’F:\DATA\SBXDATA10\SBXDATA10.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA11\SBXDATA11.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA12\SBXDATA12.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA13\SBXDATA13.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA14\SBXDATA14.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA15\SBXDATA15.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA16\SBXDATA16.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA17\SBXDATA17.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA18\SBXDATA18.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA19\SBXDATA19.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA2\SBXDATA2.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA20\SBXDATA20.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA3\SBXDATA3.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA4\SBXDATA4.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA5\SBXDATA5.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA6\SBXDATA6.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA7\SBXDATA7.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA8\SBXDATA8.ndf’ ),
( FILENAME = N’F:\DATA\SBXDATA9\SBXDATA9.ndf’ ),
( FILENAME = N’G:\LOG\SBXLOG2\SBXLOG2.ldf’ )
FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N’SBX’ and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [SBX].dbo.sp_changedbowner @loginame=N’ENTPRO\conjai’, @map=false
GO

Error message:

Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘SBX’. CREATE DATABASE is aborted.
Msg 3456, Level 21, State 1, Line 1
Could not redo log record (73574:26706:79), for transaction ID (0:766408492), on page (1:7487504), database ‘SBX’ (database ID 5). Page: LSN = (73573:137222:224), type = 1. Log: OpCode = 6, context 2, PrevPageLSN: (73574:23272:17). Restore from a backup of the database, or repair the database.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database ‘SBX’, an error occurred at log record ID (73574:26706:79). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

Thanks! I appreciate your help.

-Jaideep
on August 18, 2007 at 9:41 am | Reply Cuong

Thank you very much !
You saved my life.
thanks milions time !
on September 14, 2007 at 9:11 am | Reply Jorge

This does not work in SQL Server 2005. I tried to update the sysdatabases table and it gave me error:

‘Ad hoc updates to system catalogs are not allowed.’

I’m wondering if there is another alternative to recover an .MDF file without the log.

-jorge
on September 24, 2007 at 7:31 pm | Reply Jimmy Phat

I try to update sysdatabases. I get an error stating “Update sysdatabase fails because it can not update derived or constant field.” Why do I get this message? Appreciate for your help.
on September 25, 2007 at 1:33 pm | Reply Juanchirola

Funcionó !!! maestro !!!!
Gracias totales.
Desde Córdoba Argentina.
on November 3, 2007 at 7:29 pm | Reply Akim Zubarchuk

Thanks for the article! Helped me to resolve issue restoring databsae from Veritas backup.
on November 7, 2007 at 2:34 am | Reply uma Shankar

superve solution…….
worked perfectly…….

thanks a lot
it solved my Problem
on November 10, 2007 at 1:34 am | Reply Amiguito

Thanks a lot…
The best by the test
on November 15, 2007 at 12:45 pm | Reply Mick

Hi

I understand the use of sp_resetstatus but as silly as it sounds I cannot locate ‘sp_resetstatus’ on the server, if I try from a command line, not found etc. Is there a download for this tool? (I don’t know SQL much at all) I have searched all over….
on December 3, 2007 at 7:21 pm | Reply hoseini

Thank you very very very very very very very very very very … much !

خداوند جزای خیرت دهاد.
on February 22, 2008 at 5:08 am | Reply Jim Rickards

Hi Pinal,

Great work helped heaps with one of our clients.

Not sure if due to different flavour of SQL but found a few glitches.

Step 5 & 10 needed BEGIN TRAN not just BEGIN
Step 10 needed to be run prior to step 9 or could not update

I was folowing your steps religiosly and got to step ten and realised I did not know value from step 5 (OOPS!). Fortunately had old copy of DB as different name.

Many thanks.
on April 1, 2008 at 7:04 pm | Reply Howard

I ran into this problem last night and was able to restore my databases on a SQL Server 2005 install by following these steps:

1. Move mdf and ndf files to another directory (Data_old)
2. Create a database with the same name and same file names and locations as the original databases. (this only applies to the mdf and ndf files the log file can go anywhere)
3. Stop the SQL Server service.
4. Overwrite new mdf and ndf files with the original ones.
5. Start SQL Server.
6. Run this script (Set the @DB variable to the name of your database before running):

Declare @DB sysname;
set @DB = ‘DBName’;
– Put the database in emergency mode
EXEC(‘ALTER DATABASE [' + @DB + '] SET EMERGENCY’);
– Set single user mode
exec sp_dboption @DB, ‘single user’, ‘TRUE’;
– Repair database
DBCC checkdb (@DB, repair_allow_data_loss);
– Turn off single user mode
exec sp_dboption @DB, ‘single user’, ‘FALSE’;

I got an error stating that the log file did not match the data file. You can ignore this as we are rebuilding the log file.
- on November 30, 2011 at 11:51 am | Reply Khalid
  
  Howard,
  Your solution worked perfectly for me on SQL Server 2008!
  Many Thanks,
  Khalid
on April 29, 2008 at 9:41 pm | Reply raimund

did anyone else have the problem and a solution for the
‘Ad hoc updates to system catalogs are not allowed.’

regards raimund
on April 30, 2008 at 2:28 am | Reply Mary

Hi Pinal,

I tried this solution and got the db backup, but for some reason, the ldf couldn’t be found under ‘Properties>Files’. When I tried to do a shrink log file, it displayed the mdf instead of the ldf??!! The funny thing is, at first I was able to see the tables, but after 5 minutes, I was not able to access the db anymore! I got the following error:

“Error 945: Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details”

Hence I lost my db. :(

Any insight or suggestions on what happened would be much appreciated. :)

Thanks,
Mary
on May 22, 2008 at 6:58 pm | Reply swede_dude

You saved my day! One year of work saved cuz a disk crash…
on June 16, 2008 at 1:34 pm | Reply praveen

Thanks ….

Really Interesting….
on July 11, 2008 at 3:00 pm | Reply zlatan24

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.
on August 26, 2008 at 9:12 pm | Reply notquiteanewbie

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.
on September 1, 2008 at 11:34 am | Reply chris

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.
on September 25, 2008 at 1:07 pm | Reply Luca

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!
on September 30, 2008 at 8:37 pm | Reply Patrick

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
on November 8, 2008 at 3:20 am | Reply Todd

Hi,

Thanks for the post. It worked great.
on December 12, 2008 at 3:29 am | Reply john

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?
on December 26, 2008 at 11:46 am | Reply rodoss

5. change below code and work perfectly! Thank you!

BEGIN

to

BEGIN TRAN
on January 13, 2009 at 9:13 am | Reply Arthas

Many thanks for your great tutorial :) !
on February 20, 2009 at 3:57 am | Reply Tommy

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?
on February 24, 2009 at 8:47 am | Reply JR

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.
on February 27, 2009 at 4:35 pm | Reply husain

Thank you very much. This exactly a perfect solution. You safe me today.

Regards
ucin
on March 21, 2009 at 12:00 pm | Reply Babu

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………
on March 21, 2009 at 12:01 pm | Reply Babu

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………
on May 20, 2009 at 8:37 pm | Reply SANTHOSH

thanks for this guidance , its really superb and very easily one.
thank u very much
on June 8, 2009 at 12:40 pm | Reply Christine

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
on June 30, 2009 at 2:15 pm | Reply Isuru

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 -
on August 5, 2009 at 12:36 pm | Reply Bruwer

Hi Pinal

Thank you very much indeed for writing such a clear an concise article about a very technical matter. Well done :)

Thank you
on August 7, 2009 at 7:15 am | Reply Mark Plumpton

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
on August 23, 2009 at 10:43 pm | Reply subin

Hi Pinal Sir,

Thank you very much for this article.Thanks a lot…
The best by the test
on December 28, 2009 at 10:06 pm | Reply Bob

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
on March 25, 2010 at 8:30 am | Reply exzant

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
on March 25, 2010 at 8:49 pm | Reply Pinal Dave

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
- on November 17, 2010 at 6:21 pm | Reply saurabh
  
  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.
on May 18, 2010 at 8:33 pm | Reply Dinesh

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.
on June 1, 2010 at 6:19 pm | Reply Majella

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
- on June 28, 2010 at 9:52 pm | Reply Jesus
  
  Hi, I have the same problem…did you get any answeer for that? if so, please share it…thanks in advance.
  
  Best Regards!
  Jesus
on June 16, 2010 at 8:41 pm | Reply Surachai

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
on June 21, 2010 at 4:49 pm | Reply Fox

You are wonderful gift from god. My database back, for those who have errors this solutions is applicable in SQL Server 2000
on June 22, 2010 at 8:15 pm | Reply Warren Cruz

Oh, yes, I am another DBA helped by this fix!

By the way, it’s now 10:50PM at the office. LOL!
on July 3, 2010 at 9:25 pm | Reply Funashi Mwamba

thanks u are the man. save my day
on August 24, 2010 at 11:50 am | Reply Eduardo Nishi

Thanks, you saved my life
on November 18, 2010 at 4:31 pm | Reply saurabh

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.
on January 11, 2011 at 12:40 pm | Reply Kiran

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
on February 26, 2011 at 3:45 am | Reply desai C S

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
on February 26, 2011 at 3:45 am | Reply desai C S

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
on April 16, 2011 at 3:36 am | Reply Joe C

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
on July 5, 2011 at 2:32 pm | Reply lehaandi

thanks so much, you did a great job. Four years since you post this, it ‘s still useful.
on July 19, 2011 at 10:04 am | Reply Shobin Mathew

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