Skryptuj: Attach wszystkich baz danych użytkownika na instancji

-- attach all user databases 
-- Uwaga !!!
-- Skrypt należy użyć przed odłączeniem baz danych.
-- Przykład:
/*
 -- [demo_db]
 EXEC sp_attach_db @dbname = 'demo_db'
      ,@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\demo_db.mdf'
      ,@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\demo_db_log.ldf'
*/
-------------------------------------------------------------------------------------
 
USE [master];
GO
DECLARE @DatabaseName NVARCHAR(200) ,
    @sql_cmd NVARCHAR(4000) ,
    @file NVARCHAR(1000) ,
    @i INT

DECLARE DatabaseNameList_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
    SELECT  RTRIM(LTRIM([name]))
    FROM    sys.databases
    WHERE   database_id > 4  -- only users databases
	AND STATE_DESC='ONLINE';
PRINT 'SERVER NAME : ' + upper(@@servername) + '--> Begin script databases'
PRINT '----------------------------------------------'
PRINT ''
PRINT ''
-- external cursor with databases list
OPEN DatabaseNameList_cur
FETCH NEXT FROM DatabaseNameList_cur INTO @DatabaseName
 
WHILE @@FETCH_STATUS = 0 
    BEGIN
        SET  @i = 1;
 
        SET @sql_cmd = '-- ' + QUOTENAME(@DatabaseName) + CHAR(10)
            + 'EXEC sp_attach_db @dbname = ''' + @DatabaseName + '''' + CHAR(10);
     
	    -- internal cursor with physical files name list for each database
	    DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
        FOR
            SELECT  physical_name
            FROM    sys.master_files
            WHERE   database_id = DB_ID(@DatabaseName)
            ORDER BY [file_id];
 
        OPEN dbfiles_cur
 
        FETCH NEXT FROM dbfiles_cur INTO @file
 
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                SET @sql_cmd = @sql_cmd + '    ,@filename'
                    + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
                    + CHAR(10);
                SET @i = @i + 1;
 
                FETCH NEXT FROM dbfiles_cur INTO @file
            END
        -- close inetrnal cursor
        CLOSE dbfiles_cur;
        DEALLOCATE dbfiles_cur;
        PRINT @sql_cmd;
 
        FETCH NEXT FROM DatabaseNameList_cur INTO @DatabaseName
    END
CLOSE DatabaseNameList_cur;
DEALLOCATE DatabaseNameList_cur;


PRINT '----------------------------------------------'
PRINT ''
PRINT ''
print 'SERVER NAME : ' + upper(@@servername) + '--> All databases successfully scripted'