Generate Database Offline-Online Scripts

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Not Tested          

Credits:

Author: ChillyDBA
Date:    6 Jun 2020

Description

Generates TSQL Scripts to take the source databases offline quickly - open connections are killed first.
Also included is a commented out line to bring the database back online (in case of backout)

GENERATE script on:     SOURCE SERVER (per USER database to be migrated)
EXECUTE script on:       DESTINATION SERVER (per USER database to be migrated)

Code


--******************************************* --**** RUN THIS ON THE SOURCE SERVER *** --******************************************* -- use this script to generate code to take all in scope DBs on a server offline , with supplemental code to bring it online again -- save each DB script to a separate file using the file name at the top of the section SET NOCOUNT ON --******************************************* --**** Define and set parameter values *** --******************************************* DECLARE @DatabaseName SYSNAME ,@DatabaseID INT DECLARE @OfflineOnlineDatabaseList TABLE ( DatabaseID INT ,DatabaseName SYSNAME ) DECLARE @OfflineOnlineDatabaseResults TABLE ( ResultID INT IDENTITY (1,1) ,ResultText SYSNAME ) SELECT @DatabaseName = NULL --optionally, supply a DB name for generaton of command for a single database INSERT @OfflineOnlineDatabaseList (DatabaseID, DatabaseName) SELECT database_id, name FROM master.sys.databases WHERE name NOT IN ('master','model','tempdb','msdb','reportserver','reportservertempdb', 'distribution') AND name = ISNULL(@DatabaseName, name) AND state_desc IN ('ONLINE') SELECT @DatabaseID = MIN (DatabaseID) FROM @OfflineOnlineDatabaseList WHILE @DatabaseID IS NOT NULL BEGIN SELECT @DatabaseName = DatabaseName FROM @OfflineOnlineDatabaseList WHERE DatabaseID = @DatabaseID INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ START SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '-- Save as file name: 099. ' + @@SERVERNAME + '_set_database_' + @DatabaseName + '_offline_online.sql' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--**********************************************************************************' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--***** Purpose: Set Database [' + @DatabaseName + '] offline or online during migration' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--***** Generated on: ' + CONVERT(VARCHAR(12), GETDATE(), 112) INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--***** Generated by: ' + SUSER_SNAME() INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--**********************************************************************************' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT 'USE [' + @DatabaseName + ']' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT ' GO' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET MULTI_USER' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT ' GO' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT 'ALTER DATABASE [' + @DatabaseName + '] SET OFFLINE' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT ' GO' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--ALTER DATABASE [' + @DatabaseName + '] SET ONLINE' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT ' --GO' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ END SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' INSERT @OfflineOnlineDatabaseResults (ResultText) SELECT '' SELECT @DatabaseID = MIN (DatabaseID) FROM @OfflineOnlineDatabaseList WHERE DatabaseID > @DatabaseID END SELECT ResultText FROM @OfflineOnlineDatabaseResults ORDER BY ResultID SET NOCOUNT OFF






ċ
Andy Hughes,
Jun 6, 2020, 11:08 AM
Comments