Summary: Error 1813 message is one of the frequently observed errors in Microsoft SQL Server that may prevent you from opening the database. Read this blog to understand what causes the error and the methods using which you can fix the SQL database error 1813.
When trying to attach a SQL Server database, several users have reported about receiving SQL Server error 1813. The complete error message reads as:
Error Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Database_Name’. CREATE DATABASE is aborted.
What Causes Error SQL Database Error 1813?
SQL Server error 1813 occurs when a damaged or corrupt database logs are attached to the new server.
When your SQL Server’s log is in a damaged state, try rebuilding the log to make the database healthy and operational. For this, perform the manual steps in the same sequence given below:
Note: Rebuilding the log may result in data loss if the database did not shut down properly. If you don’t want to lose data, a better alternative is to use a SQL repair software that can help repair the database files while keeping the data intact.
Create anew database having the same name as the original database you need to recover in another location. For instance: ‘Database_Name.’ Further, re-create SQL data files (MDF and NDF) log files (LDF) with the same name as the previous data and log files.
Stop SQL Server from running.
Move the original MDF file to the new location by replacing the newly created MDF file.
Delete the LDF file of the newly created database in the SQL Server.
The following statement will return the current status of the database.
SELECT * FROM sysdatabases WHERE = Database_Name
The following statement will update one row of the database.
SET status = 32768
WHERE name = 'Database_Name'
Restart the SQL Server.
Note: This step is a must, or else the SQL Server will encounter an error
Execute the following ‘DBCC command’ in the query window of SQL Server Management Studio (SSMS). This will help create a new log file.
Note: The name of this newly created log file should be kept the same as the LDF file that was recently deleted from the new database server.
DBCC TRACEON (3604)
DBCC REBUILD_LOG(Database_Name, ‘C:\Database_Name_log.ldf ‘)
Here, DBCC REBUILD_LOG has two parameters. The first is ‘database name,’ and the second is ‘physical path of the log file.’ You need to confirm that the path is physical. This is because a logical name will return an error.
Reset the status of the database by using the command given below:
Turn/switch off the update to ‘system tables’ of the Master database by running the script given below:
sp_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
Set the status of the database to the previous status.
Note: If, while carrying out Steps 11, 12 & 13, there is an error while using the database, set the database to the single-user status.
The software main interface is displayed. Click the Browse or Search button to select the corrupt MDF file.
Note: If your database file is severely corrupt and the software is unable to detect the version of the db, you can select the database version manually from the following dialog box:
After selecting the file, click Repair to start the repair process.
You will see a preview of the recoverable objects, such as Tables, Views, Defaults, Data Types, Synonyms, etc. in a tree-like structure in the left pane of the preview window.
On the File menu, click Save to save the repaired MDF file with all its recovered objects. The Save Database dialog box appears.
6. Enter the required information and save the repaired database file.
Now try opening the repaired SQL database file from your SQL Server account.
Damaged (or corrupted) database log file (LDF) can cause SQL error 1813. You can fix the SQL database error 1813 by rebuilding the log file following the manual step-wise instructions discussed in this blog. However, the manual process can be lengthy and time-consuming. It may also result in data loss. But, you can quickly fix the error by repairing corrupted db files with the help of SQL repair software, such as Stellar Repair for MS SQL. It helps repairs the corrupt database files and recovers all its objects, including tables, keys, indexes, stored procedures, etc.