Monday, April 21, 2008

Scripting Attach/Detach of all Databases

Recently I had to upgrade a larger database server with 167 databases on it from SQL 2000 Enterprise Edition to SQL 2005 Standard. I had to maintain a fast method of attaching the databases to 2005 or 2000 in the event of an issue, rapidly, so I needed generate a script to attach/detach every database on the server.

SET NOCOUNT ON
CREATE TABLE
#Temp
(AttachScript varchar(8000))

DECLARE@sql varchar(4000)
DECLARE@dbname varchar(128)

SELECT@dbname =min(name)
FROMmaster..sysdatabases

WHILE@dbname IS NOT NULL
BEGIN
    SET
@sql =' use ['+@dbname +']
    declare @res varchar(8000)
    set nocount on
    set @res = ''exec sp_attach_db N'''''
+@dbname+''''' ''
    select @res = @res + '','''''' + rtrim(cast(filename as varchar(100))) + ''''''''
    from ['
+@dbname+']..sysfiles
    order by fileid asc
    select @res
    '
  
INSERT INTO#Temp
    EXEC(@sql)

  
SELECT@dbname =min(name)
  
FROMmaster..sysdatabases
    WHERE Name>@dbname
END
SELECT
* FROM #Temp
DROP TABLE #Temp

No comments:

Post a Comment