When I started as an official DBA four years ago, I was taught of the wonders of some of the big names in the SQL community. Brent Ozar and Kendra Little (and the rest of their crew) from http://www.BrentOzar.com, Pinal Dave and his blogs through http://blog.SQLAuthority.com, Denny Cherry at http://itknowledgeexchange.techtarget.com/profile/mrdenny/, and those that I did not mention who I have referred to by a simple Google query.  But the one I was not introduced to until I started working at my current job last year was the one that I think I have used the most since starting here.  That man is Ola Hallengren and his site https://ola.hallengren.com.  It used to be huge chore to setup and re-set up backups through maintenance plans. Ola’s backup script (https://ola.hallengren.com/sql-server-backup.html) became my bread and butter for normal MS SQL backups.  But as my last post pointed out we moved to EMCs Data Domain.

Their initial scripts are horrible.  There is no logic to them.  We were back to setting up individual backup scripts for each database.  If another database was added we would have to set it up again.  If a database was dropped we’d have to remove the script from the job so that we wouldn’t get the error emails. Oh, I could just go on…

The other DBA I work with wanted to take Ola’s scripts and modify it but ran into time constraints (he is the much stronger developer than I am).  So he asked me to hack at it.  AND HACK I DID!!!

It’s ugly, but it works.

It is a stored procedure that I put into our DBA_MaintenanceDB (the DBA’s playhouse DB on every (and I mean every) SQL server that we control).  Here it is in all of it’s imperfection:

USE [DBA_MaintenanceDB]
GO
CREATE PROCEDURE [dbo].[SP_DDDatabaseBackup]
@Print varchar(10) = 'PRINT', --PRINT (prints out the full backup script including the XP_CmdShell, good from running from CMD line) ,CMD (gives you just the SQL command that can be run from the query window),EXEC (Actually executes the script)
@Location varchar (3),-- = '', ' we have two physical units in our two locations.
@Databases nvarchar(max), --ALL_DATABASES,SYSTEM_DATABASES,USER_DATABASES,Individual Database names comma seperated
@Type varchar (50) = 'FULL',
@Stripes varchar(2) = 4, --Up to 8: this is similar to splitting your backups into several files.  It does move faster up to a point.
@Date varchar(50) = ''

AS

DECLARE @Host varchar (50)
DECLARE @User varchar (50)
DECLARE @Path varchar (50)
IF @Location = ''
	BEGIN
		SET @Host = ''
		SET @User = ''
		SET @Path = ''
	END
ELSE
	BEGIN
		SET @Host = ''
		SET @User = ''
		SET @Path = ''
	END

IF @Date = ''
BEGIN
SET @Date = CONVERT(VARCHAR(50), DATEADD(DD,15,GETDATE()),101)+' 00:00:00'--++CONVERT(VARCHAR(50), DATEADD(DD,14,GETDATE()),108)
END
DECLARE @BackupScript varchar(max)
DECLARE @Version numeric(18,10) = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
DECLARE @ServerName varchar (50)
DECLARE @Cluster nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @CurrentDBID int
DECLARE @CurrentDatabaseName nvarchar(max)
DECLARE @CurrentDatabaseNameFS nvarchar(max)

DECLARE @tmpDatabases TABLE (ID int,
                            DatabaseName nvarchar(max),
                            DatabaseNameFS nvarchar(max),
                            DatabaseType nvarchar(max),
                            Selected bit,
                            Completed bit,
                            PRIMARY KEY(Selected, Completed, ID))
DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
                            DatabaseType nvarchar(max),
                            Selected bit)

----  Collect Data
-- Get the cluster name --
IF @Version >= 11
	BEGIN
		SET @Cluster = (SELECT REPLACE(cluster_name,'\','$') FROM sys.dm_hadr_cluster)
	END

---- Select databases

SET @Databases = REPLACE(@Databases, ', ', ',');

WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
(
SELECT 1 AS StartPosition,
        ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
        SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem
WHERE @Databases IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
        ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
        SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem
FROM Databases1
WHERE EndPosition < LEN(@Databases) + 1
),
Databases2 (DatabaseItem, Selected) AS
(
SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
        CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Databases1
),
Databases3 (DatabaseItem, DatabaseType, Selected) AS
(
SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
        CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
        Selected
FROM Databases2
),
Databases4 (DatabaseName, DatabaseType, Selected) AS
(
SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
        DatabaseType,
        Selected
FROM Databases3
)
INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
SELECT DatabaseName,
        DatabaseType,
        Selected
FROM Databases4
OPTION (MAXRECURSION 0)

INSERT INTO @tmpDatabases (ID,DatabaseName, DatabaseNameFS, DatabaseType, Selected, Completed)
SELECT [database_id] AS ID,
		[name] AS DatabaseName,
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') AS DatabaseNameFS,
        CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
        0 AS Selected,
        0 AS Completed
FROM sys.databases [db]
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL
AND [state] = 0 AND [db].[is_in_standby] = 0
ORDER BY [name] ASC

UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
WHERE SelectedDatabases.Selected = 1

UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
WHERE SelectedDatabases.Selected = 0

---- Check database names
SET @ErrorMessage = ''
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
FROM @tmpDatabases
WHERE Selected = 1
AND DatabaseNameFS = ''
ORDER BY DatabaseName ASC
IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMessage = 'The names of the following databases are not supported: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '
--RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
--SET @Error = @@ERROR
END

SET @ErrorMessage = ''
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
FROM @tmpDatabases
WHERE UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases GROUP BY UPPER(DatabaseNameFS) HAVING COUNT(*) > 1)
AND UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases WHERE Selected = 1)
AND DatabaseNameFS <> ''
ORDER BY DatabaseName ASC
OPTION (RECOMPILE)
IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMessage = 'The names of the following databases are not unique in the file system: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '
--RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
--SET @Error = @@ERROR
END
--SELECT * FROM @tmpDatabases
--SELECT * FROM @SelectedDatabases
--SELECT @ErrorMessage

WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)
BEGIN
    SELECT TOP 1 @CurrentDBID = ID,
                 @CurrentDatabaseName = DatabaseName,
                 @CurrentDatabaseNameFS = DatabaseNameFS
    FROM @tmpDatabases
    WHERE Selected = 1
    AND Completed = 0
    ORDER BY ID ASC

	--Select @CurrentDBID, @CurrentDatabaseName, @CurrentDatabaseNameFS
	IF @Version >= 11
		BEGIN
			IF (SELECT sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)) = 1 AND (SELECT [database_name] FROM [sys].[availability_databases_cluster]) = @CurrentDatabaseName
			BEGIN
				SET @ServerName = @Cluster
				SET @BackupScript = (
				SELECT
					'EXEC xp_cmdshell ''ddbmsqlsv.exe '
				+ '-c '+ @ServerName + ' '
				+ '-l '+ @Type +' '
				+ '-S ' + @Stripes + ' '
				--+ '-u '
				+ '-N "'+ @ServerName + '_' + d.name + '_'+ @Type +'_Backup" '
				+ '-b "Daily ' + @Type + ' backups" '
				+ '-y "' + @date + '" '
				+ '-a "NSR_DFA_SI=TRUE" '
				+ '-a "NSR_DFA_SI_USE_DD=TRUE" '
				+ '-a "NSR_DFA_SI_DD_HOST=' + @host + '" '
				+ '-a "NSR_DFA_SI_DD_USER=' + @User + '" '
				+ '-a "NSR_DFA_SI_DEVICE_PATH=' + @Path + '"'
				+ ' "MSSQL:'+  [name] +'"'''
				FROM sys.databases d WHERE d.[database_id] = @CurrentDBID)
				IF @Print = 'EXEC'
					BEGIN
						EXECUTE (@BackupScript)
						PRINT @CurrentDatabaseName + ' has been backed up.'
					END
				ELSE
				IF @Print = 'CMD'
					BEGIN
						PRINT REPLACE(REPLACE(@BackupScript,'''',''),'EXEC xp_cmdshell ','')
					END
				ELSE
					BEGIN
						PRINT @BackupScript
					END
			END
		ELSE
		IF (SELECT sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)) = 0 AND (SELECT [database_name] FROM [sys].[availability_databases_cluster]) = @CurrentDatabaseName

			BEGIN
				--SELECT sys.[fn_hadr_backup_is_preferred_replica]('IASPRODIMS_Reduced')
				PRINT @CurrentDatabaseName + ' has been skipped because it is not the Secondary server.'
			END
		ELSE
			BEGIN
				SET @ServerName = REPLACE(@@SERVERNAME,'\','$')
				SET @BackupScript = (
				SELECT
					'EXEC xp_cmdshell ''ddbmsqlsv.exe '
				+ '-c '+ @ServerName + ' '
				+ '-l '+ @Type +' '
				+ '-S ' + @Stripes + ' '
				--+ '-u '
				+ '-N "'+ @ServerName + '_' + d.name + '_'+ @Type +'_Backup" '
				+ '-b "Daily '+ @Type +' backups" '
				+ '-y "' + @date + '" '
				+ '-a "NSR_DFA_SI=TRUE" '
				+ '-a "NSR_DFA_SI_USE_DD=TRUE" '
				+ '-a "NSR_DFA_SI_DD_HOST=' + @host + '" '
				+ '-a "NSR_DFA_SI_DD_USER=' + @User + '" '
				+ '-a "NSR_DFA_SI_DEVICE_PATH=' + @Path + '"'
				+ ' "MSSQL:'+  [name] +'"'''
				FROM sys.databases d WHERE d.[database_id] = @CurrentDBID)
				IF @Print = 'EXEC'
					BEGIN
						EXECUTE (@BackupScript)
						PRINT @CurrentDatabaseName + ' has been backed up.'
					END
				ELSE
				IF @Print = 'CMD'
					BEGIN
						PRINT REPLACE(REPLACE(@BackupScript,'''',''),'EXEC xp_cmdshell ','')
					END
				ELSE
					BEGIN
						PRINT @BackupScript
					END
			END
		END
	ELSE
		BEGIN
			SET @ServerName = REPLACE(@@SERVERNAME,'\','$')
			SET @BackupScript = (
			SELECT
				'EXEC xp_cmdshell ''ddbmsqlsv.exe '
			+ '-c ' + @ServerName + ' '
			+ '-l ' + @Type + ' '
			+ '-S ' + @Stripes + ' '
			--+ '-u '
			+ '-N "'+ @ServerName + '_' + d.name + '_'+ @Type +'_Backup" '
			+ '-b "Daily '+ @Type +' backups" '
			+ '-y "' + @date + '" '
			+ '-a "NSR_DFA_SI=TRUE" '
			+ '-a "NSR_DFA_SI_USE_DD=TRUE" '
			+ '-a "NSR_DFA_SI_DD_HOST=' + @host + '" '
			+ '-a "NSR_DFA_SI_DD_USER=' + @User + '" '
			+ '-a "NSR_DFA_SI_DEVICE_PATH=' + @Path + '"'
			+ ' "MSSQL:'+  [name] +'"'''
			FROM sys.databases d WHERE d.[database_id] = @CurrentDBID)
			IF @Print = 'EXEC'
					BEGIN
						EXECUTE (@BackupScript)
						PRINT @CurrentDatabaseName + ' has been backed up.'
					END
				ELSE
				IF @Print = 'CMD'
					BEGIN
						PRINT REPLACE(REPLACE(@BackupScript,'''',''),'EXEC xp_cmdshell ','')
					END
				ELSE
					BEGIN
						PRINT @BackupScript
					END
		END

	UPDATE @tmpDatabases
    SET Completed = 1
    WHERE Selected = 1
    AND Completed = 0
    AND ID = @CurrentDBID
END

I hope one day Ola comes along sees this and somehow incorporates it fully into his scripts, because I tried and failed…  Maybe I will try later on in my career… 🙂

Advertisements

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