SQL Server: Safely Attach Multiple Databases

The old sp_attach_db procedure is deprecated. Modern SQL Server uses CREATE DATABASE ... FOR ATTACH, and every data and log file must be accounted for.

Prefer backup and restore

For planned migrations, a tested full backup and restore is usually safer and easier to audit. Attach is appropriate when you have a cleanly detached, trusted set of database files and understand the version and file requirements.

Attach one database

USE master;
GO

CREATE DATABASE [Sales]
ON
  (FILENAME = N'D:\SQLData\Sales.mdf'),
  (FILENAME = N'D:\SQLData\Sales_Archive.ndf'),
  (FILENAME = N'E:\SQLLogs\Sales_log.ldf')
FOR ATTACH;
GO

Include every .mdf, .ndf, and .ldf file when paths have changed. The SQL Server service account must have access to each location.

Generate manifests before detaching

On the source instance, inventory all user-database files before any detach operation:

SELECT DB_NAME(database_id) AS database_name,
       file_id,
       type_desc,
       physical_name
FROM   sys.master_files
WHERE  database_id > 4
ORDER  BY database_id, file_id;

Use that reviewed inventory to generate one explicit CREATE DATABASE ... FOR ATTACH statement per database. Avoid a script that assumes each database has only one data file and one log file.

Safety checklist

• Attach only databases from a known, trusted source.
• A database from a newer SQL Server version cannot be attached to an older version.
• Ensure the source database was cleanly detached or shut down.
• Check file permissions, free space, encryption keys, contained features, and server-level dependencies such as logins and jobs.
• Run DBCC CHECKDB after attachment and repair orphaned user mappings where needed.
• If FOR ATTACH_REBUILD_LOG is ever required, understand that it breaks the log backup chain and take a full backup immediately.

Reference

Microsoft documents the requirements in CREATE DATABASE (Transact-SQL) and lists sp_attach_db as deprecated.