Get Detach or Attach all user databases script

• harsha.majety

  SSCommitted

  Points: 1537

  More actions

  February 5, 2013 at 1:57 am

  #394105

  Comments posted to this topic are about the item Get Detach or Attach all user databases script

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 2, 2016 at 4:08 pm

  #1876101

  Lots of good scripts Harsha.

• Jonathan AC Roberts

  SSCoach

  Points: 18257

  More actions

  November 14, 2018 at 5:14 am

  #2013415

  Thanks Harsha, a great script (5 stars awarded).
  This saved me a lot of effort as I had to detach and then reattach about 50 databases on two instances as the collation needed to be changed on the system databases.
  I updated the script slightly so it would output in the same format that my version of SQL Server generates

  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;

  SET @DetachOrAttach = 1;

  -- 1 Generates Detach Script
-- 0 Generates Attach Script
PRINT 'USE [master]'
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
  SELECT RTRIM(LTRIM([name]))
  FROM  sys.databases
  WHERE database_id > 4
  and name <> 'distribution';
-- 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) + '
CREATE DATABASE ' + QUOTENAME(@database) + ' ON
'
  -- Change skip checks to false if you want to update statistics before you detach.
   SET @detach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
    + 'ALTER DATABASE ' + QUOTENAME(@database) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC sp_detach_db @dbname = ''' + @database    + ''';
GO
'

    -- 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 += '( FILENAME = N' + QUOTENAME(@file,'''') + ' )'
      FETCH NEXT FROM dbfiles_cur INTO @file
      IF @@FETCH_STATUS = 0
       SET @attach_cmd += ',' + CHAR(13) + CHAR(10)
    END
    SET @attach_cmd += '
FOR ATTACH
GO
'

     CLOSE dbfiles_cur;

     DEALLOCATE dbfiles_cur;

     IF ( @DetachOrAttach = 0 )
    BEGIN
    -- Output attach script
      PRINT @attach_cmd;
    END
   ELSE -- Output detach script
    PRINT @detach_cmd;

     FETCH NEXT FROM dbname_cur INTO @database
  END
CLOSE dbname_cur;
DEALLOCATE dbname_cur;