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.
- How to Recover a Suspect Database?
- Understanding SQL Server Transaction Log Architecture
- How to check Last Restore Time of a database?
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.
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018