DSAFutures Blog

Create SQL Server Attach and Detach Statements

• Use these scripts to create attach and detach statements for SQL Server 

--SQL Script Start--

Create Attach and  Detach script
 
--Detaching
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''master'', ''msdb'', ''tempdb'')
BEGIN
SELECT ''EXEC sp_detach_db ''''?'''',''''true''''''
END'

--Attaching
EXEC sp_msforeachdb
'SELECT ''EXEC sp_attach_db ''''?'''',
'''''' + RTRIM(filename) + '''''',''
FROM ?..sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM ?..sysfiles)
UNION ALL
SELECT '''''''' + RTRIM(filename) + ''''''''
FROM ?..sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM ?..sysfiles) AND
fileid < (SELECT MAX(fileid) FROM ?..sysfiles)
UNION ALL
SELECT '''''''' + RTRIM(filename) + ''''''''
FROM ?..sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM ?..sysfiles)'

--SQL Script End--

Query SQL Server 2000 or SQL Server 2005 Database Recovery Model

Query the recovery model of SQL Server 2000 or SQL Server 2005 databases using the following queries.

--SQL Script Start--
--SQL 2000
select name, databasepropertyex(name, 'Recovery') as RecoveryModel 
from master.dbo.sysdatabases order by name

--SQL 2005
select name, recovery_model, recovery_model_desc
from master.sys.databases
--SQL Script End--