Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

Attach a SQL Server database without its log file: error 1813 and when ATTACH_REBUILD_LOG works

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

When you attach a data file (.mdf) with no log file (.ldf) and SQL Server cannot build a replacement, the last message is error 1813: "Could not open new database '%.*ls'. CREATE DATABASE is aborted." The error is the closing line, so the reason sits in the message above it. SQL Server can only rebuild a log for a read/write database that was shut down cleanly, with all its data files present.

If those conditions hold, FOR ATTACH_REBUILD_LOG creates a new log, and the old log backup chain is gone. If they do not, the documented route is a restore from backup. Work on a copy of the files, never the only original.

What the error means

Microsoft's list of events and errors 1000 to 1999 gives error 1813 as severity 16 with the text "Could not open new database '%.*ls'. CREATE DATABASE is aborted." That page describes no cause in its own words: the message is the end of a failed CREATE DATABASE. The line or lines before it name what failed, such as a missing log, a permission error or a data file problem.

Microsoft's CREATE DATABASE page separates two attach forms:

  • FOR ATTACH needs all data files (MDF and NDF) to be available. If several log files exist, they must all be available. If a read/write database has a single log file that is unavailable, and the database was shut down with no users or open transactions before the attach, "FOR ATTACH automatically rebuilds the log file and updates the primary file." For a read-only database the log cannot be rebuilt, because the primary file cannot be updated, so you must supply the log files.
  • FOR ATTACH_REBUILD_LOG is limited to read/write databases. If one or more log files are missing the log is rebuilt, and "The ATTACH_REBUILD_LOG automatically creates a new, 1-MB log file", placed in the default log-file location. It requires "A clean shutdown of the database" and all data files. If the old log files are present, the Database Engine uses them rather than rebuilding.

Microsoft's detach and attach page adds the sequence for a single missing log: if the old log is found, it is used "regardless of whether the database was shut down cleanly"; if it is not found and the database was shut down cleanly with no active log chain, the attach attempts to build a new one. Microsoft also advises you to "always keep all the detached log files until the database is successfully attached without them."

Microsoft's CREATE DATABASE page carries a warning that matters here: "This operation breaks the log backup chain. We recommend that you take a full database backup immediately after the operation is completed."

Likely causes

Cause What you see How to confirm
The database was not shut down cleanly The files were copied from a running instance, a crashed server or a snapshot; error 1818 or 5113 appears above 1813 Read the messages above 1813 for 1818 or 5113; ask how the files were taken
The database is read-only Attach fails even though the log is gone; error 5129 appears above 1813 Read the messages above 1813 for 5129; check whether the source database was set read-only
Database mirroring is enabled Error 5130 appears above 1813 Read the messages above 1813 for 5130
A data file (NDF) is missing Messages name a second data file List the files the source database had, compare with what you copied
A permission or path error comes first 5120 or 17204 appears above 1813 Read the full message list in SSMS or the error log
The log exists elsewhere but the path is wrong The attach looks in the old location Search the old server or backup for the .ldf

Prove the cause before changing anything

Read-only steps first.

  1. Read every message the failed attach returned, not just the last. Copy them out of the SSMS messages pane or the SQL Server error log. If 5120 ("Operating system error 5: Access is denied") appears, the SQL Server service or the attaching account lacks permission on the files; fix the folder permissions for the per-service SID and try again before looking at the log at all. Match the other lines to Microsoft's messages: 1818, "Primary log file '%ls' is missing and the database was not cleanly shut down so it cannot be rebuilt."; 5113, "The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only."; 5129, "The log cannot be rebuilt when the primary file is read-only."; 5130, "The log cannot be rebuilt when database mirroring is enabled."
  2. List what you have. In File Explorer, open the folder that holds the copied files, such as D:\SQLData. Count the .mdf and any .ndf files and compare them with the source database. All data files must be present.
  3. Find out how the files were produced. Microsoft's rebuild path depends on a clean shutdown, and the pages do not document a way to test for one from outside. Ask whether the database was detached, or the service stopped, before the copy. Files copied while the service was running, or from a crashed server, are not a clean shutdown.
  4. Confirm the target instance and version. Microsoft says a database created by a more recent version of SQL Server cannot be attached in earlier versions.
  5. Look for the old log. Microsoft says the attach uses an old log it finds in the old location. Check the original server, file-level backups and any VM backup for it. The pages do not say what a rebuild loses beyond the chain, so a recovered original log is the better outcome.

Make a copy of the .mdf and any .ndf files before you do anything else, and keep the originals untouched. Microsoft states that when FOR ATTACH rebuilds a log it "updates the primary file", so assume that a rebuild changes the file you point it at.

Fix

You have a backup that covers the database. Restore it with RESTORE DATABASE. A restore does not use the copied files at all, so it does not depend on how they were shut down. It is the route to take when the files may not be clean.

Risk: a restore onto a name that already exists on the server can overwrite that database. Restore to a new database name, or to a name whose current database you have confirmed may be replaced. There is no way to undo a restore over an existing database except another backup, so take one first if the name is in use.

The original log exists. Attach with all the files in place.

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

Risk: an attach changes the files it uses and registers a new database on the instance. Attach a copy, not your only original. To back out, detach the attached copy using the detach procedure on Microsoft's detach and attach page and start again from the untouched original.

Microsoft notes that you only need a filespec entry for the primary file and for any file whose path differs from where it was created or last attached.

The log is gone, the database was shut down cleanly, and it is read/write.

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

Disruptive step: this writes to the primary file and breaks the log backup chain. There is no way back to the old chain, and the attach changes the .mdf you give it, so run it on a copy. After it works, take a full backup straight away, as Microsoft advises, and restart the log backup schedule from that backup. If the attach fails, delete the copy and start again from the original. Add an extra filespec for each .ndf whose location differs from where SQL Server last had it.

The log is gone and the database was not shut down cleanly. Microsoft's pages state that a clean shutdown is a requirement for FOR ATTACH_REBUILD_LOG, and they describe no supported way to rebuild from an unclean state. Restore from backup, or look for the original log. A forced repair exists in DBCC CHECKDB as REPAIR_ALLOW_DATA_LOSS, whose name carries its warning; it is a corruption decision to make with a verified backup in hand, not a way around a missing log. No command for it is given here.

The database is read-only. Microsoft says you must provide the log files. Find them first.

A data file is missing. Find the missing NDF. All data files must be present for either attach form.

Confirm the fix

After the attach, check the state and the new file.

SELECT name, state_desc FROM sys.databases WHERE name = N'SalesDB';
SELECT name, type_desc, physical_name, size * 8 / 1024 AS size_mb
FROM SalesDB.sys.database_files;

The database should be ONLINE. The log row should show a new file of about 1 MB in the default log location, per Microsoft's text. Then run a consistency check, since SQL Server did not verify the data against the old log.

DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS;

Risk: CHECKDB reads the whole database and can slow other work while it runs. It makes repairs only when you give it a repair option, and you are not using one here. Then check application-level data as well, because the pages do not say what a rebuilt log cannot reproduce. Take the full backup and confirm it completes.

Example (illustrative): on sql03, the volume holding D:\SQLLog for LedgerDB fails at 09:50 on Tuesday. The .mdf (85 GB) survives on D:\SQLData; the 12 GB .ldf does not. A full backup ran on Sunday at 22:00 and hourly log backups ran through Tuesday 09:00. The data file was in use when the volume failed, so it is not a clean shutdown, and an attach with ATTACH_REBUILD_LOG is not a documented route. The administrator restores instead: one full backup plus the hourly logs from Sunday 23:00 to Tuesday 09:00. That span is 34 hours, which gives 35 log backups (34 intervals plus the first). Work after 09:00 and before the 09:50 failure, about 50 minutes, can only come from the damaged volume or the application. The chain is intact because no rebuild happened.

What to watch afterwards

  • Keep detached log files until the database is attached and checked, as Microsoft advises.
  • Take a full backup after any attach that rebuilt a log, then confirm the next log backup starts a new chain.
  • Size the new log file on purpose. Microsoft gives the rebuilt log's size (1 MB) and location, so set a log size that suits the workload through your normal change process.
  • Add a runbook step: detach or stop the service, then copy the .mdf, .ndf and .ldf together.
  • Alert on 1813, 5120 and 17204 in the error log.

Risks and limits

These commands have not been run or lab-tested. Microsoft's error lists give messages 1818, 5113, 5129 and 5130 for the cases where a log cannot be rebuilt, but the pages do not say what a rebuild may lose when it succeeds on a database that was not shut down cleanly. Treat any rebuilt database as unverified until the consistency check, the application check and a new full backup are done.

An attach changes the files it uses. Do not run it on your only copy. Do not attach a database from an unknown source to a production instance: Microsoft warns against attaching databases from unknown or untrusted sources, because they could contain malicious code.

Sources