SQL Attach Database Error 1813: How to Get Rid of This
“I attached log file to the SQL Server and I received an error message saying “SQL Attach Database Error 1813” I do not know why it is showing SQL Server error 1813. The database seems fine to me but showing this error. Please tell me solution to get rid of this error message.”
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
- 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.
- Close any running instances of SQL Server.
- Now, you have to move the original SQL MDF file to the new location and replace the newly created MDF file.
- Delete the LDF file from the new SQL database.
- Now start SQL Server and You will notice that the database will be notified as marked as Suspect.
- 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
- 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
- Restart SQL Server.
Note: This is a compulsory step of this method. If it is not done, SQL Server will encounter an error.
- 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
- Reset the database status by running the command:
sp_RESETSTATUS Database_Name GO
- Turn off ‘system tables’ update of Master database by running this script:
USE MASTER GO sp_CONFIGURE ‘allow updates’, 0 RECONFIGURE WITH OVERRIDE GO
- Change the database status to the Single user mode.
sp_DBOPTION ‘Database_Name’ , ‘single user’ , ‘true’
- 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.
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.