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
 

Advertisements

Shrink Databases by Nibbling a Little at a Time

When you work for a small company or have very limited drive resources, database size and growth become a big concern.  Brent Ozar makes mention to “Stop Shrinking Your Database Files. Seriously. Now.“, and in a production environment I fully concur.  BUT!!! (yes, there is always a “BUT”) There are times where it is necessary, because a development database has crept to be larger than it needs to be.  There are several reasons for this, the development that uses the enlarged DB is no longer occurring (yet the DB still needs to be online),  a developer has a bad script that is inflating the data and log files, the development team has cleared out tables of data that are not needed, etc…  I can keep going but I think you get the point.

Now in the case of the GUI DB shrink process, it requires you to go through a series of clicks, but in the end you are making one large change to the DB.  This means that if it cans out for any reason, the size never changes.  You could also script it out row by agonizing row, shrinking the file size by some number that shrinks it to where it needs to be. With concatenation this is easy, but you could wind up with 1,000s of rows of scripts just to nibble away at the file sizes.  The good news is this one gets the files shrunk in bits, but then you need to figure out where you left off.

My solution was to rewrite some scripts out there to go through calculate out how much space you currently have free.  I then take the growth rate (multiply it by two) and then figuring out the rounded size as to where the DB should be shrunk to.  The logic here is so that the DB does not need to grow as soon as you have shrunk the DB.  Once I have figured out where I am and where I want to be, then I figure out how many times the growth size goes into the difference between the two.  Hey if it grows by that amount, why not shrink it in bits by that amount (if your database growth rate is set to percentage this will not work, and you are a bad person anyway).  Then the last step before the shrinking is to remove those where there are no steps that need to occur.

Each step nibbles away the file sizes until it is at where you should be for a development system.

Look, it’s not perfect, but neither is the situation involved with needing to do this.

Here is the code:

–Auto-Shrink database based on Amount Free and Growth. This script loops through each table until the database is at its smallest pre-grown state.
USE [<DB Name>];
GO

IF OBJECT_ID(‘tempdb..#ShrinkTo’) IS NOT NULL
BEGIN
DROP TABLE [#ShrinkTo];
END;
SELECT DB_NAME() AS [DbName]
, [name] AS [FileName]
, CAST([size] / 128.0 AS int) AS [CurrentSizeMB]
, [size] / 128.0-CAST(FILEPROPERTY([name], ‘SpaceUsed’) AS int)/128.0 AS [FreeSpaceMB]
, [size] / 128.0-
([size] / 128.0-CAST(FILEPROPERTY([name], ‘SpaceUsed’) AS int)/128.0
) AS [ShrinkSize]
, CASE
WHEN [df].[growth] = 0 THEN 0
ELSE
[df].[growth] / 128
END AS [Growth]
, CAST(ROUND([size] / 128.0-
([size] / 128.0-CAST(FILEPROPERTY([name], ‘SpaceUsed’) AS int)/128.0
)+[df].[growth] / 128, -1)+[df].[growth] / 128 AS int) AS [RoundedShrinkSize]
, CASE
WHEN [df].[growth] = 0 THEN 0
ELSE CAST(
([size] / 128.0-
(CAST(ROUND(
([size] / 128.0-
([size] / 128.0-CAST(FILEPROPERTY([name], ‘SpaceUsed’) AS int)/128.0
)+[df].[growth] / 128
), -1)+
(
[df].[growth] / 128
) AS int)
)
)/
(
[df].[growth] / 128
) AS int)
END AS [Steps]
INTO [#ShrinkTo]
FROM [sys].[database_files] AS [df];

DELETE FROM [#ShrinkTo]
WHERE [#ShrinkTo].[FreeSpaceMB] < [Growth]
OR [#ShrinkTo].[Steps] < 1;

SELECT * FROM [#ShrinkTo] AS [st] ORDER BY [st].[FileName];

DECLARE @FileName varchar(150) = ”;
DECLARE @MaxFileName varchar(150) = (SELECT TOP 1 [FileName] FROM [#ShrinkTo] AS [ST] WHERE [steps] <> 0 ORDER BY [FileName] DESC);

WHILE @FileName < @MaxFileName
BEGIN
SET @FileName = (SELECT TOP 1 [FileName] FROM [#ShrinkTo] AS [ST] WHERE [steps] <> 0 AND [st].[FileName] > @FileName ORDER BY [FileName] ASC);
DECLARE @Current int = (SELECT [CurrentSizeMB] FROM [#ShrinkTo] AS [st] WHERE [FileName] = @FileName);
DECLARE @CurrentTest int = (SELECT CAST([size] / 128.0 AS int) AS [CurrentSizeMB] FROM [sys].[database_files] AS [df] WHERE [df].[name] = @FileName);
DECLARE @Shrink int = (SELECT [RoundedShrinkSize] FROM [#ShrinkTo] AS [st] WHERE [FileName] = @FileName);
DECLARE @Growth int = (SELECT [Growth] FROM [#ShrinkTo] AS [st] WHERE [FileName] = @FileName);
DECLARE @Steps int = (SELECT [Steps] FROM [#ShrinkTo] AS [st] WHERE [FileName] = @FileName);
DECLARE @Script nvarchar(500) = ”;
IF @Steps > 20 AND @Steps <= 100
BEGIN
SET @Growth = CAST(@Growth * 2 AS int);
SET @Steps = CAST(@Steps / 2 AS int);
END;
IF @Steps > 100
BEGIN
SET @Growth = CAST(@Growth * 4 AS int);
SET @Steps = CAST(@Steps / 4 AS int);
END;
WHILE @Shrink < @Current
BEGIN
SELECT @FileName AS [FileName]
, @Current AS [CurrentSize]
, @Shrink AS [ShrinkingTo]
, @Growth AS [AmountShrunk]
, @Steps AS [Step];
SET @Script = ‘DBCC SHRINKFILE (”’+@FileName+”’ , ‘+CAST(@Current AS varchar(20))+’)’;

EXECUTE (@Script);
SET @Current =
@Current – @Growth;
SET @Steps = @Steps – 1;

END;
END;

IF OBJECT_ID(‘tempdb..#ShrinkTo’) IS NOT NULL
BEGIN
DROP TABLE [#ShrinkTo];
END;

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.  🙂

 

I love Log Shipping

I have found one thing in SQL I do very, very, very well…  Can’t you tell by the title?  There are times when to use and not to though…

Very good times to use it:

  • Migrating a “live” database from one server to another.
  • Moving a cluster from one domain to another while keeping the primary site up.
  • Non-automatic failover DRs
  • Setting up an initial DR system in house or across a WAN.
  • Creating a read-only server where the data is allowed to stale for long periods of time.
  • During setup of the SQL server pair for the HA Availability Group (especially on larger databases) or any other major setup (replication) before switching this DR structure “on”.
  • Where you have two sites where you need to go through some sort of FTP connection to transfer the data.
  • Where you are limited on budget and need to have a warm DR server up, but you are limited to only one DR SQL server. You can easily set it up for a many to one scenario.
  • You are limited to SQL Server Standard only.
  • Where another copy of the database already exists on the server, but you need the one from the other server for DR purposes. Example: we have DBA_MaintenanceDB on every SQL server (every server).  It is meant for the DBA’s to store data in (like old backup information from MSDB, SP_AskBrent results, database inventories, and all sort of other junk out there), but B.M.T. the company had a DBA that thought he would write SPs in there to run against the primary production database.  Well he left and never transferred the knowledge.  Until we get that mess cleaned up and sorted out, we need to have a copy of the DBA_MaintenanceDB on our DR server, so it on the secondary server it is named LS_Prod_DBA_MaintenanceDB  so we still have all of those SPs.

Bad times to use it:

  • For a reporting server that needs to be kept as up to date as possible.
  • For a reporting server where you need different securities on the database than in production
  • You are horrible at remembering the difference between “With Recovery” and “With NoRecovery” (I raise my hand to this, and it’s annoying)
  • Your RPO and RTO for DR is less than 5 minutes each.
  • Remember this: anything that happens in the database on the production server will occur to the log shipped copy on the other server after all of the TRN files have been restored.  So if you need another copy of the DB, yet something has to be different, don’t use log shipping

I have been watching the Logshipping BrentOzar blog like a hawk now, just because it’s fun to try to help out others in the understanding of how Log Shipping works.  In doing this I have learned more than I knew before, but still learn more every time someone asks another question.

I will dig into details on this more as the days go on.

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.