SQL Server – Script to Detach Attach Databases

\ by

This uses the old syntax.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT DISTINCT
 DB_NAME(dbid) AS DatabaseName,
 fileid,
 CASE WHEN fileid = 1 THEN 'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
 ELSE ''
 END DetachScript,
 CASE WHEN fileid = 1
 THEN 'exec sp_attach_db @dbname = N''' + DB_NAME(dbid) + '''' + ',@filename' + CONVERT(VARCHAR(10), fileid) + '=N''' + filename + ''''
 ELSE ',@filename' + CONVERT(VARCHAR(10), fileid) + '=N''' + filename + ''''
 END AS AttachScript
FROM master.dbo.sysaltfiles
WHERE DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'
 AND DB_NAME(dbid) IN ('Sandbox')
ORDER BY DB_NAME(dbid),
 fileid
GO
Posted in: SQL