Attach Database Scripts

I was tasked with migrating a development database server to another development database server. Time was of the essence, so the decision was made to detach the files from the original database server and attach them to the new server.

There were twenty-one databases in all. The caveat in all this was each database had multiple file groups and multiple files within each file group. There are plenty of attach database scripts out there, but none of them worked for me with the conditions listed above.

Since time was of the essence and this would be a massive undertaking if done manually, I wrote my own script to add to the masses. This script worked perfectly for me, and I hope you find it useful.

NOTE: I have added the option about half way down to create the scripts and have the logs rebuilt in case you only want to move the data files.

NOTE2: You should run the results to text.

~Ron

CREATE TABLE #_dba_migrate_db
(
	FileID int IDENTITY(1,1) NOT NULL,
	DBName varchar(255) NOT NULL,
	DBGroupName varchar(255) NOT NULL,
	DBFileLogicalName varchar(255) NOT NULL,
	DBFileName varchar(1000) NOT NULL,
	DBSize varchar(15) NOT NULL,
	DBMaxSize varchar(15) NOT NULL,
	DBFileGrowth varchar(15) NOT NULL,
	CONSTRAINT PK_#_dba_migrate_db PRIMARY KEY CLUSTERED
	(
		FileID
	)
);
GO

sp_msforeachdb
'
	USE [?];

	IF DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'')
		INSERT INTO #_dba_migrate_db
		(
			DBName,
			DBGroupName,
			DBFileLogicalName,
			DBFileName,
			DBSize,
			DBMaxSize,
			DBFileGrowth
		)
		SELECT
			DB_NAME(),
			ISNULL(g.groupname,''LOG'') GroupName,
			LTRIM(RTRIM(f.name)) LogicalName,
			LTRIM(RTRIM(f.filename)) FileName,
			CAST(CEILING(((CONVERT(decimal(25,2),f.size)/1024)*8)) AS varchar(15)) + ''MB'' [Size],
			CASE f.maxsize WHEN -1 THEN ''UNLIMITED'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.maxsize)/1024)*8)) AS varchar(15)) + ''MB'' END [MaxSize],
			CASE WHEN f.status & 0x100000 > 0 THEN CAST(f.growth AS varchar(15)) + ''%'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.growth)/1024)*8)) AS varchar(15)) + ''MB'' END [FileGrowth]
		FROM
			sysfiles f
		INNER JOIN
			sysfilegroups g ON
				f.groupid = g.groupid
		UNION ALL

		SELECT
			DB_NAME(),
			''LOG'' GroupName,
			LTRIM(RTRIM(f.name)) LogicalName,
			LTRIM(RTRIM(f.filename)) FileName,
			CAST(CEILING(((CONVERT(decimal(25,2),f.size)/1024)*8)) AS varchar(15)) + ''MB'' [Size],
			CASE f.maxsize WHEN -1 THEN ''UNLIMITED'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.maxsize)/1024)*8)) AS varchar(15)) + ''MB'' END [MaxSize],
			CASE WHEN f.status & 0x100000 > 0 THEN CAST(f.growth AS varchar(15)) + ''%'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.growth)/1024)*8)) AS varchar(15)) + ''MB'' END [FileGrowth]
		FROM
			sysfiles f
		WHERE
			groupid = 0
'
GO

SET NOCOUNT ON;

DECLARE
	@DBName varchar(255),
	@DBGroupName varchar(255),
	@FileID int,
	@MaxData int,
	@MaxLog int,
	@IncludeLog bit;

SET @IncludeLog = 0;

DECLARE db_cursor CURSOR FOR
	SELECT DISTINCT
		DBName
	FROM
		#_dba_migrate_db

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT 'CREATE DATABASE ' + @DBName + ' ON'

	DECLARE group_cursor CURSOR FOR
		SELECT
			MIN(FileID) FileID,
			DBGroupName
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			(DBGroupName <> 'LOG' OR @IncludeLog = 1)
		GROUP BY
			DBGroupName
		ORDER BY
			MIN(FileID);

	OPEN group_cursor;

	FETCH NEXT FROM group_cursor INTO @FileID,@DBGroupName;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT
			@MaxData = MAX(FileID)
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			DBGroupName <> 'LOG';

		SELECT
			@MaxLog = MAX(FileID)
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			DBGroupName = 'LOG';
			

		SELECT CASE WHEN @DBGroupName NOT IN ('PRIMARY','LOG') THEN 'FILEGROUP ' + @DBGroupName
			WHEN @DBGroupName = 'LOG' THEN @DBGroupName + ' ON' ELSE @DBGroupName END;

		SELECT
			+ '('
			+ CHAR(10)
			+ '	NAME = ' + DBFileLogicalName + ', '
			+ CHAR(10)
			+ '	FILENAME = ''' + DBFileName + ''','
			+ CHAR(10)
			+ '	SIZE = ' + DBSize + ','
			+ CHAR(10)
			+ '	MAXSIZE = ' + DBMaxSize + ','
			+ CHAR(10)
			+ '	FILEGROWTH = ' + DBFileGrowth
			+ CHAR(10)
			+ ')'
			+ CASE WHEN FileID IN (@MaxData,@MaxLog) THEN '' ELSE ',' END
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			DBGroupName = @DBGroupName;

		FETCH NEXT FROM group_cursor INTO @FileID,@DBGroupName;
	END

	CLOSE group_cursor;

	DEALLOCATE group_cursor;

	SELECT
		CASE WHEN @IncludeLog = 1 THEN 'FOR ATTACH;' ELSE 'FOR ATTACH_REBUILD_LOG;' END
		+ CHAR(10)
		+ 'GO'
		+ CHAR(10);

	FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;

DEALLOCATE db_cursor;

DROP TABLE #_dba_migrate_db
Advertisements

0 Responses to “Attach Database Scripts”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: