Test Database Restores from EMC DDBMA without waiting hours to select the database.

So if you ever used the DDBMA tool you will find out the GUI tool is very clunky and convoluted.  One of the most difficult things to do is to test the database backups.  Within the tool you need to open SSMS in Admin mode, open the EMC tool in the toolbar, click on the Restore tab, open the appropriate bucket, select the database that you want to restore, and then wait 5 to 30 minutes for the tool to put back together all of the restore points.  (Can you tell I have done this enough times?)

Well I finally constructed a stored procedure that handles all of this in a simple (yet complex) stored procedure.  I will explain the breakdown in a followup post.
USE [master];
GO

--CREATE TABLE [DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations]
-- (
-- [RowNumber] int IDENTITY(1, 1)
--, [Instance] [varchar](max) NOT NULL
--, [DBName] [varchar](max) NOT NULL
--, [Original_FileName] [varchar](max) NOT NULL
--, [Name] [varchar](max) NOT NULL
--, [Truncated_FileName] [varchar](max) NOT NULL
--, [Location] AS (CASE WHEN [Instance] LIKE '%-PA-%' THEN 'PA' ELSE 'NJ' END) PERSISTED
-- )
--ON [PRIMARY];
IF OBJECT_ID('usp_DatabaseRestoreTest') IS NOT NULL
BEGIN
DROP PROCEDURE [usp_DatabaseRestoreTest];
END;
GO

CREATE PROCEDURE [usp_DatabaseRestoreTest](
@Instance varchar(100)
, @OrigDBName varchar(1000)
, @NewDBName varchar(1000)
, @DriveLetter char(2)
, @Debug int)
AS
BEGIN
SET NOCOUNT ON;

TRUNCATE TABLE [DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations];

INSERT INTO [DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations]
([Instance]
, [DBName]
, [Original_FileName]
, [Name]
, [Truncated_FileName]
)
SELECT *
FROM [].[DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations];

INSERT INTO [DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations]
([Instance]
, [DBName]
, [Original_FileName]
, [Name]
, [Truncated_FileName]
)
SELECT *
FROM [].[DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations];

INSERT INTO [DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations]
([Instance]
, [DBName]
, [Original_FileName]
, [Name]
, [Truncated_FileName]
)
SELECT *
FROM [].[DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations];

INSERT INTO [DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations]
([Instance]
, [DBName]
, [Original_FileName]
, [Name]
, [Truncated_FileName]
)
SELECT *
FROM [].[DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations];

DECLARE @FileLocation varchar(max) = @DriveLetter+'\'+@Instance;

DECLARE @Restore varchar(max) = '';

SELECT @Restore = CASE
WHEN @Restore = '' THEN ''''''''''+[Name]+'''''''''='''''''''+@FileLocation+CASE
WHEN LEN([Truncated_FileName]) > 50 THEN LEFT([Truncated_FileName],
LEN([Truncated_FileName]) -
(
50 + LEN([RowNumber]) + 1
))+'_'+CAST([RowNumber] AS varchar(10))+RIGHT([Truncated_FileName], CHARINDEX('.', REVERSE([Truncated_FileName])))
ELSE [Truncated_FileName]
END+''''''''''
ELSE @Restore+COALESCE(', '''''''''+[Name]+'''''''''='''''''''+@FileLocation+CASE
WHEN LEN([Truncated_FileName]) > 50 THEN LEFT([Truncated_FileName],
LEN([Truncated_FileName]) -
(
50 + LEN([RowNumber]) + 1
))+'_'+CAST([RowNumber] AS varchar(10))+RIGHT([Truncated_FileName], CHARINDEX('.', REVERSE([Truncated_FileName])))
ELSE [Truncated_FileName]
END+'''''''''', '')
END
FROM [DBA_MaintenanceDB].[dbo].[dba_DB_File_Locations] AS [ddfl]
WHERE [instance] = @Instance
AND [dbname] = @OrigDBName;

DECLARE @Host varchar(50) = '';
DECLARE @User varchar(50) = '';
DECLARE @Path varchar(50) = '';
IF @Debug = 1
BEGIN
SELECT @Host AS [Host];
SELECT @User AS [User];
SELECT @Path AS [Path];
END;
IF @Instance LIKE '%\%'
BEGIN
SET @OrigDBName =
(
SELECT 'MSSQL'+REPLACE(RIGHT(@Instance, CHARINDEX('\', REVERSE(@Instance))), '\', '$')+':'+@OrigDBName
);
END;
ELSE
BEGIN
SET @OrigDBName =
(
SELECT 'MSSQL:'+@OrigDBName
);
END;
IF @Debug = 1
BEGIN
SELECT @OrigDBName AS [OrigDBName_Corrected];
END;
SET @Instance = '';
IF @Debug = 1
BEGIN
SELECT @Instance AS [Instance];
END;
DECLARE @Script nvarchar(max) = '';
DECLARE @Date varchar(30) =
(
SELECT CONVERT(varchar(30), GETDATE() + 1, 101)
);
--SELECT @Date
DECLARE @CommandScript varchar(500) = 'if not exist "'+@FileLocation+'" mkdir '+@FileLocation;
EXECUTE [xp_cmdshell]
@CommandScript;

SET @Script = 'DECLARE @returnCode int
EXEC @returnCode = dbo.emc_run_restore '' -c '+@Instance+'.local -C" '+@Restore+' " -f -t "'+@Date+' 05:30:00 AM" -S normal -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+'" -d "MSSQL:'+@NewDBName+'" "'+@OrigDBName+'" ''
IF @returnCode 0
BEGIN
RAISERROR (''Fail!'', 16, 1)
END;';
IF @Debug = 1
BEGIN
SELECT @Script AS [Script];
END;
ELSE
BEGIN
EXECUTE (@Script);
END;
DECLARE @TestScript nvarchar(max) = 'GO
USE ['+@OrigDBName+']
SELECT [t].[name], [c].[name] FROM [sys].[tables] t INNER JOIN [sys].[columns] c ON [c].[object_id] = [t].[object_id]';
IF @Debug = 1
BEGIN
SELECT @TestScript AS [TestScript];
END;
ELSE
BEGIN
EXECUTE (@TestScript);
END;

END;
GO

EXECUTE [usp_DatabaseRestoreTest]
@Instance = 'Instance'
, @OrigDBName = 'OrigDBName'
, @NewDBName = 'NewDBName'
, @DriveLetter = 'DriveLetter'
, @Debug = 0;

GO
 

A Good Log Shipping Email Report

As I have stated before I pride myself as being an accidental DBA.  So my T-SQL skills are not perfect.  One of the things that I do pride myself with is setting up a better way to monitor log shipping.

If you have any experience with log shipping, you know that the default monitoring tool that is setup as part of log shipping just tells you that something is wrong and no details at all.  So here you get emails every 5 minutes that state that something is wrong and you need to go dig trying to find the issue.  I hate that!

So I wrote a stored procedure that actually does the same thing, but gives me a chart that looks like the report on the monitoring server instead showing those databases where the log shipping is broken (in red none the less).  With slight tweaks you can set up another stored procedure to send you a daily report showing where your log shipping is every day.  Once you have the stored procedures working you just need to setup the jobs on your monitoring SQL server.

Remember this is a DR critical setup, you do not set it and forget it!!!

Here it is in it’s imperfection:

USE [dba_MaintenanceDB]
GO
CREATE PROCEDURE [dbo].[sp_LSAlert]
AS
    BEGIN
	SET NOCOUNT ON;
	DECLARE @MxCnt INT, @Cnt INT
	DECLARE @JobID VARBINARY(MAX)
	DECLARE @RunnableJobs INT
	DECLARE @Owner VARCHAR(20)
	DECLARE @vRecipients AS VARCHAR(MAX)
	DECLARE @vCopy_Recipients AS VARCHAR(MAX)
	DECLARE @vXML_String AS NVARCHAR(MAX)
	DECLARE @vBody AS NVARCHAR(MAX)
	DECLARE @vSQL_String AS NVARCHAR(MAX)
	DECLARE @vSubject AS NVARCHAR(255)
	DECLARE @UTCRunTime DATETIME = GETUTCDATE();
	SET @vRecipients = '	<list of recipients seperated by emi-colons>'
	SET @vCopy_Recipients = ''
	SET @vSubject = 'LogShipping Alert: ' + @@SERVERNAME

        IF OBJECT_ID('tempdb.dbo.#enum_job') IS NOT NULL
            BEGIN
                DROP TABLE dbo.#enum_job
            END
        IF OBJECT_ID('tempdb.dbo.#LS_Jobs') IS NOT NULL
            BEGIN
                DROP TABLE dbo.#LS_Jobs
            END

		INSERT INTO dbo.dba_LogShippingLatency
		        ( RunUTCDateTime
		        , Issue
		        , Primary_Server
		        , Primary_Database
		        , Secondary_Server
		        , Secondary_Database
		        , Last_Backup
		        , Backup_Threshold
		        , Last_Backup_LocalDate
		        , Last_Backup_File
		        , Last_Copied_LocalDate
		        , Last_Copied_File
		        , Restore_Threshold
		        , Last_Restore
		        , Last_Restore_LocalDate
		        , Last_Restore_File
		        , Last_Restore_Latency )

        SELECT
			@UTCRunTime AS RunUTCDateTime
            , CASE WHEN [sec].restore_threshold < DATEDIFF(MINUTE,
                                                             [sec].last_restored_date,
                                                             GETDATE()) OR
                          [sec].restore_threshold <= [sec].last_restored_latency OR
                          [pri].last_backup_date < DATEDIFF(MINUTE,
                                                            [pri].last_backup_date,
                                                            GETDATE())
                     THEN 'Issue'
                     ELSE ''
                END AS [Issue]
              , [pri].primary_server
              , [pri].primary_database
              , [sec].secondary_server
              , [sec].secondary_database
              , DATEDIFF(MINUTE, [pri].last_backup_date, GETDATE()) AS Last_Backup
              , [pri].backup_threshold
              , [pri].last_backup_date
              , CASE WHEN [pri].last_backup_file LIKE '\\<part of primary location name>%'
                     THEN REPLACE([pri].last_backup_file,
                                  '<primary file location>\' +
                                  [pri].primary_server + '\' +
                                  [pri].primary_database + '\Log\', '')
                     ELSE REPLACE([pri].last_backup_file,
                                  '<secondary file location>\' +
                                  [pri].primary_server + '\' +
                                  [pri].primary_database + '\Log\', '')
                END AS Last_Backup_File
              , CASE WHEN [sec].last_copied_date IS NULL THEN ''
                     ELSE CAST([sec].last_copied_date AS VARCHAR(50))
                END AS Last_Copied_Date
              , CASE WHEN [sec].last_copied_file IS NULL THEN ''
                     ELSE REPLACE([sec].last_copied_file,
                                  '<secondary file location>' +
                                  [pri].primary_server + '\' +
                                  [pri].primary_database + '\Log\', '')
                END AS Last_Copied_File
              , [sec].restore_threshold
              , DATEDIFF(MINUTE, [sec].last_restored_date, GETDATE()) AS Last_Restore
              , [sec].last_restored_date
              , CASE WHEN [sec].last_restored_file IS NULL THEN ''
                     ELSE REPLACE([sec].last_restored_file,
                                  '<secondary file location>' +
                                  [sec].secondary_server + '\' +
                                  [sec].secondary_database + '\Log\', '')
                END AS last_restored_file
              , [sec].last_restored_latency
            FROM
                msdb.[dbo].[log_shipping_monitor_primary] [pri]
                INNER JOIN msdb.[dbo].[log_shipping_monitor_secondary] [sec]
                    ON [pri].primary_database = [sec].primary_database AND
                       [pri].primary_server = [sec].primary_server
            WHERE
                [sec].restore_threshold < DATEDIFF(MINUTE,
                                                   [sec].last_restored_date,
                                                   GETDATE()) OR
                [sec].restore_threshold <= [sec].last_restored_latency OR
                [pri].last_backup_date < DATEDIFF(MINUTE,
                                                  [pri].last_backup_date,
                                                  GETDATE())

/****************************************************************
Setup Email body
Note: Because we get a lot of transactions around 5am to 8am, we allow our production latency to get to 120 minutes.
****************************************************************/
        IF (
             SELECT COUNT (*)
				FROM dbo.dba_LogShippingLatency AS dLSL
				WHERE
					dLSL.RunUTCDateTime = @UTCRunTime
					AND dLSL.Issue <> ''
					AND (
							(
								dLSL.Primary_Database = '<Server_Name>'
								AND CAST(dLSL.RunUTCDateTime AS TIME) BETWEEN '10:00:00' AND '13:00:00' -- between 5am and 8am
								AND dLSL.Last_Restore_Latency > 120
							)
						OR
                        	(
								dLSL.Primary_Database = '<Server_Name>'
								AND NOT (CAST(dLSL.RunUTCDateTime AS TIME) BETWEEN '10:00:00' AND '13:00:00') -- NOT between 5am and 8am
								AND dLSL.Last_Restore_Latency > dLSL.Restore_Threshold
							)
						OR
							(
								dLSL.Primary_Database <> '<Server_Name>'
								AND dLSL.Last_Restore_Latency > dLSL.Restore_Threshold
							)
						)

           ) > 0
            BEGIN;
			        SET @vXML_String = CONVERT (NVARCHAR(MAX), (
			 SELECT
					''
				  , dLSL.Issue AS 'td'
				  , ''
				  , dLSL.Primary_Server AS 'td'
				  , ''
				  , dLSL.Primary_Database AS 'td'
				  , ''
				  , dLSL.Secondary_Server AS 'td'
				  , ''
				  , dLSL.Secondary_Database AS 'td'
				  , ''
				  , dLSL.Last_Backup AS 'td'
				  , ''
				  , dLSL.Backup_Threshold AS 'td'
				  , ''
				  , dLSL.Last_Backup_LocalDate AS 'td'
				  , ''
				  , dLSL.last_copied_Localdate AS 'td'
				  , ''
				  , dLSL.Last_Copied_File AS 'td'
				  , ''
				  , dLSL.restore_threshold AS 'td'
				  , ''
				  , dLSL.Last_Restore AS 'td'
				  , ''
				  , dLSL.last_restore_Localdate AS 'td'
				  , ''
				  , dLSL.Last_Restore_File AS 'td'
				  , ''
				  , dLSL.last_restore_latency AS 'td'
				FROM
					dbo.dba_LogShippingLatency AS dLSL
				WHERE
					dLSL.RunUTCDateTime = @UTCRunTime
				ORDER BY
					dLSL.primary_server
				  , dLSL.primary_database
				  , dLSL.Secondary_Server
				  , dLSL.Secondary_Database
		   FOR
			 XML PATH('tr')
		   ))
        SELECT
                @vXML_String
        SET @vBody = '
<h3><center>LogShipping Alerts</center></h3>
<center>
<table border=1 cellpadding=2>
<tr>
<th>Issue</th>
<th>Primary_Server</th>
<th>Primary_Database</th>
<th>Secondary_Server</th>
<th>Secondary_Database</th>
<th>Last_Backup</th>
<th>Backup_Threshold</th>
<th>Last_Backup_Date</th>
<th>Last_Copied_Date</th>
<th>Last_Copied_File</th>
<th>Restore_Threshold</th>
<th>Last_Restore</th>
<th>Last_Restore_Date</th>
<th>Last_Restore_File</th>
<th>Last_Restore_Latency</th>
</tr>
'
        SET @vBody = @vBody + @vXML_String + '</table>
</center>'

        IF OBJECT_ID('tempdb.dbo.#enum_job') IS NOT NULL
            BEGIN
                DROP TABLE dbo.#enum_job
            END
        IF OBJECT_ID('tempdb.dbo.#LS_Jobs') IS NOT NULL
            BEGIN
                DROP TABLE dbo.#LS_Jobs
            END
----------------------------------------
--	Variable Update: Finalize @vBody Variable Contents
----------------------------------------
        SET @vBody = '
		<html>
			<body>
<style type="text/css">
				table {font-size:8.0pt;font-family:Arial;text-align:left;}
				tr {text-align:left;}
			</style>

	' + @vBody + '
			</body>
		</html>
	'
        SET @vBody = REPLACE(@vBody, '
<tr>
<td>Issue</td>
',
                             '
<tr bgcolor="IndianRed">
<td align="center">Issue</td>
')
        SET @vBody = REPLACE(@vBody, '
<td>right_align', '
<td align="right">')
        SET @vBody = REPLACE(@vBody, '
<td>center_align', '
<td align="center">')
----------------------------------------
--	sp_send_dbmail: Deliver Results / Notification To End User(s)
----------------------------------------
        EXEC msdb.dbo.sp_send_dbmail
	--@profile_name =  'SystemEmail',
            @recipients = @vRecipients
          , @importance = 'High'
          , @copy_recipients = @vCopy_Recipients
          , @subject = @vSubject
          , @body = @vBody
          , @body_format = 'HTML'
       END;
    END
GO

With tweaks and modifications you should be able to get this working for you as well.  🙂

 

EMC DDBA Version 2

Okay, it seems like I am slamming EMC on their Data Domain Boost Agent since I began this blog.  Oh wait I started three days ago and am talking about something I just learned.

We were led by our noses to change from our old methods of backups (the other DBA and I were kicking and screaming horribly along the way).  I admit it we were bad.  Hey, change something that you have been using for 15 years (started with SQL 2000 remember).  Even backing up to tapes (yes, I was a backup administrator at one point) I always backed up to the bak file (or bck is what I started with) first and then backed them up to tape or external storage.  We knew the bak’s were always (well almost always) good.  I can rely on them.  Well now we are switching to the EMC Data Domain system.  It’s got a lot of neat bells and whistles that you can read about on their site, but the one thing we were sold on was the EMC Data Domain Boost Agent.  This tool was advertised to make our lives easier and faster.  They admitted straight out of the gate that their GUI tool for SMSS was lacking and that we would have to write scripts to restore database to other servers, so we were expecting that.

After figuring out how to write a script (yesterdays post) to make our life easier to use this tool with, I moved to our 2TB backup.  We were scared, especially after reading Denny Cherry’s written experience with a 6TB DB.

I began running the scripts and the first backup took 13 hours. OUCH!! But we had been forewarned that the first backup took longer than normal.  Okay I can live with that.
Day 2: 9 hours and 21 minutes…  Okay, must be settling in.
Day 3: 9 hours and 24 minutes…  Uhm, is this normal?
Day 4: 13 hours and 41 minutes…  Whoa, something has got to be wrong
Day 5: Squirrel!!!! Yes guilty of being sidetracked at this point with more pressing issues… 🙂

Day 20: 20 hours…  Our snap process (we use this on our replicated copy (this was our 2TB test server) to grab DEV environments) could not occur because of this.

Now the backups are creating an issue.  Now the emails really started to fly around that these backups were taking too long.  EMC worked with us but gave us a bad taste in our mouth on how we were to handle this.

“Install the Beta version of DDBA 2.0.”

“Wait you want us to install a Beta copy of a program we are having issues with!”

Doesn’t sound like the smartest thing to do, does it?  Well after some hemming and hawing (and more sidetracking) we finally sat down and got a copy of their 2.0 product.

Night and Day

Yep, in installing Version 2.0 Beta, I went from averaging 13 hours to 5 hours (just like we were before we switched to their tool in the first place)

Also, we could now successfully see our backups from the other servers that we couldn’t before.  This allows me to use their GUI to restore a backup from the SQL1 bucket in the Data Domain to the server SQL2.

Now if only they could fix that pesky UAC issue (you need to start SSMS in Admin mode in order to restore a database using their tool even if you are a Server Admin and DB System Admin).  But otherwise this is a 100% turn around and can’t wait till it is officially released at the end of the month.  🙂

EMC Data Domain backup script based off of Ola Hallengren’s SQL Server Backup script

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 &gt;= 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 &lt; 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],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'&lt;',''),'&gt;',''),'|',''),' ','') 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] &lt;&gt; '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 &gt; 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(*) &gt; 1)
AND UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases WHERE Selected = 1)
AND DatabaseNameFS &lt;&gt; ''
ORDER BY DatabaseName ASC
OPTION (RECOMPILE)
IF @@ROWCOUNT &gt; 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 &gt;= 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… 🙂

First interesting post I can think of: Nifty way of getting backup times.

Just recently we switched from our old fashioned, yet reliable MS SQL Server backups to EMCs DataDomain backups. In doing this our 2 TB backups went from being 4-8 hours to being greater than 13 hours (had to kill them twice when they were greater than 19 hours).  One of the things that we needed to do to show EMC the issue was to show the times before and after the switch took place.  Thank god for MSDB.  We built this script to find the data quickly and efficiently:

SELECT
	, 'FULL' AS [Type_Of_Backup]
	, CASE WHEN [SBS].[name] = 'DDBMMSSQL' THEN 'DataDomain' ELSE 'SQLBackups' END AS [Backup_Location]
	, [SBS].database_name
	, [SBS].[backup_start_date]
	, [SBS].[backup_finish_date]
	, RIGHT( '00' + CAST(DATEDIFF(HOUR,[SBS].[backup_start_date], [SBS].[backup_finish_date]) AS VARCHAR(3)), 2)
		+ ':' +
		CASE WHEN RIGHT( '00' + CAST(DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date])
						- (DATEDIFF(Hour,[SBS].[backup_start_date], [SBS].[backup_finish_date]) * 60) AS VARCHAR(3)), 2) < 0 THEN '00' ELSE
						RIGHT( '00' + CAST(DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date])
						- (DATEDIFF(Hour,[SBS].[backup_start_date], [SBS].[backup_finish_date]) * 60) AS VARCHAR(3)), 2) END
	AS RunTime
	, DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date]) AS RunTimeMinutes
FROM msdb.dbo.backupset AS [SBS]
WHERE
	[SBS].database_name = '<;database to measure>;'
	AND [SBS].type='D' --This means the full backups.
	AND([SBS].user_name='<;Service Account>;'OR [SBS].user_name='<;SQL Agent Account>;')

Forgive my programming skills since I pride myself as being an accidental DBA and come from the hardware side and not the development side.  I am still learning and will always make room for improvement.