Attaching and Detaching SQL Server Databases

Detach and attach is the quickest way to move a database between instances or to a different drive when you have file-level access to both sides: take the database offline, copy the .mdf and .ldf files, and register them on the new instance. There is no backup to write and restore, so for a 500 GB database on a fast SAN it is minutes instead of hours. The trade-off is that the database is unavailable from detach until attach, and a mistake with file permissions or a missing log file leaves you with a database that will not come back.

I reach for this when a SAN migration leaves me a short window and a big database. What follows is the order of operations I trust on SQL Server 2016 and 2017, and how to recover when the attach fails.

Two things to know first. The target must be the same or a newer version (2012 onto 2017 is fine, never the other way round), and you need sysadmin on both instances, or db_owner plus CREATE DATABASE.

Write down the file list first

Once detached, sys.master_files no longer knows about the database, so capture what you will need:

SELECT name, physical_name, type_desc, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('ShopDB');

SELECT name, compatibility_level, recovery_model_desc, is_broker_enabled, owner_sid
FROM sys.databases WHERE name = 'ShopDB';

Also script the logins that own users in the database; they must exist on the target or the users will be orphaned.

Kick users out and detach

USE master;
ALTER DATABASE ShopDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db @dbname = N'ShopDB', @skipchecks = 'true';

ROLLBACK IMMEDIATE terminates open transactions. @skipchecks = 'true' skips the UPDATE STATISTICS that detach otherwise runs, which on a large database can take a long time for no benefit. If the database is part of replication, mirroring or an Availability Group, remove it from those first; detach refuses otherwise.

Copy the files

robocopy D:\SQLData\ShopDB \\sqlsrv02\D$\SQLData\ShopDB ShopDB.mdf ShopDB_log.ldf /Z /R:3

I copy, never move, until the attach on the other side has succeeded. Then give the target service account permission on the files:

icacls "D:\SQLData\ShopDB\ShopDB.mdf" /grant "NT SERVICE\MSSQLSERVER:(F)"
icacls "D:\SQLData\ShopDB\ShopDB_log.ldf" /grant "NT SERVICE\MSSQLSERVER:(F)"

For a named instance the account is NT SERVICE\MSSQL$INSTANCENAME. On Linux the files must be owned by mssql:mssql and typically live under /var/opt/mssql/data.

Attach on the target

Use CREATE DATABASE ... FOR ATTACH. sp_attach_db still works but is deprecated and limited to 16 files.

CREATE DATABASE ShopDB
ON (FILENAME = N'D:\SQLData\ShopDB\ShopDB.mdf'),
   (FILENAME = N'D:\SQLData\ShopDB\ShopDB_log.ldf')
FOR ATTACH;

List every data file; the log file must be included if it exists. If you have the data files but the log is genuinely lost and the database was cleanly detached (no open transactions), you can rebuild a log:

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

This only works when the database was shut down cleanly. If it was not, you have lost transactions and this is a restore-from-backup situation.

Cleanup: owner, compatibility level, users

ALTER AUTHORIZATION ON DATABASE::ShopDB TO sa;
ALTER DATABASE ShopDB SET COMPATIBILITY_LEVEL = 140;   -- if you want the new engine's behaviour
ALTER DATABASE ShopDB SET MULTI_USER;

Attaching keeps the old compatibility level; it does not upgrade it automatically. Then re-map orphaned users:

USE ShopDB;
EXEC sp_change_users_login 'Report';
ALTER USER app_user WITH LOGIN = app_user;

Attaching a database from an older version runs the internal upgrade steps immediately, which is why the source can never go back.

Check it

SELECT name, state_desc, user_access_desc, compatibility_level FROM sys.databases WHERE name = 'ShopDB';
DBCC CHECKDB ('ShopDB') WITH NO_INFOMSGS;

Run CHECKDB after any file copy. A silent copy error would show up here, not in the attach.

When the attach fails

"Msg 5120, Level 16: Unable to open the physical file "D:\SQLData\ShopDB\ShopDB.mdf". Operating system error 5: "5(Access is denied.)" The service account cannot read the file. When you copy files as yourself, the new copy inherits only the folder permissions, and SQL Server does not automatically add itself. Fix it with icacls as shown above.

"Msg 1813: Could not open new database 'ShopDB'. CREATE DATABASE is aborted." Usually paired with an error about the log file: the log was not cleanly detached, or you listed the wrong log file. Do not try ATTACH_REBUILD_LOG blindly; check whether the original instance is still available and detach it properly.

"Msg 948: The database 'ShopDB' cannot be opened because it is version 852. This server supports version 782 and earlier." You are attaching to an older version. Version 852 is SQL Server 2016; 782 is 2014. Move it to the correct instance or restore a backup with a different approach (there is none for downgrading; scripting the data is the only route).

"Msg 3707: Cannot detach a suspect or recovery pending database." Detaching a broken database makes it worse. Run ALTER DATABASE ShopDB SET EMERGENCY and DBCC CHECKDB first, or restore from backup.

Where downtime matters more than speed, I use backup and restore instead. It never leaves the source unusable.