SQL Server Database Will Not Attach: What to Check

Attaching a database means pointing SQL Server at existing data and log files. When it fails, the error usually names the reason, and the cause is often much simpler than corruption: a permissions problem, a version mismatch, or a log file that is missing or does not belong with the data file. Working through the causes in a sensible order, on a copy of the files, keeps the simple problems from becoming serious ones.

Copy the MDF, any NDF files and the LDF to a separate location, and do the attach experiments on the copies. An attach that is attempted repeatedly, or with options that rebuild the log, changes the files. Having the originals untouched leaves every later option open.

A common error is an operating system access-denied message when SQL Server tries to open the file. The SQL Server service account needs read and write permission on the files and on the folder that holds them. Files copied from another machine, or from a backup, often carry permissions that do not include this account. Fixing the permissions is safe and often resolves the error entirely.

A database can be attached to the same or a newer version of SQL Server, but not to an older one, and SQL Server will report the version mismatch. Attaching to a newer version upgrades the database, and it cannot then be moved back, so it is worth doing on a copy first.

If the log file is missing, SQL Server can sometimes attach the database and build a new log with the ATTACH_REBUILD_LOG option, but this generally works only if the database was shut down cleanly. If it was not, the log holds transactions that were never written to the data file, and rebuilding it can leave the database inconsistent. A log file that comes from a different point in time than the data file is a similar problem, because the two must belong together.

After any attach that involved a rebuilt or replaced log, run DBCC CHECKDB and treat the result with care until it comes back clean.

Avoid putting the database into emergency mode and running a repair with data loss allowed as a first move. Those options work by discarding what cannot be made consistent, and once run, that data is gone. Also avoid restoring an old backup over the files before copying them, and avoid running different attach methods in succession against the same original files.

If the attach fails even after permissions, version and log have been checked, or the data file itself is damaged, the recovery source becomes the MDF and its log. Data can often be read from damaged files directly, but how much depends on the damage, and we tell you exactly what is recoverable after a free assessment. Keep the originals untouched and work from a copy.