Technical Article

Detach All User Databases

,

We have recently migrated our core live SQL Server environment from SQL Server 2005 to SQL Server 2008 making use of virtualisation from VMWare and SAN storage from NetApp.  Probably the coolest feature from the SAN solution is the ability to create application consistant snapshots and present them to other servers.

 

Let me elabourate, one of our live SQL Servers we migrated has about 1TB of data files and 500GB of log files.  In our old environment to copy and restore backups of these databases even compressed ones lets say to a UAT environment, took somewhere between 6 to 8 hours.  Utilising the SAN technology that NetApp provides we can present this same 1.5TB of data in less than 10 minutes!

 

This is done in short by taking a snapshot of the source data and log volumes then mounting these as drives in VMWare, we then detach all the current databases and attach the ones on the newly presented drives followed by unmounting the original drives.

 

During the development and testing of this process when I wanted to start afresh with a server with an instance of SQL Server with no databases I put together this quick procedure to detach all the current databases prior to removing the volumes from the VM.

 

I will, when i find the time write an article with as much detail as possible and provide the SQL and Powershell scripts we use to carry out this process.  Until then if one person finds this script useful i will be happy.

 

Enjoy

 

Chris

USE [master]
GO
IF EXISTS ( SELECT *
 FROM sys.objects
 WHERE [object_id] = OBJECT_ID(N'[dbo].[spDetachAllUserDatabases]')
 AND type IN ( N'P', N'PC' ) ) 
 DROP PROCEDURE [dbo].[spDetachAllUserDatabases]
GO
CREATE PROCEDURE [dbo].[spDetachAllUserDatabases]
AS 
 BEGIN
  --Declare Variables
 DECLARE @DatabaseName VARCHAR(100)
 DECLARE @MinDatabaseID INT
 DECLARE @MaxDatabaseID INT
 DECLARE @SQL VARCHAR(4000)
    
  --Check for temporary table and drop it if it exists
 IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL 
 DROP TABLE [#Database] ;
 
  --Create temporary table
 CREATE TABLE #Database
 (
 ID INT IDENTITY(1, 1),
 DatabaseName VARCHAR(100)
 )
 
  --Check for existing user databases
 IF EXISTS ( SELECT name
 FROM sys.databases
 WHERE database_id > 4
 AND name NOT IN ( 'SQLDBA', 'ReportServer',
 'ReportServerTempDB',
 'distribution' ) ) 
 BEGIN
  --Insert all database names into a temporary table
 INSERT INTO #Database ( DatabaseName )
 SELECT name
 FROM sys.databases
 WHERE database_id > 4
 AND name NOT IN ( 'SQLDBA', 'ReportServer',
 'ReportServerTempDB',
 'distribution' ) 
 
  --Set Variables for the detach database loop 
 SELECT @MinDatabaseID = MIN(ID),
 @MaxDatabaseID = MAX(ID)
 FROM #Database

  --Begin loop to detach databases
 WHILE @MinDatabaseID <= @MaxDatabaseID
 BEGIN
  --Get DatabaseName
 SELECT @DatabaseName = DatabaseName
 FROM #Database
 WHERE ID = @MinDatabaseID
 
  --Build Detach Database Command
 SET @SQL = 'EXEC sp_detach_db ' + '''' + @DatabaseName
 + '''' + ';'
        
  --Try Catch block to execute SQL and handle errors            
 BEGIN TRY
  --Detach Database
 EXEC ( @SQL
 )
 PRINT 'Detached ' + @DatabaseName
 END TRY
 BEGIN CATCH
 SELECT @DatabaseName,
 message_id,
 severity,
 [text],
 @SQL
 FROM sys.messages
 WHERE message_id = @@ERROR
 AND language_id = 1033 --British English
 END CATCH
        
  --Get the next DatabaseName ID
 SET @MinDatabaseID = @MinDatabaseID + 1    
  --End Loop
 END
 END
 END

GO

Rate

3.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.75 (4)

You rated this post out of 5. Change rating