How to Fix SQL Database Error 1813 Using 4 Methods

What is SQL Server Error 1813?

SQL Server raises error 1813 when CREATE DATABASE cannot open the new database. You see the full message as Msg 1813, Level 16, State 2, Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.

Attaching a database uses CREATE DATABASE with the FOR ATTACH clause so a failed attach ends with this message. Check the lines above it in the Messages tab because the first error names the real cause.

What Causes Error 1813?

There are five possible reasons which cause Error 1813:

• Damaged Log File. SQL Server cannot open a corrupt transaction log file while it attaches the database.
• Missing Log File. Attach rebuilds a missing log file only when the database shut down cleanly.
• Newer Database Version. A database from a newer SQL Server version cannot attach to an earlier version.
• Damaged Data File. A corrupt MDF or NDF file stops the attach.
• Power Failure. A sudden power loss stops SQL Server without a clean shutdown and can damage the files.

What to Check Before You Fix the Error?

Prepare the files before you try any fix.

• Copy the MDF, NDF and LDF files to a safe folder.
• Note the first error shown above error 1813 in the Messages tab.
• Note the SQL Server version of the source server and the destination server.
• Find your last full backup.
• Check that the disk has free space for the copies and a new log file.

Which Free Methods Fix Error 1813 in SQL Server?

There are three free methods to fix the SQL 1813 error with SQL Server Management Studio (SSMS) and T-SQL. Let’s check the safest option first:

Backup Restore (Free, Safest)

Microsoft names a restore from the last known good backup as the primary fix for a damaged database.

1. Right click Databases in Object Explorer and select Restore Database.
2. Select Device under Source and click the browse button.
3. Select File in Backup media type, click Add and select your BAK file.
4. Click OK in both windows.
5. Select Options and tick Overwrite the existing database (WITH REPLACE) when a database with the same name exists.
6. Use the default RESTORE WITH RECOVERY option and click OK.

Use this T-SQL to restore the same backup.

RESTORE DATABASE [SalesDB]
FROM DISK = N'E:\Backup\SalesDB.bak'
WITH REPLACE, RECOVERY;

Restoring returns data up to the backup time only. Data saved after that time is lost.

ATTACH_REBUILD_LOG (Free, Needs a Clean Shutdown)

ATTACH_REBUILD_LOG attaches the data files and builds a new log file. Microsoft lists three needs. You need a clean shutdown and every data file and a writable database.

1. Rename the damaged LDF file because attach uses an available log file instead of rebuilding it.
2. Open a New Query window in SSMS.
3. Enter the T-SQL below with your database name and file path and click Execute.

CREATE DATABASE [SalesDB]
ON (FILENAME = N'D:\SQLData\SalesDB.mdf')
FOR ATTACH_REBUILD_LOG;

Add a FILENAME line for every NDF file whose path differs from the path stored when you created or last attached the database. SQL Server builds a new 1 MB log file in the default log file location.

Take a full backup of the database right after this method ends because it breaks the log backup chain. Our Restore SQL Server Database Without Transaction Log File guide explains more log file options.

Emergency Repair With DBCC CHECKDB (Free, Data Loss Risk)

Use this method when the database did not shut down cleanly. SQL Server rebuilds the log file during the repair and Microsoft warns that the rebuild can break transactional consistency.

1. Copy the original data files to a safe folder.
2. Create a new database with the same name. Give its data files the same names as the original files.
3. Stop the SQL Server service.
4. Copy the original MDF and NDF files over the new files and delete the new LDF file. Use copy and paste instead of cut.
5. Start the SQL Server service. SQL Server marks the database as suspect.
6. Open a New Query window and execute the T-SQL below with your database name.

ALTER DATABASE [SalesDB] SET EMERGENCY;
ALTER DATABASE [SalesDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('SalesDB', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [SalesDB] SET MULTI_USER;

Execute DBCC CHECKCONSTRAINTS ('SalesDB') after the repair and take a full backup. Microsoft states that no full ACID guarantee exists after a log rebuild and SQL Server sets the log size to its default size.

Older fixes for error 1813 update the status column in sysdatabases and set the allow updates option. Microsoft states that the allow updates option has no effect and that direct updates to system tables are not supported. Check the Microsoft allow updates documentation before you copy an old script.

How to Fix SQL Database Error 1813 Through EmailDoctor Software?

EmailDoctor SQL Database Recovery repairs damaged MDF and NDF files from SQL Server 2019, 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005 and 2000. Tables, stored procedures, functions, views, rules, indexes, triggers, keys, columns and data types come back from the file. Use the software when error 1813 remains after the free methods.

Free demo previews every recovered object and exports 25 records for every table and 5 other database objects.

Five features help you recover a database.

• Quick and Advanced Scan. Quick Scan handles normal corruption and Advanced Scan handles highly corrupted files.
• SQL Server Version. Tick Auto detect or select the version of your MDF file.
• NDF Files. Detect the NDF files of the MDF file automatically or add them manually.
• Deleted Records. Preview permanently deleted records in red.
• Export Options. Export to a SQL Server database or a SQL Server compatible script or a CSV file.

Check the system needs before you install the software.

• Operating System. Windows 10, 8 or 7 and Windows Server 2016, 2012 R2 or 2008.
• Disk Space. 100 MB of free space.
• Memory. 4 GB of RAM recommended.
• Framework. .NET Framework 4.5 or newer.

Recover a Damaged MDF File for Error 1813 on Windows

1. Open the software from the Start menu.
2. Click Open and select the damaged MDF file.
3. Select Quick Scan. Tick Auto detect to find the SQL Server version of the file or select the version from the list.
4. Open the NDF Options tab. Select the first option to detect NDF files automatically or select the second option and click Add Files or Add Folder. Click OK.
5. Wait while the software scans the file and shows the scan report.
6. Click OK on the Quick Scan note and preview the tables, views, stored procedures, rules, triggers and functions.
7. Click Export and select Export As SQL Server Database.
8. Select your server name from the list or type it. Select Windows Authentication or SQL Server Authentication and enter your login details.
9. Select Create New Database or select Export to Existing Database to overwrite an existing database.
10. Type a name for the new database.
11. Check the default collation and select another one from the drop down list when you need it. Tick the database components you want to export.
12. Select With Only Schema or With Schema & Data and click Export.
13. Click OK in the Export Completed window and click Close in the status window.
14. Expand the databases in SQL Server to find the recovered database.

    Select Advanced Scan and tick Recover Deleted Objects when Quick Scan does not show a table or a stored procedure you need. Our Recover Deleted Objects of SQL Database guide explains the deleted object scan.

    Want to stop damage before it starts? Our SQL Server Database Corruption Causes and Solutions guide lists the causes.

Which Fix Suits Your Database?

Four methods fix error 1813 but they differ in needs and risk.

Start with the backup. Use ATTACH_REBUILD_LOG after a clean shutdown and use the emergency repair as the last free step. Recovery software can resolve your problem faster than free methods and does not require any prior technical knowledge.

What Problems Appear While You Fix Error 1813?

Problems that can slow down the process of fixing SQL 1813 error are:

• Attach Fails Again. Rename the old LDF file because attach uses an available log file instead of building a new one.
• Database Shows Suspect Status. Set the database to EMERGENCY and repair it with DBCC CHECKDB.
• Single User Command Waits. Close other connections or add WITH ROLLBACK IMMEDIATE to the command.
• Newer Version Error. Attach the database to a server with the same version or a newer version.
• sp_configure Fails. Remove the allow updates lines from your script because the option has no effect.
• Log File Is Small After Repair. Resize the log file because the repair sets it to the default size.

Error 5120 is another attach error. Our Fix SQL Server Error 5120 During Attach guide explains it.

Frequently Asked Questions

Final Words

Error 1813 appears when an attach cannot open the database and the first error above it names the cause. Two causes are a damaged log file and a newer database version.

Copy the MDF, NDF and LDF files before any repair. Restore a backup first and use the emergency repair as the last free step.

EmailDoctor software recovers tables and other objects from a damaged MDF file and exports them to a new database.