Sweet Baby

DECLARE @name VARCHAR(50) — database name

DECLARE @path VARCHAR(256) — path for backup files

DECLARE @sql VARCHAR(8000)

— specify database backup directory

SET @path = ‘\yourcomputerpublic’

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’) — exclude these databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql = ‘DECLARE @fileName VARCHAR(1000) ‘ + CHAR(13) + CHAR(10)

+ ‘declare @logs varchar(1000) ‘ + CHAR(13) + CHAR(10)

+ ‘declare @cmdstring varchar(1000) ‘ + CHAR(13) + CHAR(10)

+ ‘use [‘+@name+’] ‘ + CHAR(13) + CHAR(10)

+ ‘DECLARE db_cursors CURSOR FOR ‘ + CHAR(13) + CHAR(10)

+ ‘SELECT right(rtrim(filename), CHARINDEX(“",REVERSE(rtrim(filename)))-1) FROM dbo.sysfiles ‘ + CHAR(13) + CHAR(10)

+ ‘OPEN db_cursors ‘ + CHAR(13) + CHAR(10)

+ ‘FETCH NEXT FROM db_cursors INTO @fileName ‘ + CHAR(13) + CHAR(10)

+ ‘FETCH NEXT FROM db_cursors INTO @logs ‘ + CHAR(13) + CHAR(10)

+ ‘use [master] ‘ + CHAR(13) + CHAR(10)

+ ‘CLOSE db_cursors ‘ + CHAR(13) + CHAR(10)

+ ‘DEALLOCATE db_cursors’ + CHAR(13) + CHAR(10)

+ ‘ALTER DATABASE [‘+@name+’] SET OFFLINE WITH ROLLBACK IMMEDIATE ‘ + CHAR(13) + CHAR(10)

+ ‘set @cmdstring = “copy “"+rtrim(@fileName)+"" ‘+@path+"‘ ‘ + CHAR(13) + CHAR(10)

+ ‘exec master..xp_cmdshell @cmdstring ‘ + CHAR(13) + CHAR(10)

+ ‘set @cmdstring = “copy “"+rtrim(@logs)+"" ‘+@path+"‘ ‘ + CHAR(13) + CHAR(10)

+ ‘exec master..xp_cmdshell @cmdstring ‘ + CHAR(13) + CHAR(10)

+ ‘ALTER DATABASE [‘+@name+’] SET ONLINE ‘ + CHAR(13) + CHAR(10)

–generate attach script

+ ‘print “sp_attach_db “"‘+@name+""‘,"""+rtrim(@fileName)+""", “""+rtrim(@logs)+""""‘

print @sql

–exec(@sql)

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor