Detach | Attach

Eae Pessoal.

Nesse post vou disponibilizar um script que eu utilizo bastante para gerar o script de Detach ou Attach dos bancos de dados de um servidor.

Quando se tem os bancos de dados segregados em vários discos e pastas esse script se torna uma mão na roda para essa tarefa.

USE [master] ;
GO
DECLARE @database NVARCHAR( 200) ,
    @cmd NVARCHAR(1000 ) ,
    @detach_cmd NVARCHAR(4000 ) ,
    @attach_cmd NVARCHAR(4000 ) ,
    @file NVARCHAR(1000 ) ,
    @i INT ,
    @DetachOrAttach BIT;

-- 1 Gera o Script de Detach
-- 0 Gera o Script de Attach
SET @DetachOrAttach = 0 ;

DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
    SELECT  RTRIM(LTRIM ([name]))
    FROM    sys.databases
    WHERE   database_id > 4;
 -- No system databases
OPEN dbname_cur

FETCH NEXT FROM dbname_cur INTO @database

WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT  @i = 1 ;

        SET @attach_cmd = '-- ' + QUOTENAME( @database) + CHAR (10)
            + 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR (10);
 
        SET @detach_cmd = '-- ' + QUOTENAME( @database) + CHAR (10)
            + 'EXEC sp_detach_db @dbname = ''' + @database
            + ''' , @skipchecks = ''true'';' + CHAR(10 );

        DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
        FOR
            SELECT  physical_name
            FROM    sys .master_files
            WHERE   database_id = DB_ID(@database )
            ORDER BY [file_id];

        OPEN dbfiles_cur

        FETCH NEXT FROM dbfiles_cur INTO @file

        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @attach_cmd = @attach_cmd + '    ,@filename'
                    + CAST (@i AS NVARCHAR (10)) + ' = ''' + @file + ''''
                    + CHAR (10);
                SET @i = @i + 1;

                FETCH NEXT FROM dbfiles_cur INTO @file
            END

        CLOSE dbfiles_cur ;

        DEALLOCATE dbfiles_cur ;

        IF ( @DetachOrAttach = 0 )
            BEGIN
                PRINT @attach_cmd ;
            END
        ELSE
            PRINT @detach_cmd ;

        FETCH NEXT FROM dbname_cur INTO @database
    END

CLOSE dbname_cur ;

DEALLOCATE dbname_cur ;