Archive for August, 2009

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

Search SQL Agent Job Steps

I’ve never really that about how enigmatic SQL Agent jobs can be because I am usually the one creating them. Anyways, a developer was asking if I knew a way to search the T-SQL within a job, so here it is …

DECLARE
      @SearchString varchar(255),
      @IsEnabled bit;
 
SET @SearchString = 'warp'; -- Enter Search String Here. Leave Blank for All
SET @IsEnabled = 1; -- 0 = Disabled, 1 = Enabled, 2 = All
 
SELECT
      j.Name JobName,
      j.Description JobDescription,
      js.step_id StepID,
      js.step_name StepName,
      js.database_name DatabaseName,
      js.command StepCommand  
FROM
      msdb..sysjobs j
INNER JOIN
      msdb..sysjobsteps js ON
            j.job_id = js.job_id
WHERE
      (j.enabled = @IsEnabled OR @IsEnabled = 2) AND
      js.command LIKE '%' + @SearchString + '%'
ORDER BY
      j.Name,
      js.step_id;

~Ron

Change Default Directories for SSMS 2005

I needed to change the default directories for SSMS (SQL Server Management Studio) 2005, and it took me a bit Googling to figure it out, so I thought I would post it here in case I forget. 🙂

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM]

The default value is “%USERPROFILE%\My Documents\SQL Server Management Studio Express\Projects\” in case you really bork things up and need to reset them.

Hopefully this helps you, too.

~Ron

Debugging Stored Prcedures in Visual Studio

I was speaking with a developer friend of mine the other day and the topic of debugging stored procedures came up. My friend was unaware that this was even possible, so I thought that I would share.

In SQL2000 debugging was as simple as right clicking on a stored procedure in Query Analyzer and selecting Debug from the context menu. This was a simple, yet effective method, but is very limited in its capabilities. That feature was removed in SSMS (SQL Server Management Studio).

For SQL 2005 and above, this can be done in Visual Studio. In server explorer create a data connection. Drill down to the stored procedure that you want to debug and click “Step Into Stored Procedure.” From here you can step though the stored procedure.

Alternatively, you can right click on the stored procedure and select “Open” from the context menu. From there you can set breakpoints, change SQL blocks, execute SQL blocks and step into the stored procedure.

My two favorite features of debugging stored procedures in Visual Studio are the ability to see and change variables as you step through the code and the ability to see the managed code when you open a CLR procedure.

For a step by step explanation (with pictures) you can check out SQL Team‘s article, which explains the steps better than I.

Happy debugging!

~Ron