Posts Tagged 'SQL Server'



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

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

Helpful SQL Server Date Functions

One of my common duties is to create and/or update reports in SSRS (SQL Server Reporting Services). A large number of these reports ran from very unclean data. To compensate for this, the original developers handled the data in a variety of inconsistent manners, mostly around dates. Although this may have gotten the job done originally, it does nothing for the quality of the report.

As these reports were updated, we began using standard date functions to improve development time and report quality.


/* First Day of the Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

/* Last Day of the Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0))

/* First Day of Next Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0)

/* Last Day of Next Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,2,GETDATE())),0))

/* First Day of Last Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())),0)

/* Last Day of Last Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

/* First Day of the Year */
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

/* Last Day of the Year */
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,DATEADD(yy,1,GETDATE())),0))

/* First Day of the Quarter */
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)

/* Last Day of the Quarter */
SELECT DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(qq,1,GETDATE())),0))

/* First Monday of the Month */
SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,6 - DATEPART(DAY,GETDATE()),GETDATE())),0)

~Ron

Cannot Connect to Named Instance of SQL Server behind a Firewall

I installed SQL Server 2008 Express on all of my workstations at home this weekend to have a test environment to play with before my 70-453. I had never installed SQL Server across a workgroup before, so it was a learning experience.

Two of the workstations were Windows XP Professional and one was Windows Vista Home Premium. The installs were all fairly uneventful. Being SQL Express intalls, i used the SQLEXPRESS named instance. I opened up TCP port 1433 in Windows Firewall and enabled remote connections.

All set, right? No.

OK. Let’s check everything again.

  1. SQL Server Browser service running? Check
  2. Remote connections enabled? Check
  3. Exception for TCP port 1433? Check

All set, right? No.

Crap.

After a half hour of searching and five minutes of cursing I discover the SQL Server Browser service uses UDP port 1434. I added the exception and everything is hunky-dory.

~Ron