Generate Attach and Detach Database Script for All User Databases on SQL Instance


Managing your SQL Server databases often requires attaching and detaching databases. Below is a T-SQL script to generate the attach and detach commands for all user databases on a SQL Server instance. This script can be used on SQL Server versions 2008, 2012, 2014, and 2016.

Script to Generate Attach and Detach Commands
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;

-- Change this to '0' for sp_attach_db commands; or '1' for sp_detach_db commands
SET @DetachOrAttach = 0;

-- 0 Generates Attach Script
-- 1 Generates Detach Script

DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
    SELECT RTRIM(LTRIM([name]))
    FROM sys.databases
    WHERE database_id > 4; -- Exclude 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);

    -- Get a list of files for the database
    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
        -- Output attach script
        PRINT @attach_cmd;
    END
    ELSE
    BEGIN
        -- Output detach script
        PRINT @detach_cmd;
    END

    FETCH NEXT FROM dbname_cur INTO @database;
END

CLOSE dbname_cur;
DEALLOCATE dbname_cur;

Usage

  1. Copy the Script: Copy the above script into your SQL Server Management Studio (SSMS).
  2. Set Parameter: Change the @DetachOrAttach parameter to 0 for generating attach scripts or 1 for detach scripts.
  3. Run the Script: Execute the script to generate the respective attach or detach commands.

Explanation

  • Variable Declarations: Variables are declared to hold database names, commands, and file paths.
  • Cursor for Databases: A cursor (dbname_cur) is declared to iterate over all user databases.
  • Attach and Detach Commands: Based on the @DetachOrAttach parameter, either attach or detach commands are generated.
  • Cursor for Files: Another cursor (dbfiles_cur) is used to get all file paths for each database.
  • Print Commands: The generated commands are printed for execution.

By using this script, you can easily generate the necessary T-SQL commands for attaching or detaching all user databases on your SQL Server instance, simplifying your database management tasks.