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
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!
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
a solution that works….just follow the steps
very straight-forward and complete.
Time saver…Thanks a mil
THANK YOU!!! Worked like a charm… thanks
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
Thank you very much !
You saved my life.
thanks milions time !
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
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.
Funcionó !!! maestro !!!!
Gracias totales.
Desde Córdoba Argentina.
Thanks for the article! Helped me to resolve issue restoring databsae from Veritas backup.
superve solution…….
worked perfectly…….
thanks a lot
it solved my Problem
Thanks a lot…
The best by the test
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….
Thank you very very very very very very very very very very … much !
خداوند جزای خیرت دهاد.
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.
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.
Howard,
Your solution worked perfectly for me on SQL Server 2008!
Many Thanks,
Khalid
Thanks Howard;
that’s really helped me, i was going crazy if the database has been crashed
Thanks again
ibrahim
hermano muchas gracias acabas de salvarme!!!! gracias
Your method works!
Thank you very much!
did anyone else have the problem and a solution for the
‘Ad hoc updates to system catalogs are not allowed.’
regards raimund
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
You saved my day! One year of work saved cuz a disk crash…
Thanks ….
Really Interesting….