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

The error 1813 comes when corrupt transaction log are attempted to attach to new server. Error details are given below:

Msg 1813, Level 16, State 2, Line 1,
Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

If you get this issue you can follow this tip to fix your issue. Below are the detailed steps.

Related Articles:

Fix/Solution/Workaround:

Follow all steps without missing any to fix this issue.

1. Move original database mdf and ndf files to another directory (Data_old)
2. Create a database with the same name and same database file names in same 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 newly created mdf and ndf files with the original ones that you copied to Data_Old folder. Don’t cut and paste. Always use copy-paste.
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’; --Replace DBName with your database name.
-- Put the database in emergency mode
EXEC(‘ALTER DATABASE [' + @DB + '] SET EMERGENCY’);
-- Set single user mode
exec sp_dboption @DB, ‘single user’, ‘TRUE’;
or
-- Repair database
DBCC checkdb (@DB, repair_allow_data_loss);
-- Turn off single user mode
exec sp_dboption @DB, ‘single user’, ‘FALSE’;

If you are not able to connect after single user then run below command in one go to fix this issue. Make sure to change your database name in below Alter statement.

Alter database dbname SET EMERGENCY
go
Alter database dbname set single_user with rollback immediate
go
DBCC checkdb ('dbname', repair_allow_data_loss);
go
Alter database dbname set multi_user

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.

If you like this tip, you can follow our facebook page and Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

2 Responses

  1. Alina Endevour says:

    Great Post Manvendra!!!!

Leave a Reply

Your email address will not be published. Required fields are marked *