Benchmark Database Refresh Instructions
- Jim Calhoun
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