Ошибка 1813 sql server

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.

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.

Also read: 3 Simple Methods to Attach SQL Database without Transaction Log File

Manual Workaround to Fix SQL Database Error 1813

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 SQL repair software that can help repair the database files while keeping the data intact.

1. Create a new 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.
2. Stop SQL Server from running.
3. Move the original MDF file to the new location by replacing the newly created MDF file.
4. Delete the LDF file of the newly created database in the SQL Server.
5. Start the SQL Server, doing which the database will be marked as ‘suspect’.
6. Confirm that the System tables of the Master database allow and assist in upgrading the values.

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

1. Alter the mode of the database to emergency mode.

   Also Read: Recover SQL Database from Emergency Mode to Normal Mode

• 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.

BEGIN
UPDATE sysdatabases
SET status = 32768
WHERE name = 'Database_Name'
COMMIT TRAN

1. Restart the SQL Server.

Note: This step is a must, or else the SQL Server will encounter an error

1. 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 ‘)
GO

1. 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.
2. Reset the status of the database by using the command given below:

sp_RESETSTATUS Database_Name
GO

1. Turn/switch off the update to ‘system tables’ of the Master database by running the script given below:

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

1. 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.

sp_DBOPTION 'Database_Name' , 'single user' , 'true'

1. After the completion of Steps 11, 12 & 13, if the database is not in multi-user mode, run the script stated below.

sp_DBOPTION 'Database_Name' , 'single user' , 'false'

With the correct execution of all these steps, the SQL Server error 1813 can be fixed. But, if the error persists, using a SQL database repair software from Stellar® may help.

SQL Database Repair Software to Fix Error 1813

Stellar Repair for MS SQL software can fix all types of corruption errors in SQL database files while maintaining database integrity. Essentially, the software helps to repair the corrupt database files and restores the database to its normal state in just a few clicks.

Steps to Use Stellar Repair for MS SQL Software

1. Launch the software.
2. 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:

1. After selecting the file, click Repair to start the repair process.
2. 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.

1. 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.

Conclusion

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.

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 tool 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.

General

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

Aisha ~
Modified: December 18, 2021 ~ 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.

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.