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 INTDECLARE @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, nameFROM master.sys.databasesWHERE 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 @OfflineOnlineDatabaseListWHILE @DatabaseID IS NOT NULLBEGINSELECT @DatabaseName = DatabaseNameFROM @OfflineOnlineDatabaseListWHERE DatabaseID = @DatabaseIDINSERT @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 @OfflineOnlineDatabaseListWHERE DatabaseID > @DatabaseIDENDSELECT ResultTextFROM @OfflineOnlineDatabaseResultsORDER BY ResultIDSET NOCOUNT OFF