SQL Server Error 1813: Resolvable Method

In the premises, where MS SQL is used, error based situations do occur. Worth appreciable servers like SQL server can also pose indifferent scenarios in front of users for which one has to be prepared. Discussing about the error messages, one error message that often found is as follows:

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

Cause for SQL Error 1813

Before proceeding ahead, one should know the exact reason so that proper conclusion can be taken to resolve the matter. SQL error code 1813 mainly take place when damaged database log are attached to the new server.

Workaround for Microsoft SQL Server Error 1813

SQL server log is corrupted and the foremost requirement is to rebuild the database so that it can be made operational.

• Create a new database with the similar name which you are looking forward to recover which is "yourdatabasename". Ensure that name of LDF and MDF files remain same as prior the log file and database data.
• After this, stop SQL server. Then, move original MDF file to the new location by easily replacing currently created MDF file. Delete the LDF file of the new database you have just created.
• Start with the SQL server. Database will be marked as suspect.
• System tables of Master database help in updating the values. For this, follow given below process:

USE MASTER
GO
sp_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

• System tables of Master database help in updating the values. For this, follow given below process:

SELECT *
FROM sysdatabases
WHERE name = 'yourdatabasename'
—-The statement will update only one row in database
BEGIN
UPDATE sysdatabases
SET status = 32768
WHERE name = 'yourdatabasename'
COMMIT TRAN

• Restart the SQL Server
• Run DBCC command in SQL Server Management Studio. This will result in creating new log file. Make sure that you keep the name of this file same as LDF file just deleted from new database:

DBCC TRACEON (3604)
DBCC REBUILD_LOG(yourdatabasename,'c:\yourdatabasename_log.ldf')
GO

DBCC has two parameters-first it is database name and second is physical path of the log file. Ensure that path is physical otherwise, when you put logical name, an error message will appear.

• Reset the database status by using the given below command.

sp_RESETSTATUS yourdatabasename
GO

• Switch off the update to the system tables of the Master database while running following script.

USE MASTER
GO
sp_CONFIGURE 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO

• Rearrange the database status to the previous status.

Given below will update only single row in the database
BEGIN
UPDATE sysdatabases
SET status = (value retrieved IN first query OF STEP 5)
WHERE name = 'yourdatabasename'
COMMIT TRAN
GO'

Note: While going through 8, 9, 10 steps if there is error while database is in use then set the database to status single user.

sp_DBOPTION 'yourdatabasename', 'single user','true'

Once you are over with 8,9,10 steps and you find that database is not in multi user mode then run this script.

sp_DBOPTION 'yourdatabasename', 'single user','false'

Note: If the steps mentioned above unable to provide help to resolve SQL error code 1813, then you have to look for some other way out. You can opt for external online utility to fix the problem.

Fix MS SQL Server Error 1813 Conveniently

With MDF recovery software, there are higher chances to retain back data by simple steps. The manual steps mentioned above have risk of losing data. Besides this, you have to be technically sound while following the manual steps to fix SQL error 1813.