/
Benchmark Database Refresh Create Job SQL

Benchmark Database Refresh Create Job SQL

USE [msdb]

GO

/**

Editor : Caleb Hammel

Created : 04/09/2020

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(100)

DECLARE @LogFolder VARCHAR(100)

DECLARE @BackupFolder VARCHAR(200)

DECLARE @PhsyDevName VARCHAR(1)

 

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

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

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

SET @PhsyDevName = @BackupFolder

 

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 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]

DECLARE @SQLVersion VARCHAR(25)

SET @SQLVersion = @@version

SET @SQLVersion = RIGHT(@SQLVersion,4)

 

DECLARE @PATH NVARCHAR(500)

DECLARE @LogNameData NVARCHAR(200)

DECLARE @LogNameLog NVARCHAR(200)

SET @PATH = (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 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)

)

 

IF(@SQLVersion > 2015)

BEGIN

ALTER TABLE #fileListTable

ADD [SnapshotURL] NVARCHAR(360)

END

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

SET @LogNameData = (SELECT LogicalName FROM #fileListTable WHERE Type = ''D'')

SET @LogNameLog = (SELECT LogicalName FROM #fileListTable WHERE Type = ''L'')

 

RESTORE DATABASE [' + @TestBenchmarkDB + ']

FROM DISK = @PATH

WITH FILE = 1

,MOVE @LogNameData TO ''' + @DataFolder + @TestBenchmarkDB + '.MDF''

,MOVE @LogNameLog TO ''' + @LogFolder + @TestBenchmarkDB + '_Log.LDF''

,NOUNLOAD

,REPLACE

,STATS = 5

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