By David Wiseman (Administrator)Created 25 Mar 2012
My Rating:
Vote
Rating:
Not Rated
Views:4888
Downloads:13
Source:

Script Databases for Attach

Language:  T-SQL

Compatibility

SQL Server 7 No SQL Server 2000 No
SQL Server 2005 Yes SQL Server 2008 Yes
Description

This script can be used to generate a T-SQL script to attach all the databases on your server. Run this script before detaching your databases.

Code

Line Numbers: On  Off      Plain Text
/* 
	Created By:		David Wiseman
	Website:		http://www.wisesoft.co.uk
	Description:
	Script databases for attach.  Run this script before detaching your databases to generate an attach script for each database.
	Also checks db ownership chaining, trustworthy and broker enabled settings which are normally lost as part of the detach/attach process.
	Run in SSMS and output results to text.
*/
SET NOCOUNT ON;
DECLARE @Database SYSNAME;
DECLARE @IsTrustworthy BIT;
DECLARE @IsBrokerEnabled BIT;
DECLARE @IsDBChaining BIT;
DECLARE @SQL NVARCHAR(MAX);
-- Cursor to loop over each DB
DECLARE cDatabases CURSOR FAST_FORWARD
	FOR SELECT name,is_trustworthy_on,is_broker_enabled,is_db_chaining_on
	FROM sys.databases 
	WHERE name NOT IN('master','msdb','tempdb','model')
	AND state=0; -- ONLINE
	
OPEN cDatabases;
WHILE 1=1
BEGIN;
	FETCH NEXT FROM cDatabases INTO @Database,@IsTrustworthy,@IsBrokerEnabled,@IsDBChaining;
	IF @@FETCH_STATUS <> 0
		BREAK;
	-- Generate script to attach database.
	SET @SQL = 'USE ' + QUOTENAME(@Database) + '
	SELECT ''CREATE DATABASE '' + QUOTENAME(DB_NAME()) + '' ON'' +
	STUFF((SELECT '',
	(FILENAME = '' + QUOTENAME(physical_name,'''''''') + '')''
	FROM sys.database_files
	FOR XML PATH(''''),TYPE).value(''.'',''NVARCHAR(MAX)''),1,1,'''') + ''
	FOR ATTACH
GO''';
	
	exec sp_executesql @SQL;
	
	-- Ensure trustworthy, db chaining and broker enabled options are maintained after the attach.
	IF @IsTrustworthy=1
	BEGIN;
		SELECT 'ALTER DATABASE ' + QUOTENAME(@Database) + ' SET TRUSTWORTHY ON
GO';
	END;
	IF @IsBrokerEnabled=1
	BEGIN;
		SELECT 'ALTER DATABASE ' + QUOTENAME(@Database) + ' SET ENABLE_BROKER
GO';
	END;
	IF @IsDBChaining=1
	BEGIN;
		SELECT 'ALTER DATABASE ' + QUOTENAME(@Database) ' SET DB_CHAINING ON
GO';
	END;
END;
	
CLOSE cDatabases;
DEALLOCATE cDatabases;

 


Got a useful script? Click here to upload!


 

  Post Comment
Order By:  
User Comments
      
Be the first to post a comment!