Tips to fix SQL database error 1813

Read time 4 minutes

Last month, we got several user queries related to the SQL database error 1813 which appears while users try to attach their databases. The error message was somewhat like this –

SQL SERVER – Fix : Error Msg 1813, Level 16, State 2, Line 1
Could not open new database 'databasename'. CREATE DATABASE is aborted.

After studying the queries, we are coming up with some tips to fix SQL database error 1813. We would like to share them with you through this blog.

The error occurs due to the corruption of log database files (LDF) of the SQL Server. We suggest a manual method to rebuild this log database file in emergency mode.

Let us know the whole process in detail.

Manually fixing SQL database error 1813

You can try to fix this SQL database error 1813 manually by rebuilding the database using the SQL Server Management Studio. Let us know all the necessary steps required to perform this fix.

1. Create a new database with the help of Microsoft SQL Server Management Studio. Save the database files (LDF and MDF) as the same name of the original database (affected with the error).
2. Next, stop all the running instances of the SQL Server.
3. Transfer the original database (MDF) file to the newly created file folder location and replace this original MDF file with the newly created MDF file only. Also, delete the newly created LDF file from its folder.
4. Now, start the SQL Server as an administrator. You would see that the database is in the SUSPECT mode.
5. Run this script in the Management Studio to validate that master database system tables allow upgrading values.
USE MASTER
GO
sp_CONFIGURE ‘allow updates’, 1
RECONFIGURE WITH OVERRIDE
GO
6. After confirming, run this script to convert SUSPECT mode of current database to EMERGENCY mode.
SELECT *
FROM sysdatabases
WHERE name = ‘databasename‘
—-The statement will update only one row in database
BEGIN
UPDATE sysdatabases
SET status = <value>
WHERE name = ‘databasename‘
COMMIT TRAN
7. Now, close and restart the SQL Server.
8. Start the SQL Server Management Studio and rebuild the log file with the same name as the just deleted log file (from the newly created database).
DBCC TRACEON (3604)
DBCC REBUILD_LOG(databasename,’C:\databasename_log.ldf’)
GO
9. You need to reset the database status now with this script.
sp_RESETSTATUS databasename
GO
10. Switch off the system tables value upgrade (performed in Step 6). Run:
USE MASTER
GO
sp_CONFIGURE ‘allow updates’,0
RECONFIGURE WITH OVERRIDE
GO
11. Set the database to the previous state

Given below will update only single row in the database

BEGIN
UPDATE sysdatabases
SET status = (value retrieved IN first query OF STEP 7)
WHERE name = ‘databasename‘
COMMIT TRAN
GO’
12. If you still find error while performing the above steps, try changing database to single user mode.
sp_DBOPTION ‘databasename’, ‘single user’,’true’

Or come out of this mode using this command –

sp_DBOPTION ‘databasename’, ‘single user’,’false’

Running all the above steps accurately can fix SQL database error 1813; but it is not always the case. Due to the length and complexity of the above manual approach, there are high chances of errors while performing it even if you have good technical knowledge. And another thing is that the corruption can be so severe that it cannot be resolved with this method.

What should be the next step?

If you want to recover every bit of your SQL data without performing lengthy operations, then try a professional solution. In fact, we have it for you – Kernel for SQL Database Recovery. The SQL database recovery software independently works to repair corrupt SQL database files (MDF and LDF) to its original form fixing all the SQL Server errors. The tool makes the inaccessible objects accessible to users and saves to either Live SQL Server or in batch file. They can also choose to save to the desired destination after previewing it. Thus, this tool helps to open and view MDF files without SQL Server. All these features can be tested via the free trial version of the software. Later on, you can purchase the tool after seeing its working.

Conclusion

The blog helps users to resolve one of the common SQL errors – SQL database error 1813 — by rebuilding the database using the Management Studio. It also mentions a powerful third-party tool to recover the SQL database securely.