SQL Attach Database Error 1813: How to Get Rid of This

  

Published By Mohit Jha

Approved By Tej Pratap Shukla

Published On December 18th, 2021

Reading Time 4 Minutes Reading

If you are looking for a solution to solve SQL Server error 1813, this is the perfect place. In this write-up, we will share workarounds that can be used to fix SQL Server Error 1813 Attach Database

First, let us learn what could be the possible reason for getting the SQL error message 1813.

SQL Attach Database Error 1813 – Cause

The reason for this error is simple and straightforward. If a user attempts to attach any corrupt database file to new SQL Server, they will get SQL Server error 1813. In order to know its remedial method, go to the next section.

SQL Server Error 1813 Attach Database – Quick way to Fix

Since the error is occurring due to the damaged log files of SQL database, the option we have is to rebuild the database. Then it will become corruption-free and operational. Here is the manual technique to fix SQL Server Error 1813 Attach Database

Caution: This process is quite long and users need to perform each and every step attentively to achieve the desired result.

Manual Process of Fixing SQL Server Error 1813

1. 1. First, create a new database. This database must have the same name as the one you want to fix. Also make sure that names of MDF and LDF files stay similar to the earlier ones.
   2. Close any running instances of SQL Server.
   3. Now, you have to move the original SQL MDF file to the new location and replace the newly created MDF file.
   4. Delete the LDF file from the new SQL database.
   5. Now start SQL Server and You will notice that the database will be notified as marked as Suspect.
   6. Run these commands to make sure you will be allowed to upgrade values in the System tables of Master database.

USE MASTER
GO
sp_CONFIGURE ‘allow updates’, 1
RECONFIGURE WITH OVERRIDE
GO

1. Set Emergency mode for the SQL Server.
   • • This command will display the present status of the database.

   SELECT * FROM alldatabases WHERE name= Database_Name

   • • Run this command to update one row of the database.

   BEGIN
   UPDATE alldatabases
   SET status = 32768
   WHERE name = ‘Database_Name’
   COMMIT TRAN

2. Restart SQL Server.
   Note: This is a compulsory step of this method. If it is not done, SQL Server will encounter an error.
3. Go to SQL Server Management Studio’s query window to execute the ‘DBCC command.’ This will result in creating a new log file.
   Note– This new LDF file should bear the same name of the recently deleted LDF file.

   DBCC TRACEON (3604)
   DBCC REBUILD_LOG(Database_Name, ‘C:\Database_Name_log.ldf ‘)
   GO

4. Reset the database status by running the command:

5. sp_RESETSTATUS Database_Name
   GO

6. Turn off ‘system tables’ update of Master database by running this script:

   USE MASTER
   GO
   sp_CONFIGURE ‘allow updates’, 0
   RECONFIGURE WITH OVERRIDE
   GO

7. Change the database status to the Single user mode.

   sp_DBOPTION ‘Database_Name’ , ‘single user’ , ‘true’

8. If the database is still not running on multi-user mode, run this command:

   sp_DBOPTION ‘Database_Name’ , ‘single user’ , ‘false’

Resolve SQL Server Error 1813 Attach Database – Short and Simple Way

As we can see, the above-mentioned method is a lengthy process and requires the attention of the user while performing step by step process. So here is a simple and quick solution that is equally effective. Users can opt for SQL Recovery Tool. The software removes all types of corruption from MDF and NDF files. Use this application and quickly fix SQL Server error 1813.

Download Purchase Now

Conclusion:

The blog covers the SQL Server Error 1813 attach database. The error occurs when you try to attach corrupted log to SQL Server. The blog discusses the manual as well as an automated approach to resolve SQL Server error 1813.