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

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