If you are encountering the error message “Error Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted” in SQL Server, don’t worry, there is a solution. This error occurs when corrupt database logs are attempted to be attached to a new server.
Follow the steps below to fix this error:
- Create a new database with the same name as the one you are trying to recover or restore. Make sure the name of the MDF file (primary data file) and LDF files (log files) are the same as the previous database.
- Stop SQL Server and move the original MDF file from the older server or location to the new server or location, replacing the newly created MDF file. Delete the LDF file of the new server that was just created.
- Start SQL Server. The database will be marked as suspect, which is expected.
- Make sure the system tables of the Master database allow updates by executing the following command:
sp_CONFIGURE 'allow updates', 1 RECONFIGURE WITH OVERRIDE - Change the database mode to emergency mode by executing the following command:
UPDATE sysdatabases SET status = 32768 WHERE name = 'yourdatabasename' - Restart SQL Server. This step is crucial, as failure to restart SQL Server will result in an error.
- Execute the following DBCC command in the query window of Management Studio to create a new log file:
DBCC TRACEON (3604) DBCC REBUILD_LOG (yourdatabasename, 'c:\yourdatabasename_log.ldf') - Reset the database status using the following command:
sp_RESETSTATUS yourdatabasename - Turn off the update to system tables of the Master database by executing the following command:
sp_CONFIGURE 'allow updates', 0 RECONFIGURE WITH OVERRIDE - Reset the database status to its previous status by executing the following command:
UPDATE sysdatabases SET status = (value retrieved in the first query of step 5) WHERE name = 'yourdatabasename'
Note: If you encounter an error during steps 8, 9, or 10 because the database is in use, set the database to single user mode by executing the command: sp_DBOPTION 'yourdatabasename', 'single user', 'true'. Once steps 8, 9, and 10 are completed, if the database is not already in multi-user mode, run the script: sp_DBOPTION 'yourdatabasename', 'single user', 'false'.
If you encounter any issues while following the above process to fix the error, please let me know. Make sure to follow all the steps in order and restart SQL Server as mentioned.
By following these steps, you should be able to resolve the “Error Msg 1813” and make your database operational again.
For more information on SQL Server error messages and troubleshooting, you can refer to the SQL Server Books Online (BOL) documentation.