Benchmark Database Refresh Create Job SQL
- Jim Calhoun
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