/
Benchmark Database Refresh Instructions

Benchmark Database Refresh Instructions

Benchmark TEST Database Refresh Instructions

 

 

Below are the instructions on how to get the TEST refresh script going.  This script should work on all current SQL Servers versions 2008 and up.

 

Open the script with “SQL Server Management Studio” (SSMS) connected to the server that the Benchmark TEST and LIVE databases are installed on.

This script will only work if both TEST and LIVE are on the same server and instance. 

 

Fill in the names of the LIVE and TEST databases into the scripts variables “@LiveBenchmarkDB” and “@TestBenchmarkDB” then “Execute” the script. 

 

 

Expand the “SQL Server Agent” and then the “Jobs” folder.  You may need to “Refresh” the “Jobs” folder for the newly added job made by the script to show up.

 

Run the job by right clicking on the “Benchmark Refresh Test” job and select “Start Job at Step…”

 

 

Start the job

 

Wait for the job to finish.

 

This could take a while depending on the size of the databases because this job does both a backup and a refresh.

 

If you encounter any errors contact Pioneer Technology Group and we will help you work through them.

 



Benchmark Test Database Refresh SQL Job

USE [msdb]

GO

/**

Editor : Caleb Hammel

Created : 04/09/2020

Edit : 02/07/23 - Added COPY_ONLY to the BACKUP

07/27/23 - Fixed issues with network locations for backups and having more than one data\log file.

Description :

Benchmark Refresh Test w/ Live Data. Works with SQL Server 2008 and up

Preserves Test Tables for tblSetting, tblService, tblServiceSetting, tblPaymentProvider, tblPaymentProviderSettings, tblQuery, tblQueryField

Installation : Input the names for the LIVE and TEST Benchmark Databases into @LiveBenchmarkDB and @TestBenchmarkDB then run the script to create the SQL Job.

Execution : Run the SQL Job named "Benchmark Refresh Test" from the "SQL Server Agent" > Jobs.

**/

DECLARE @LiveBenchmarkDB VARCHAR(100)

DECLARE @TestBenchmarkDB VARCHAR(100)

SET @LiveBenchmarkDB = 'BenchmarkLive'

SET @TestBenchmarkDB = 'BenchmarkTest'

-- Do not change anything below

DECLARE @DataFolder VARCHAR(500)

DECLARE @LogFolder VARCHAR(500)

DECLARE @BackupFolder VARCHAR(500)

DECLARE @PhsyDevName VARCHAR(500)

--Get Data and Log file folders\locations

SET @DataFolder = CONVERT(VARCHAR(500),SERVERPROPERTY('instancedefaultdatapath'))

SET @LogFolder = CONVERT(VARCHAR(500),SERVERPROPERTY('instancedefaultlogpath'))

--Get Backup folder location

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupFolder OUTPUT, 'no_output'

--Get Drive or Network Share

SET @PhsyDevName = LEFT(@BackupFolder,CHARINDEX('\',@BackupFolder,3))

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Benchmark Refresh Test',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

DECLARE @usecommand VARCHAR(5000)

SET @usecommand = 'USE MASTER

GO

IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME=''BENCHMARKTESTSETTING'')

DROP DATABASE BENCHMARKTESTSETTING

GO

CREATE DATABASE [BENCHMARKTestSetting]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N''BENCHMARKTestSetting'', FILENAME = N''' + @DataFolder + 'BENCHMARKTestSetting.mdf'' , SIZE = 5000KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N''BENCHMARKTestSetting_log'', FILENAME = N''' + @LogFolder + 'BENCHMARKTestSetting_Log.ldf'' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

ALTER DATABASE [BENCHMARKTestSetting] SET COMPATIBILITY_LEVEL = 100

GO

ALTER DATABASE [BENCHMARKTestSetting] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET ANSI_NULLS OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET ANSI_PADDING OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET ARITHABORT OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [BENCHMARKTestSetting] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [BENCHMARKTestSetting] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE [BENCHMARKTestSetting] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET DISABLE_BROKER

GO

ALTER DATABASE [BENCHMARKTestSetting] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [BENCHMARKTestSetting] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [BENCHMARKTestSetting] SET READ_WRITE

GO

ALTER DATABASE [BENCHMARKTestSetting] SET RECOVERY SIMPLE

GO

ALTER DATABASE [BENCHMARKTestSetting] SET MULTI_USER

GO

ALTER DATABASE [BENCHMARKTestSetting] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [BENCHMARKTestSetting] SET TARGET_RECOVERY_TIME = 0 SECONDS

GO

USE [BENCHMARKTestSetting]

GO

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N''PRIMARY'') ALTER DATABASE [BENCHMARKTestSetting] MODIFY FILEGROUP [PRIMARY] DEFAULT

GO'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Settings Backup DB',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Service Tables',

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'USE [BENCHMARKTestSetting]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblSetting](

[SettingID] [int] IDENTITY(1,1) NOT NULL,

[SettingGroup] [varchar](50) NULL,

[SettingKey] [varchar](50) NULL,

[SettingValue] [varchar](max) NULL,

[SettingDescription] [varchar](500) NULL,

[SettingCategory] [varchar](50) NULL CONSTRAINT [DF_tblSetting_SettingCategory] DEFAULT (''General''),

[DefaultValue] [varchar](max) NULL,

[SettingDataType] [varchar](50) NULL,

[CreateByUserID] [int] NULL CONSTRAINT [DF_tblSetting_CreateByUserID_1] DEFAULT ((0)),

[CreateDate] [datetime] NULL CONSTRAINT [DF_tblSetting_CreateDate_1] DEFAULT (getdate()),

[ModifyByUserID] [int] NULL CONSTRAINT [DF_tblSetting_ModifyByUserID_1] DEFAULT ((0)),

[ModifyDate] [datetime] NULL CONSTRAINT [DF_tblSetting_ModifyDate_1] DEFAULT (getdate()))

GO

USE [BENCHMARKTestSetting]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblService](

[ServiceID] [int] IDENTITY(1,1) NOT NULL,

[ServiceName] [varchar](100) NULL,

[ServiceDescription] [varchar](255) NULL,

[Active] [bit] NULL,

[CreateByUserID] [int] NULL,

[CreateDate] [datetime] NULL,

[ModifyByUserID] [int] NULL,

[ModifyDate] [datetime] NULL)

GO

USE [BENCHMARKTestSetting]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblServiceSetting](

[ServiceSettingID] [int] IDENTITY(1,1) NOT NULL,

[ServiceID] [int] NULL,

[SettingName] [varchar](50) NULL,

[SettingValue] [varchar](1000) NULL,

[CreateByUserID] [int] NULL,

[CreateDate] [datetime] NULL,

[ModifyByUserID] [int] NULL,

[ModifyDate] [datetime] NULL)

GO

USE [BENCHMARKTestSetting]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblPaymentProvider](

[PaymentProviderID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](250) NULL,

[ProviderType] [varchar](250) NULL,

[Endpoint] [varchar](250) NULL,

[CreateByUserID] [int] NULL,

[CreateDate] [datetime] NULL,

[ModifyByUserID] [int] NULL,

[ModifyDate] [datetime] NULL)

GO

USE [BENCHMARKTestSetting]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblPaymentProviderSetting](

[PaymentProviderSettingID] [int] IDENTITY(1,1) NOT NULL,

[PaymentProviderID] [int] NULL,

[SettingName] [varchar](100) NULL,

[SettingValue] [varchar](250) NULL,

[CreateByUserID] [int] NULL,

[CreateDate] [datetime] NULL,

[ModifyByUserID] [int] NULL,

[ModifyDate] [datetime] NULL)

GO',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Reports Tables',

@step_id=3,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'USE [BENCHMARKTestSetting]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblQuery](

[QueryID] [int] IDENTITY(1,1) NOT NULL,

[QueryKey] [varchar](50) NULL,

[Description] [varchar](255) NULL,

[LongDescription] [varchar](1000) NULL,

[QueryType] [varchar](50) NULL,

[QueryText] [varchar](max) NULL,

[QueryGroup] [varchar](50) NULL,

[ProgramControl] [varchar](255) NULL,

[AccessRightName] [varchar](50) NULL,

[DashboardRightName] [varchar](50) NULL,

[Printer] [varchar](50) NULL,

[HideOnMainMenu] [bit] NULL CONSTRAINT [DF_tblQuery_HideOnMainMenu] DEFAULT ((0)),

[AutoFinalize] [bit] NULL CONSTRAINT [DF_tblQuery_AutoFinalize] DEFAULT ((1)),

[DocketCodeID] [int] NULL CONSTRAINT [DF_tblQuery_DocketCodeID] DEFAULT ((0)),

[AutoDocketImage] [bit] NULL CONSTRAINT [DF_tblQuery_AutoDocketImage] DEFAULT ((0)),

[UseSearchGrid] [bit] NULL CONSTRAINT [DF_tblQuery_UseSearchGrid] DEFAULT ((0)),

[SpecialUseCode] [smallint] NULL,

[WordForm] [bit] NULL CONSTRAINT [DF_tblQuery_WordForm] DEFAULT ((0)),

[WordAllowEditing] [bit] NULL CONSTRAINT [DF_tblQuery_WordAllowEditing] DEFAULT ((0)),

[AddAsNew] [bit] NOT NULL CONSTRAINT [DF_tblQuery_Preserve] DEFAULT ((0)),

[UpdateQry] [bit] NOT NULL CONSTRAINT [DF_tblQuery_UpdateQry] DEFAULT ((0)),

[ICPReport] [bit] NULL,

[DisplayType] [int] NULL,

[FinalizeType] [int] NULL,

[TruncateOnImport] [bit] NULL CONSTRAINT [DF_tblQuery_TruncateOnImport] DEFAULT ((0)),

[RunCaseListID] [int] NULL CONSTRAINT [DF_tblQuery_RunCaseListID] DEFAULT ((0)),

[DefaultDirectory] [varchar](255) NULL,

[DefaultExtension] [varchar](50) NULL,

[DefaultFilter] [varchar](100) NULL,

[DefaultDelimiter] [varchar](50) NULL,

[DefaultTextQualifier] [varchar](50) NULL,

[DefaultProcessedDirectory] [varchar](255) NULL,

[SkipRowCount] [int] NULL CONSTRAINT [DF_tblQuery_SkipRowCount] DEFAULT ((0)),

[DisplayOrder] [int] NULL,

[ShowOnWeb] [bit] NULL,

[ExportFieldNames] [varchar](1000) NULL,

[CreateByUserID] [int] NULL CONSTRAINT [DF_tblQueries_CreateByUserID] DEFAULT ((0)),

[CreateDate] [datetime] NULL CONSTRAINT [DF_tblQueries_CreateDate] DEFAULT (getdate()),

[ModifyByUserID] [int] NULL CONSTRAINT [DF_tblQueries_ModifyByUserID] DEFAULT ((0)),

[ModifyDate] [datetime] NULL,

[RefreshAfterListAction] [bit] NULL,

[DelayPrintingUntilAfterPost] [bit] NULL CONSTRAINT [DF_tblQuery_DelayPrintingUntilAfterPost] DEFAULT ((0)),

[PurgeSequenceID] [int] NULL,

[PurgeDispositionFormat] [varchar](500) NULL,

[PurgeStorageContainerID] [int] NULL)

GO

USE [BENCHMARKTestSetting]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblQueryField](

[QueryFieldID] [int] IDENTITY(1,1) NOT NULL,

[QueryID] [int] NULL CONSTRAINT [DF_tblQueryField_QueryID] DEFAULT ((0)),

[FieldName] [varchar](50) NULL,

[FieldQuestion] [varchar](100) NULL,

[FieldType] [varchar](50) NULL,

[FieldObject] [varchar](50) NULL,

[ObjectFieldName] [varchar](50) NULL,

[Required] [bit] NULL CONSTRAINT [DF_tblQueryField_Required] DEFAULT ((0)),

[ComboListValue] [varchar](500) NULL,

[FieldDefault] [varchar](100) NULL,

[DashboardDefault] [varchar](100) NULL,

[WildCardSearch] [smallint] NULL CONSTRAINT [DF_tblQueryField_WildCardSearch] DEFAULT ((0)),

[AutoDocketSource] [bit] NULL CONSTRAINT [DF_tblQueryField_AutoDocketSource] DEFAULT ((0)),

[MultiPrintQueryFieldID] [int] NULL,

[MultiPrintGlobalProperty] [varchar](50) NULL,

[MultiPrintConditionalCompare] [varchar](max) NULL,

[MultiPrintDisplayField] [varchar](max) NULL,

[CreateByUserID] [int] NULL CONSTRAINT [DF_tblQueryField_CreateByUserID] DEFAULT ((0)),

[CreateDate] [datetime] NULL CONSTRAINT [DF_tblQueryField_CreateDate] DEFAULT (getdate()),

[ModifyByUserID] [int] NULL CONSTRAINT [DF_tblQueryField_ModifyByUserID] DEFAULT ((0)),

[ModifyDate] [datetime] NULL,

[MultiPrintOption] [smallint] NULL)',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE [BENCHMARKTESTSETTING]

GO

SET IDENTITY_INSERT TBLSETTING ON

INSERT INTO [DBO].[TBLSETTING]

([SETTINGID]

,[SETTINGGROUP]

,[SETTINGKEY]

,[SETTINGVALUE]

,[SETTINGDESCRIPTION]

,[SETTINGCATEGORY]

,[DEFAULTVALUE]

,[SETTINGDATATYPE]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

S.SETTINGID

,S.SETTINGGROUP

,S.SETTINGKEY

,S.SETTINGVALUE

,S.SETTINGDESCRIPTION

,S.SETTINGCATEGORY

,S.DEFAULTVALUE

,S.SETTINGDATATYPE

,S.CREATEBYUSERID

,S.CREATEDATE

,S.MODIFYBYUSERID

,S.MODIFYDATE

FROM

' + @TestBenchmarkDB + '..TBLSETTING S

SET IDENTITY_INSERT TBLSETTING OFF

GO

USE [BENCHMARKTESTSETTING]

GO

SET IDENTITY_INSERT TBLSERVICE ON

INSERT INTO [DBO].[TBLSERVICE]

([SERVICEID]

,[SERVICENAME]

,[SERVICEDESCRIPTION]

,[ACTIVE]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

S.SERVICEID

,S.SERVICENAME

,S.SERVICEDESCRIPTION

,S.ACTIVE

,S.CREATEBYUSERID

,S.CREATEDATE

,S.MODIFYBYUSERID

,S.MODIFYDATE

FROM

' + @TestBenchmarkDB + '..TBLSERVICE S

SET IDENTITY_INSERT TBLSERVICE OFF

GO

USE [BENCHMARKTESTSETTING]

GO

SET IDENTITY_INSERT TBLSERVICESETTING ON

INSERT INTO [DBO].[TBLSERVICESETTING]

([SERVICESETTINGID]

,[SERVICEID]

,[SETTINGNAME]

,[SETTINGVALUE]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

SS.SERVICESETTINGID

,SS.SERVICEID

,SS.SETTINGNAME

,SS.SETTINGVALUE

,SS.CREATEBYUSERID

,SS.CREATEDATE

,SS.MODIFYBYUSERID

,SS.MODIFYDATE

FROM

' + @TestBenchmarkDB + '..TBLSERVICESETTING SS

SET IDENTITY_INSERT TBLSERVICESETTING OFF

GO

USE [BENCHMARKTESTSETTING]

GO

SET IDENTITY_INSERT TBLPAYMENTPROVIDER ON

INSERT INTO [DBO].[TBLPAYMENTPROVIDER]

(PAYMENTPROVIDERID

,[NAME]

,[PROVIDERTYPE]

,[ENDPOINT]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

PP.PAYMENTPROVIDERID

,PP.NAME

,PP.PROVIDERTYPE

,PP.ENDPOINT

,PP.CREATEBYUSERID

,PP.CREATEDATE

,PP.MODIFYBYUSERID

,PP.MODIFYDATE

FROM

' + @TestBenchmarkDB + '..TBLPAYMENTPROVIDER PP

SET IDENTITY_INSERT TBLPAYMENTPROVIDER OFF

GO

USE [BENCHMARKTESTSETTING]

GO

SET IDENTITY_INSERT TBLPAYMENTPROVIDERSETTING ON

INSERT INTO [DBO].[TBLPAYMENTPROVIDERSETTING]

([PAYMENTPROVIDERSETTINGID]

,[PAYMENTPROVIDERID]

,[SETTINGNAME]

,[SETTINGVALUE]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

PPS.PAYMENTPROVIDERSETTINGID

,PPS.PAYMENTPROVIDERID

,PPS.SETTINGNAME

,PPS.SETTINGVALUE

,PPS.CREATEBYUSERID

,PPS.CREATEDATE

,PPS.MODIFYBYUSERID

,PPS.MODIFYDATE

FROM

' + @TestBenchmarkDB + '..TBLPAYMENTPROVIDERSETTING PPS

SET IDENTITY_INSERT TBLPAYMENTPROVIDERSETTING OFF'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Setting Tables',

@step_id=4,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE [BENCHMARKTESTSETTING]

GO

SET IDENTITY_INSERT TBLQUERY ON

INSERT INTO [DBO].[TBLQUERY]

([QUERYID]

,[QUERYKEY]

,[DESCRIPTION]

,[LONGDESCRIPTION]

,[QUERYTYPE]

,[QUERYTEXT]

,[QUERYGROUP]

,[PROGRAMCONTROL]

,[ACCESSRIGHTNAME]

,[DASHBOARDRIGHTNAME]

,[PRINTER]

,[HIDEONMAINMENU]

,[AUTOFINALIZE]

,[DOCKETCODEID]

,[AUTODOCKETIMAGE]

,[USESEARCHGRID]

,[SPECIALUSECODE]

,[WORDFORM]

,[WORDALLOWEDITING]

,[ADDASNEW]

,[UPDATEQRY]

,[ICPREPORT]

,[DISPLAYTYPE]

,[FINALIZETYPE]

,[TRUNCATEONIMPORT]

,[RUNCASELISTID]

,[DEFAULTDIRECTORY]

,[DEFAULTEXTENSION]

,[DEFAULTFILTER]

,[DEFAULTDELIMITER]

,[DEFAULTTEXTQUALIFIER]

,[DEFAULTPROCESSEDDIRECTORY]

,[SKIPROWCOUNT]

,[DISPLAYORDER]

,[SHOWONWEB]

,[EXPORTFIELDNAMES]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE]

,[REFRESHAFTERLISTACTION]

,[DELAYPRINTINGUNTILAFTERPOST]

,[PURGESEQUENCEID]

,[PURGEDISPOSITIONFORMAT]

,[PURGESTORAGECONTAINERID])

SELECT

Q.QUERYID

,Q.QUERYKEY

,Q.DESCRIPTION

,Q.LONGDESCRIPTION

,Q.QUERYTYPE

,Q.QUERYTEXT

,Q.QUERYGROUP

,Q.PROGRAMCONTROL

,Q.ACCESSRIGHTNAME

,Q.DASHBOARDRIGHTNAME

,Q.PRINTER

,Q.HIDEONMAINMENU

,Q.AUTOFINALIZE

,Q.DOCKETCODEID

,Q.AUTODOCKETIMAGE

,Q.USESEARCHGRID

,Q.SPECIALUSECODE

,Q.WORDFORM

,Q.WORDALLOWEDITING

,Q.ADDASNEW

,Q.UPDATEQRY

,Q.ICPREPORT

,Q.DISPLAYTYPE

,Q.FINALIZETYPE

,Q.TRUNCATEONIMPORT

,Q.RUNCASELISTID

,Q.DEFAULTDIRECTORY

,Q.DEFAULTEXTENSION

,Q.DEFAULTFILTER

,Q.DEFAULTDELIMITER

,Q.DEFAULTTEXTQUALIFIER

,Q.DEFAULTPROCESSEDDIRECTORY

,Q.SKIPROWCOUNT

,Q.DISPLAYORDER

,Q.SHOWONWEB

,Q.EXPORTFIELDNAMES

,Q.CREATEBYUSERID

,Q.CREATEDATE

,Q.MODIFYBYUSERID

,Q.MODIFYDATE

,Q.REFRESHAFTERLISTACTION

,Q.DELAYPRINTINGUNTILAFTERPOST

,Q.PURGESEQUENCEID

,Q.PURGEDISPOSITIONFORMAT

,Q.PURGESTORAGECONTAINERID

FROM

' + @TestBenchmarkDB + '..TBLQUERY Q

SET IDENTITY_INSERT TBLQUERY OFF

GO

USE [BENCHMARKTESTSETTING]

GO

SET IDENTITY_INSERT TBLQUERYFIELD ON

INSERT INTO [DBO].[TBLQUERYFIELD]

([QUERYFIELDID]

,[QUERYID]

,[FIELDNAME]

,[FIELDQUESTION]

,[FIELDTYPE]

,[FIELDOBJECT]

,[OBJECTFIELDNAME]

,[REQUIRED]

,[COMBOLISTVALUE]

,[FIELDDEFAULT]

,[DASHBOARDDEFAULT]

,[WILDCARDSEARCH]

,[AUTODOCKETSOURCE]

,[MULTIPRINTQUERYFIELDID]

,[MULTIPRINTGLOBALPROPERTY]

,[MULTIPRINTCONDITIONALCOMPARE]

,[MULTIPRINTDISPLAYFIELD]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE]

,[MULTIPRINTOPTION])

SELECT

QF.QUERYFIELDID

,QF.QUERYID

,QF.FIELDNAME

,QF.FIELDQUESTION

,QF.FIELDTYPE

,QF.FIELDOBJECT

,QF.OBJECTFIELDNAME

,QF.REQUIRED

,QF.COMBOLISTVALUE

,QF.FIELDDEFAULT

,QF.DASHBOARDDEFAULT

,QF.WILDCARDSEARCH

,QF.AUTODOCKETSOURCE

,QF.MULTIPRINTQUERYFIELDID

,QF.MULTIPRINTGLOBALPROPERTY

,QF.MULTIPRINTCONDITIONALCOMPARE

,QF.MULTIPRINTDISPLAYFIELD

,QF.CREATEBYUSERID

,QF.CREATEDATE

,QF.MODIFYBYUSERID

,QF.MODIFYDATE

,QF.MULTIPRINTOPTION

FROM

' + @TestBenchmarkDB + '..TBLQUERYFIELD QF

SET IDENTITY_INSERT TBLQUERYFIELD OFF

GO'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Report Tables',

@step_id=5,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'DECLARE @PATH VARCHAR(100)

SET @PATH = ''' + @BackupFolder + 'BENCHMARKTEST-SS-''+FORMAT(SYSDATETIME(), ''yyyy.MM.dd'')+''.BAK''

BACKUP DATABASE [' + @TestBenchmarkDB + '] TO DISK = @PATH

WITH COPY_ONLY

,NOFORMAT

,NOINIT

,NAME = ''' + @TestBenchmarkDB + '-FULL DATABASE BACKUP''

,SKIP

,NOREWIND

,NOUNLOAD

,COMPRESSION

,STATS = 10

GO'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Test In Case',

@step_id=6,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE MASTER

GO

DECLARE @KILL VARCHAR(8000) = '''';

SELECT @KILL = @KILL + ''KILL '' + CONVERT(VARCHAR(5), SPID) + '';''

FROM MASTER..SYSPROCESSES

WHERE DBID = DB_ID(''' + @TestBenchmarkDB + ''')

EXEC(@KILL);

GO'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Kill Test Connection',

@step_id=7,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE [MASTER]

--Get SQL Version for for column differences in the file list from backup files

DECLARE @SQLVersion VARCHAR(25) = @@version

SET @SQLVersion = RIGHT(@SQLVersion,4)

--Get backup path information

DECLARE @PATH NVARCHAR(500) = (

SELECT TOP 1 B.PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A

INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY B ON A.MEDIA_SET_ID = B.MEDIA_SET_ID

WHERE A.DATABASE_NAME LIKE ''' + @LiveBenchmarkDB + '''

AND A.TYPE = ''D''

AND PHYSICAL_DEVICE_NAME LIKE ''' + @PhsyDevName + '%''

ORDER BY A.BACKUP_FINISH_DATE DESC)

--Create temp table #fileListTable to store list of Data and Log files in the backup file

CREATE TABLE #fileListTable (

[LogicalName] NVARCHAR(128),

[PhysicalName] NVARCHAR(260),

[Type] CHAR(1),

[FileGroupName] NVARCHAR(128),

[Size] NUMERIC(20,0),

[MaxSize] NUMERIC(20,0),

[FileID] BIGINT,

[CreateLSN] NUMERIC(25,0),

[DropLSN] NUMERIC(25,0),

[UniqueID] UNIQUEIDENTIFIER,

[ReadOnlyLSN] NUMERIC(25,0),

[ReadWriteLSN] NUMERIC(25,0),

[BackupSizeInBytes] BIGINT,

[SourceBlockSize] INT,

[FileGroupID] INT,

[LogGroupGUID] UNIQUEIDENTIFIER,

[DifferentialBaseLSN] NUMERIC(25,0),

[DifferentialBaseGUID] UNIQUEIDENTIFIER,

[IsReadOnly] BIT,

[IsPresent] BIT,

[TDEThumbprint] VARBINARY(32)

)

--Add column for sql version differences

IF(@SQLVersion > 2015)

BEGIN

ALTER TABLE #fileListTable

ADD [SnapshotURL] NVARCHAR(360)

END

--Add Data and Log files from Backup file to temp table #fileListTable

INSERT INTO #fileListTable EXEC(''RESTORE FILELISTONLY FROM DISK = '''''' + @PATH + '''''''')

--Create Initial Restore Script

DECLARE @sql nvarchar(max) = ''RESTORE DATABASE ' + @TestBenchmarkDB + ' FROM DISK = '''''' + @PATH + '''''' WITH REPLACE, STATS = 5, ''

--Add Data and Log files to Restore Script

SELECT @sql = @sql + CHAR(13) + '' MOVE '''''' + LogicalName + '''''' TO ''''' + @DataFolder + @TestBenchmarkDB + '.'' + RIGHT(PhysicalName,LEN(PhysicalName) - CHARINDEX(''.'',PhysicalName)) + '''''',''

FROM #fileListTable WHERE IsPresent = 1

--Remove Trailing comma from Restore Script

SET @sql = SUBSTRING(@sql,1,LEN(@sql)-1)

EXEC (@sql)

GO'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore Live Over Test',

@step_id=8,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE ' + @TestBenchmarkDB + '

DELETE TBLSETTING

DELETE TBLSERVICE

DELETE TBLSERVICESETTING

DELETE TBLPAYMENTPROVIDER

DELETE TBLPAYMENTPROVIDERSETTING'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Live Settings from Test',

@step_id=9,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE ' + @TestBenchmarkDB + '

DELETE TBLQUERY

DELETE TBLQUERYFIELD'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Report Settings from Test',

@step_id=10,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE ' + @TestBenchmarkDB + '

TRUNCATE TABLE TBLSERVICELOG

TRUNCATE TABLE TBLERECORD

TRUNCATE TABLE TBLAUDIT

TRUNCATE TABLE TBLWEBLOG

TRUNCATE TABLE TBLEFILELOG'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate Extra Tables',

@step_id=11,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE [' + @TestBenchmarkDB + ']

GO

SET IDENTITY_INSERT TBLSETTING ON

INSERT INTO [DBO].[TBLSETTING]

([SETTINGID]

,[SETTINGGROUP]

,[SETTINGKEY]

,[SETTINGVALUE]

,[SETTINGDESCRIPTION]

,[SETTINGCATEGORY]

,[DEFAULTVALUE]

,[SETTINGDATATYPE]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

S.SETTINGID

,S.SETTINGGROUP

,S.SETTINGKEY

,S.SETTINGVALUE

,S.SETTINGDESCRIPTION

,S.SETTINGCATEGORY

,S.DEFAULTVALUE

,S.SETTINGDATATYPE

,S.CREATEBYUSERID

,S.CREATEDATE

,S.MODIFYBYUSERID

,S.MODIFYDATE

FROM

BENCHMARKTESTSETTING..TBLSETTING S

SET IDENTITY_INSERT TBLSETTING OFF

GO

USE [' + @TestBenchmarkDB + ']

GO

SET IDENTITY_INSERT TBLSERVICE ON

INSERT INTO [DBO].[TBLSERVICE]

([SERVICEID]

,[SERVICENAME]

,[SERVICEDESCRIPTION]

,[ACTIVE]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

S.SERVICEID

,S.SERVICENAME

,S.SERVICEDESCRIPTION

,S.ACTIVE

,S.CREATEBYUSERID

,S.CREATEDATE

,S.MODIFYBYUSERID

,S.MODIFYDATE

FROM

BENCHMARKTESTSETTING..TBLSERVICE S

SET IDENTITY_INSERT TBLSERVICE OFF

GO

USE [' + @TestBenchmarkDB + ']

GO

SET IDENTITY_INSERT TBLSERVICESETTING ON

INSERT INTO [DBO].[TBLSERVICESETTING]

([SERVICESETTINGID]

,[SERVICEID]

,[SETTINGNAME]

,[SETTINGVALUE]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

SS.SERVICESETTINGID

,SS.SERVICEID

,SS.SETTINGNAME

,SS.SETTINGVALUE

,SS.CREATEBYUSERID

,SS.CREATEDATE

,SS.MODIFYBYUSERID

,SS.MODIFYDATE

FROM

BENCHMARKTESTSETTING..TBLSERVICESETTING SS

SET IDENTITY_INSERT TBLSERVICESETTING OFF

GO

USE [' + @TestBenchmarkDB + ']

GO

SET IDENTITY_INSERT TBLPAYMENTPROVIDER ON

INSERT INTO [DBO].[TBLPAYMENTPROVIDER]

(PAYMENTPROVIDERID

,[NAME]

,[PROVIDERTYPE]

,[ENDPOINT]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

PP.PAYMENTPROVIDERID

,PP.NAME

,PP.PROVIDERTYPE

,PP.ENDPOINT

,PP.CREATEBYUSERID

,PP.CREATEDATE

,PP.MODIFYBYUSERID

,PP.MODIFYDATE

FROM

BENCHMARKTESTSETTING..TBLPAYMENTPROVIDER PP

SET IDENTITY_INSERT TBLPAYMENTPROVIDER OFF

GO

USE [' + @TestBenchmarkDB + ']

GO

SET IDENTITY_INSERT TBLPAYMENTPROVIDERSETTING ON

INSERT INTO [DBO].[TBLPAYMENTPROVIDERSETTING]

([PAYMENTPROVIDERSETTINGID]

,[PAYMENTPROVIDERID]

,[SETTINGNAME]

,[SETTINGVALUE]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE])

SELECT

PPS.PAYMENTPROVIDERSETTINGID

,PPS.PAYMENTPROVIDERID

,PPS.SETTINGNAME

,PPS.SETTINGVALUE

,PPS.CREATEBYUSERID

,PPS.CREATEDATE

,PPS.MODIFYBYUSERID

,PPS.MODIFYDATE

FROM

BENCHMARKTESTSETTING..TBLPAYMENTPROVIDERSETTING PPS

SET IDENTITY_INSERT TBLPAYMENTPROVIDERSETTING OFF'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Backup Settings into Test',

@step_id=12,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'USE [' + @TestBenchmarkDB + ']

GO

SET IDENTITY_INSERT TBLQUERY ON

INSERT INTO [DBO].[TBLQUERY]

([QUERYID]

,[QUERYKEY]

,[DESCRIPTION]

,[LONGDESCRIPTION]

,[QUERYTYPE]

,[QUERYTEXT]

,[QUERYGROUP]

,[PROGRAMCONTROL]

,[ACCESSRIGHTNAME]

,[DASHBOARDRIGHTNAME]

,[PRINTER]

,[HIDEONMAINMENU]

,[AUTOFINALIZE]

,[DOCKETCODEID]

,[AUTODOCKETIMAGE]

,[USESEARCHGRID]

,[SPECIALUSECODE]

,[WORDFORM]

,[WORDALLOWEDITING]

,[ADDASNEW]

,[UPDATEQRY]

,[ICPREPORT]

,[DISPLAYTYPE]

,[FINALIZETYPE]

,[TRUNCATEONIMPORT]

,[RUNCASELISTID]

,[DEFAULTDIRECTORY]

,[DEFAULTEXTENSION]

,[DEFAULTFILTER]

,[DEFAULTDELIMITER]

,[DEFAULTTEXTQUALIFIER]

,[DEFAULTPROCESSEDDIRECTORY]

,[SKIPROWCOUNT]

,[DISPLAYORDER]

,[SHOWONWEB]

,[EXPORTFIELDNAMES]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE]

,[REFRESHAFTERLISTACTION]

,[DELAYPRINTINGUNTILAFTERPOST]

,[PURGESEQUENCEID]

,[PURGEDISPOSITIONFORMAT]

,[PURGESTORAGECONTAINERID])

SELECT

Q.QUERYID

,Q.QUERYKEY

,Q.DESCRIPTION

,Q.LONGDESCRIPTION

,Q.QUERYTYPE

,Q.QUERYTEXT

,Q.QUERYGROUP

,Q.PROGRAMCONTROL

,Q.ACCESSRIGHTNAME

,Q.DASHBOARDRIGHTNAME

,Q.PRINTER

,Q.HIDEONMAINMENU

,Q.AUTOFINALIZE

,Q.DOCKETCODEID

,Q.AUTODOCKETIMAGE

,Q.USESEARCHGRID

,Q.SPECIALUSECODE

,Q.WORDFORM

,Q.WORDALLOWEDITING

,Q.ADDASNEW

,Q.UPDATEQRY

,Q.ICPREPORT

,Q.DISPLAYTYPE

,Q.FINALIZETYPE

,Q.TRUNCATEONIMPORT

,Q.RUNCASELISTID

,Q.DEFAULTDIRECTORY

,Q.DEFAULTEXTENSION

,Q.DEFAULTFILTER

,Q.DEFAULTDELIMITER

,Q.DEFAULTTEXTQUALIFIER

,Q.DEFAULTPROCESSEDDIRECTORY

,Q.SKIPROWCOUNT

,Q.DISPLAYORDER

,Q.SHOWONWEB

,Q.EXPORTFIELDNAMES

,Q.CREATEBYUSERID

,Q.CREATEDATE

,Q.MODIFYBYUSERID

,Q.MODIFYDATE

,Q.REFRESHAFTERLISTACTION

,Q.DELAYPRINTINGUNTILAFTERPOST

,Q.PURGESEQUENCEID

,Q.PURGEDISPOSITIONFORMAT

,Q.PURGESTORAGECONTAINERID

FROM

BENCHMARKTESTSETTING..TBLQUERY Q

SET IDENTITY_INSERT TBLQUERY OFF

GO

USE [' + @TestBenchmarkDB + ']

GO

SET IDENTITY_INSERT TBLQUERYFIELD ON

INSERT INTO [DBO].[TBLQUERYFIELD]

([QUERYFIELDID]

,[QUERYID]

,[FIELDNAME]

,[FIELDQUESTION]

,[FIELDTYPE]

,[FIELDOBJECT]

,[OBJECTFIELDNAME]

,[REQUIRED]

,[COMBOLISTVALUE]

,[FIELDDEFAULT]

,[DASHBOARDDEFAULT]

,[WILDCARDSEARCH]

,[AUTODOCKETSOURCE]

,[MULTIPRINTQUERYFIELDID]

,[MULTIPRINTGLOBALPROPERTY]

,[MULTIPRINTCONDITIONALCOMPARE]

,[MULTIPRINTDISPLAYFIELD]

,[CREATEBYUSERID]

,[CREATEDATE]

,[MODIFYBYUSERID]

,[MODIFYDATE]

,[MULTIPRINTOPTION])

SELECT

QF.QUERYFIELDID

,QF.QUERYID

,QF.FIELDNAME

,QF.FIELDQUESTION

,QF.FIELDTYPE

,QF.FIELDOBJECT

,QF.OBJECTFIELDNAME

,QF.REQUIRED

,QF.COMBOLISTVALUE

,QF.FIELDDEFAULT

,QF.DASHBOARDDEFAULT

,QF.WILDCARDSEARCH

,QF.AUTODOCKETSOURCE

,QF.MULTIPRINTQUERYFIELDID

,QF.MULTIPRINTGLOBALPROPERTY

,QF.MULTIPRINTCONDITIONALCOMPARE

,QF.MULTIPRINTDISPLAYFIELD

,QF.CREATEBYUSERID

,QF.CREATEDATE

,QF.MODIFYBYUSERID

,QF.MODIFYDATE

,QF.MULTIPRINTOPTION

FROM

BENCHMARKTESTSETTING..TBLQUERYFIELD QF

SET IDENTITY_INSERT TBLQUERYFIELD OFF

GO'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Backup Reports into Test',

@step_id=13,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Read Only Image Path',

@step_id=14,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'DECLARE @LISTSTR VARCHAR(MAX)

SELECT @LISTSTR = COALESCE(@LISTSTR+'','' ,'''') + DOCUMENTSHARE

FROM (SELECT DISTINCT DOCUMENTSHARE FROM TBLDOCUMENT WHERE DOCUMENTSHARE IS NOT NULL AND DOCUMENTSHARE <> '''') T

--SELECT @LISTSTR

UPDATE TBLSETTING SET SETTINGVALUE = @LISTSTR WHERE SETTINGKEY LIKE ''ReadOnlyImagePath''',

@database_name=@TestBenchmarkDB,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @usecommand = 'ALTER DATABASE [' + @TestBenchmarkDB + '] SET RECOVERY SIMPLE WITH NO_WAIT

GO'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Change Database to Simple',

@step_id=15,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=@usecommand,

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Kill Backup Settings Connections',

@step_id=16,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'USE MASTER

GO

DECLARE @KILL VARCHAR(8000) = '''';

SELECT @KILL = @KILL + ''KILL '' + CONVERT(VARCHAR(5), SPID) + '';''

FROM MASTER..SYSPROCESSES

WHERE DBID = DB_ID(''BENCHMARKTESTSETTING'')

EXEC(@KILL);',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Drop Settings Database',

@step_id=17,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'DROP DATABASE [BenchmarkTestSetting]

GO',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

 

 

Related content