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
 

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;