aziMgVmS

1/*
2
3SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016
4
5Backup: https://ola.hallengren.com/sql-server-backup.html
6Integrity Check: https://ola.hallengren.com/sql-server-integrity-check.html
7Index and Statistics Maintenance: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
8
9The solution is free: https://ola.hallengren.com/license.html
10
11You can contact me by e-mail at ola@hallengren.com.
12
13Last updated 20 June, 2016.
14
15Ola Hallengren
16https://ola.hallengren.com
17
18*/
19
20USE [master] -- Specify the database in which the objects will be created.
21
22SET NOCOUNT ON
23
24DECLARE @CreateJobs nvarchar(max)
25DECLARE @BackupDirectory nvarchar(max)
26DECLARE @CleanupTime int
27DECLARE @OutputFileDirectory nvarchar(max)
28DECLARE @LogToTable nvarchar(max)
29DECLARE @Version numeric(18,10)
30DECLARE @Error int
31
32SET @CreateJobs          = 'Y'          -- Specify whether jobs should be created.
33SET @BackupDirectory     = N'C:\Backup' -- Specify the backup root directory.
34SET @CleanupTime         = NULL         -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
35SET @OutputFileDirectory = NULL         -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
36SET @LogToTable          = 'Y'          -- Log commands to a table.
37
38SET @Error = 0
39
40SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
41
42IF IS_SRVROLEMEMBER('sysadmin') = 0
43BEGIN
44  RAISERROR('You need to be a member of the SysAdmin server role to install the solution.',16,1)
45  SET @Error = @@ERROR
46END
47
48IF OBJECT_ID('tempdb..#Config') IS NOT NULL DROP TABLE #Config
49
50CREATE TABLE #Config ([Name] nvarchar(max),
51                      [Value] nvarchar(max))
52
53IF @CreateJobs = 'Y' AND @OutputFileDirectory IS NULL AND SERVERPROPERTY('EngineEdition') <> 4 AND @Version < 12
54BEGIN
55  IF @Version >= 11
56  BEGIN
57    SELECT @OutputFileDirectory = [path]
58    FROM sys.dm_os_server_diagnostics_log_configurations
59  END
60  ELSE
61  BEGIN
62    SELECT @OutputFileDirectory = LEFT(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)),LEN(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max))) - CHARINDEX('\',REVERSE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)))))
63  END
64END
65
66IF @CreateJobs = 'Y' AND RIGHT(@OutputFileDirectory,1) = '\' AND SERVERPROPERTY('EngineEdition') <> 4
67BEGIN
68  SET @OutputFileDirectory = LEFT(@OutputFileDirectory, LEN(@OutputFileDirectory) - 1)
69END
70
71INSERT INTO #Config ([Name], [Value])
72VALUES('CreateJobs', @CreateJobs)
73
74INSERT INTO #Config ([Name], [Value])
75VALUES('BackupDirectory', @BackupDirectory)
76
77INSERT INTO #Config ([Name], [Value])
78VALUES('CleanupTime', @CleanupTime)
79
80INSERT INTO #Config ([Name], [Value])
81VALUES('OutputFileDirectory', @OutputFileDirectory)
82
83INSERT INTO #Config ([Name], [Value])
84VALUES('LogToTable', @LogToTable)
85
86INSERT INTO #Config ([Name], [Value])
87VALUES('DatabaseName', DB_NAME(DB_ID()))
88
89INSERT INTO #Config ([Name], [Value])
90VALUES('Error', CAST(@Error AS nvarchar))
91
92IF OBJECT_ID('[dbo].[DatabaseBackup]') IS NOT NULL DROP PROCEDURE [dbo].[DatabaseBackup]
93IF OBJECT_ID('[dbo].[DatabaseIntegrityCheck]') IS NOT NULL DROP PROCEDURE [dbo].[DatabaseIntegrityCheck]
94IF OBJECT_ID('[dbo].[IndexOptimize]') IS NOT NULL DROP PROCEDURE [dbo].[IndexOptimize]
95IF OBJECT_ID('[dbo].[CommandExecute]') IS NOT NULL DROP PROCEDURE [dbo].[CommandExecute]
96
97IF OBJECT_ID('[dbo].[CommandLog]') IS NULL AND OBJECT_ID('[dbo].[PK_CommandLog]') IS NULL
98BEGIN
99CREATE TABLE [dbo].[CommandLog](
100[ID] int IDENTITY(1,1) NOT NULL CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED,
101[DatabaseName] sysname NULL,
102[SchemaName] sysname NULL,
103[ObjectName] sysname NULL,
104[ObjectType] char(2) NULL,
105[IndexName] sysname NULL,
106[IndexType] tinyint NULL,
107[StatisticsName] sysname NULL,
108[PartitionNumber] int NULL,
109[ExtendedInfo] xml NULL,
110[Command] nvarchar(max) NOT NULL,
111[CommandType] nvarchar(60) NOT NULL,
112[StartTime] datetime NOT NULL,
113[EndTime] datetime NULL,
114[ErrorNumber] int NULL,
115[ErrorMessage] nvarchar(max) NULL
116)
117END
118GO
119SET ANSI_NULLS ON
120GO
121SET QUOTED_IDENTIFIER ON
122GO
123CREATE PROCEDURE [dbo].[CommandExecute]
124
125@Command nvarchar(max),
126@CommandType nvarchar(max),
127@Mode int,
128@Comment nvarchar(max) = NULL,
129@DatabaseName nvarchar(max) = NULL,
130@SchemaName nvarchar(max) = NULL,
131@ObjectName nvarchar(max) = NULL,
132@ObjectType nvarchar(max) = NULL,
133@IndexName nvarchar(max) = NULL,
134@IndexType int = NULL,
135@StatisticsName nvarchar(max) = NULL,
136@PartitionNumber int = NULL,
137@ExtendedInfo xml = NULL,
138@LogToTable nvarchar(max),
139@Execute nvarchar(max)
140
141AS
142
143BEGIN
144
145  ----------------------------------------------------------------------------------------------------
146  --// Source: https://ola.hallengren.com                                                          //--
147  ----------------------------------------------------------------------------------------------------
148
149  SET NOCOUNT ON
150
151  DECLARE @StartMessage nvarchar(max)
152  DECLARE @EndMessage nvarchar(max)
153  DECLARE @ErrorMessage nvarchar(max)
154  DECLARE @ErrorMessageOriginal nvarchar(max)
155
156  DECLARE @StartTime datetime
157  DECLARE @EndTime datetime
158
159  DECLARE @StartTimeSec datetime
160  DECLARE @EndTimeSec datetime
161
162  DECLARE @ID int
163
164  DECLARE @Error int
165  DECLARE @ReturnCode int
166
167  SET @Error = 0
168  SET @ReturnCode = 0
169
170  ----------------------------------------------------------------------------------------------------
171  --// Check core requirements                                                                    //--
172  ----------------------------------------------------------------------------------------------------
173
174  IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')
175  BEGIN
176    SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
177    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
178    SET @Error = @@ERROR
179  END
180
181  IF @Error <> 0
182  BEGIN
183    SET @ReturnCode = @Error
184    GOTO ReturnCode
185  END
186
187  ----------------------------------------------------------------------------------------------------
188  --// Check input parameters                                                                     //--
189  ----------------------------------------------------------------------------------------------------
190
191  IF @Command IS NULL OR @Command = ''
192  BEGIN
193    SET @ErrorMessage = 'The value for the parameter @Command is not supported.' + CHAR(13) + CHAR(10) + ' '
194    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
195    SET @Error = @@ERROR
196  END
197
198  IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60
199  BEGIN
200    SET @ErrorMessage = 'The value for the parameter @CommandType is not supported.' + CHAR(13) + CHAR(10) + ' '
201    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
202    SET @Error = @@ERROR
203  END
204
205  IF @Mode NOT IN(1,2) OR @Mode IS NULL
206  BEGIN
207    SET @ErrorMessage = 'The value for the parameter @Mode is not supported.' + CHAR(13) + CHAR(10) + ' '
208    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
209    SET @Error = @@ERROR
210  END
211
212  IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
213  BEGIN
214    SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '
215    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
216    SET @Error = @@ERROR
217  END
218
219  IF @Execute NOT IN('Y','N') OR @Execute IS NULL
220  BEGIN
221    SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '
222    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
223    SET @Error = @@ERROR
224  END
225
226  IF @Error <> 0
227  BEGIN
228    SET @ReturnCode = @Error
229    GOTO ReturnCode
230  END
231
232  ----------------------------------------------------------------------------------------------------
233  --// Log initial information                                                                    //--
234  ----------------------------------------------------------------------------------------------------
235
236  SET @StartTime = GETDATE()
237  SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)
238
239  SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)
240  SET @StartMessage = @StartMessage + 'Command: ' + @Command
241  IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + 'Comment: ' + @Comment
242  SET @StartMessage = REPLACE(@StartMessage,'%','%%')
243  RAISERROR(@StartMessage,10,1) WITH NOWAIT
244
245  IF @LogToTable = 'Y'
246  BEGIN
247    INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)
248    VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)
249  END
250
251  SET @ID = SCOPE_IDENTITY()
252
253  ----------------------------------------------------------------------------------------------------
254  --// Execute command                                                                            //--
255  ----------------------------------------------------------------------------------------------------
256
257  IF @Mode = 1 AND @Execute = 'Y'
258  BEGIN
259    EXECUTE(@Command)
260    SET @Error = @@ERROR
261    SET @ReturnCode = @Error
262  END
263
264  IF @Mode = 2 AND @Execute = 'Y'
265  BEGIN
266    BEGIN TRY
267      EXECUTE(@Command)
268    END TRY
269    BEGIN CATCH
270      SET @Error = ERROR_NUMBER()
271      SET @ReturnCode = @Error
272      SET @ErrorMessageOriginal = ERROR_MESSAGE()
273      SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')
274      RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
275    END CATCH
276  END
277
278  ----------------------------------------------------------------------------------------------------
279  --// Log completing information                                                                 //--
280  ----------------------------------------------------------------------------------------------------
281
282  SET @EndTime = GETDATE()
283  SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120)
284
285  SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END + CHAR(13) + CHAR(10)
286  SET @EndMessage = @EndMessage + 'Duration: ' + CASE WHEN DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,@EndTimeSec - @StartTimeSec,108) + CHAR(13) + CHAR(10)
287  SET @EndMessage = @EndMessage + 'Date and time: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10) + ' '
288  SET @EndMessage = REPLACE(@EndMessage,'%','%%')
289  RAISERROR(@EndMessage,10,1) WITH NOWAIT
290
291  IF @LogToTable = 'Y'
292  BEGIN
293    UPDATE dbo.CommandLog
294    SET EndTime = @EndTime,
295        ErrorNumber = CASE WHEN @Execute = 'N' THEN NULL ELSE @Error END,
296        ErrorMessage = @ErrorMessageOriginal
297    WHERE ID = @ID
298  END
299
300  ReturnCode:
301  IF @ReturnCode <> 0
302  BEGIN
303    RETURN @ReturnCode
304  END
305
306  ----------------------------------------------------------------------------------------------------
307
308END
309GO
310SET ANSI_NULLS ON
311GO
312SET QUOTED_IDENTIFIER ON
313GO
314CREATE PROCEDURE [dbo].[DatabaseBackup]
315
316@Databases nvarchar(max),
317@Directory nvarchar(max) = NULL,
318@BackupType nvarchar(max),
319@Verify nvarchar(max) = 'N',
320@CleanupTime int = NULL,
321@CleanupMode nvarchar(max) = 'AFTER_BACKUP',
322@Compress nvarchar(max) = NULL,
323@CopyOnly nvarchar(max) = 'N',
324@ChangeBackupType nvarchar(max) = 'N',
325@BackupSoftware nvarchar(max) = NULL,
326@CheckSum nvarchar(max) = 'N',
327@BlockSize int = NULL,
328@BufferCount int = NULL,
329@MaxTransferSize int = NULL,
330@NumberOfFiles int = NULL,
331@CompressionLevel int = NULL,
332@Description nvarchar(max) = NULL,
333@Threads int = NULL,
334@Throttle int = NULL,
335@Encrypt nvarchar(max) = 'N',
336@EncryptionAlgorithm nvarchar(max) = NULL,
337@ServerCertificate nvarchar(max) = NULL,
338@ServerAsymmetricKey nvarchar(max) = NULL,
339@EncryptionKey nvarchar(max) = NULL,
340@ReadWriteFileGroups nvarchar(max) = 'N',
341@OverrideBackupPreference nvarchar(max) = 'N',
342@NoRecovery nvarchar(max) = 'N',
343@URL nvarchar(max) = NULL,
344@Credential nvarchar(max) = NULL,
345@MirrorDirectory nvarchar(max) = NULL,
346@MirrorCleanupTime int = NULL,
347@MirrorCleanupMode nvarchar(max) = 'AFTER_BACKUP',
348@LogToTable nvarchar(max) = 'N',
349@Execute nvarchar(max) = 'Y'
350
351AS
352
353BEGIN
354
355  ----------------------------------------------------------------------------------------------------
356  --// Source: https://ola.hallengren.com                                                          //--
357  ----------------------------------------------------------------------------------------------------
358
359  SET NOCOUNT ON
360
361  DECLARE @StartMessage nvarchar(max)
362  DECLARE @EndMessage nvarchar(max)
363  DECLARE @DatabaseMessage nvarchar(max)
364  DECLARE @ErrorMessage nvarchar(max)
365
366  DECLARE @Version numeric(18,10)
367  DECLARE @AmazonRDS bit
368
369  DECLARE @Cluster nvarchar(max)
370
371  DECLARE @DefaultDirectory nvarchar(4000)
372
373  DECLARE @CurrentRootDirectoryID int
374  DECLARE @CurrentRootDirectoryPath nvarchar(4000)
375
376  DECLARE @CurrentDBID int
377  DECLARE @CurrentDatabaseID int
378  DECLARE @CurrentDatabaseName nvarchar(max)
379  DECLARE @CurrentBackupType nvarchar(max)
380  DECLARE @CurrentFileExtension nvarchar(max)
381  DECLARE @CurrentFileNumber int
382  DECLARE @CurrentDifferentialBaseLSN numeric(25,0)
383  DECLARE @CurrentDifferentialBaseIsSnapshot bit
384  DECLARE @CurrentLogLSN numeric(25,0)
385  DECLARE @CurrentLatestBackup datetime
386  DECLARE @CurrentDatabaseNameFS nvarchar(max)
387  DECLARE @CurrentDirectoryID int
388  DECLARE @CurrentDirectoryPath nvarchar(max)
389  DECLARE @CurrentFilePath nvarchar(max)
390  DECLARE @CurrentDate datetime
391  DECLARE @CurrentCleanupDate datetime
392  DECLARE @CurrentIsDatabaseAccessible bit
393  DECLARE @CurrentAvailabilityGroup nvarchar(max)
394  DECLARE @CurrentAvailabilityGroupRole nvarchar(max)
395  DECLARE @CurrentAvailabilityGroupBackupPreference nvarchar(max)
396  DECLARE @CurrentIsPreferredBackupReplica bit
397  DECLARE @CurrentDatabaseMirroringRole nvarchar(max)
398  DECLARE @CurrentLogShippingRole nvarchar(max)
399  DECLARE @CurrentBackupSetID int
400  DECLARE @CurrentIsMirror bit
401
402  DECLARE @CurrentCommand01 nvarchar(max)
403  DECLARE @CurrentCommand02 nvarchar(max)
404  DECLARE @CurrentCommand03 nvarchar(max)
405  DECLARE @CurrentCommand04 nvarchar(max)
406  DECLARE @CurrentCommand05 nvarchar(max)
407
408  DECLARE @CurrentCommandOutput01 int
409  DECLARE @CurrentCommandOutput02 int
410  DECLARE @CurrentCommandOutput03 int
411  DECLARE @CurrentCommandOutput04 int
412  DECLARE @CurrentCommandOutput05 int
413
414  DECLARE @CurrentCommandType01 nvarchar(max)
415  DECLARE @CurrentCommandType02 nvarchar(max)
416  DECLARE @CurrentCommandType03 nvarchar(max)
417  DECLARE @CurrentCommandType04 nvarchar(max)
418  DECLARE @CurrentCommandType05 nvarchar(max)
419
420  DECLARE @Directories TABLE (ID int PRIMARY KEY,
421                              DirectoryPath nvarchar(max),
422                              Mirror bit,
423                              Completed bit)
424
425  DECLARE @DirectoryInfo TABLE (FileExists bit,
426                                FileIsADirectory bit,
427                                ParentDirectoryExists bit)
428
429  DECLARE @tmpDatabases TABLE (ID int IDENTITY,
430                               DatabaseName nvarchar(max),
431                               DatabaseNameFS nvarchar(max),
432                               DatabaseType nvarchar(max),
433                               Selected bit,
434                               Completed bit,
435                               PRIMARY KEY(Selected, Completed, ID))
436
437  DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
438                                    DatabaseType nvarchar(max),
439                                    Selected bit)
440
441  DECLARE @CurrentBackupSet TABLE (ID int IDENTITY PRIMARY KEY,
442                                   Mirror bit,
443                                   VerifyCompleted bit,
444                                   VerifyOutput int)
445
446  DECLARE @CurrentDirectories TABLE (ID int PRIMARY KEY,
447                                     DirectoryPath nvarchar(max),
448                                     Mirror bit,
449                                     DirectoryNumber int,
450                                     CleanupDate datetime,
451                                     CleanupMode nvarchar(max),
452                                     CreateCompleted bit,
453                                     CleanupCompleted bit,
454                                     CreateOutput int,
455                                     CleanupOutput int)
456
457  DECLARE @CurrentFiles TABLE ([Type] nvarchar(max),
458                               FilePath nvarchar(max),
459                               Mirror bit)
460
461  DECLARE @CurrentCleanupDates TABLE (CleanupDate datetime, Mirror bit)
462
463  DECLARE @DirectoryCheck bit
464
465  DECLARE @Error int
466  DECLARE @ReturnCode int
467
468  SET @Error = 0
469  SET @ReturnCode = 0
470
471  SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
472
473  SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END
474
475  ----------------------------------------------------------------------------------------------------
476  --// Log initial information                                                                    //--
477  ----------------------------------------------------------------------------------------------------
478
479  SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
480  SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)
481  SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)
482  SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)
483  SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
484  SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')
485  SET @StartMessage = @StartMessage + ', @Directory = ' + ISNULL('''' + REPLACE(@Directory,'''','''''') + '''','NULL')
486  SET @StartMessage = @StartMessage + ', @BackupType = ' + ISNULL('''' + REPLACE(@BackupType,'''','''''') + '''','NULL')
487  SET @StartMessage = @StartMessage + ', @Verify = ' + ISNULL('''' + REPLACE(@Verify,'''','''''') + '''','NULL')
488  SET @StartMessage = @StartMessage + ', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL')
489  SET @StartMessage = @StartMessage + ', @CleanupMode = ' + ISNULL('''' + REPLACE(@CleanupMode,'''','''''') + '''','NULL')
490  SET @StartMessage = @StartMessage + ', @Compress = ' + ISNULL('''' + REPLACE(@Compress,'''','''''') + '''','NULL')
491  SET @StartMessage = @StartMessage + ', @CopyOnly = ' + ISNULL('''' + REPLACE(@CopyOnly,'''','''''') + '''','NULL')
492  SET @StartMessage = @StartMessage + ', @ChangeBackupType = ' + ISNULL('''' + REPLACE(@ChangeBackupType,'''','''''') + '''','NULL')
493  SET @StartMessage = @StartMessage + ', @BackupSoftware = ' + ISNULL('''' + REPLACE(@BackupSoftware,'''','''''') + '''','NULL')
494  SET @StartMessage = @StartMessage + ', @CheckSum = ' + ISNULL('''' + REPLACE(@CheckSum,'''','''''') + '''','NULL')
495  SET @StartMessage = @StartMessage + ', @BlockSize = ' + ISNULL(CAST(@BlockSize AS nvarchar),'NULL')
496  SET @StartMessage = @StartMessage + ', @BufferCount = ' + ISNULL(CAST(@BufferCount AS nvarchar),'NULL')
497  SET @StartMessage = @StartMessage + ', @MaxTransferSize = ' + ISNULL(CAST(@MaxTransferSize AS nvarchar),'NULL')
498  SET @StartMessage = @StartMessage + ', @NumberOfFiles = ' + ISNULL(CAST(@NumberOfFiles AS nvarchar),'NULL')
499  SET @StartMessage = @StartMessage + ', @CompressionLevel = ' + ISNULL(CAST(@CompressionLevel AS nvarchar),'NULL')
500  SET @StartMessage = @StartMessage + ', @Description = ' + ISNULL('''' + REPLACE(@Description,'''','''''') + '''','NULL')
501  SET @StartMessage = @StartMessage + ', @Threads = ' + ISNULL(CAST(@Threads AS nvarchar),'NULL')
502  SET @StartMessage = @StartMessage + ', @Throttle = ' + ISNULL(CAST(@Throttle AS nvarchar),'NULL')
503  SET @StartMessage = @StartMessage + ', @Encrypt = ' + ISNULL('''' + REPLACE(@Encrypt,'''','''''') + '''','NULL')
504  SET @StartMessage = @StartMessage + ', @EncryptionAlgorithm = ' + ISNULL('''' + REPLACE(@EncryptionAlgorithm,'''','''''') + '''','NULL')
505  SET @StartMessage = @StartMessage + ', @ServerCertificate = ' + ISNULL('''' + REPLACE(@ServerCertificate,'''','''''') + '''','NULL')
506  SET @StartMessage = @StartMessage + ', @ServerAsymmetricKey = ' + ISNULL('''' + REPLACE(@ServerAsymmetricKey,'''','''''') + '''','NULL')
507  SET @StartMessage = @StartMessage + ', @EncryptionKey = ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL')
508  SET @StartMessage = @StartMessage + ', @ReadWriteFileGroups = ' + ISNULL('''' + REPLACE(@ReadWriteFileGroups,'''','''''') + '''','NULL')
509  SET @StartMessage = @StartMessage + ', @OverrideBackupPreference = ' + ISNULL('''' + REPLACE(@OverrideBackupPreference,'''','''''') + '''','NULL')
510  SET @StartMessage = @StartMessage + ', @NoRecovery = ' + ISNULL('''' + REPLACE(@NoRecovery,'''','''''') + '''','NULL')
511  SET @StartMessage = @StartMessage + ', @URL = ' + ISNULL('''' + REPLACE(@URL,'''','''''') + '''','NULL')
512  SET @StartMessage = @StartMessage + ', @Credential = ' + ISNULL('''' + REPLACE(@Credential,'''','''''') + '''','NULL')
513  SET @StartMessage = @StartMessage + ', @MirrorDirectory = ' + ISNULL('''' + REPLACE(@MirrorDirectory,'''','''''') + '''','NULL')
514  SET @StartMessage = @StartMessage + ', @MirrorCleanupTime = ' + ISNULL(CAST(@MirrorCleanupTime AS nvarchar),'NULL')
515  SET @StartMessage = @StartMessage + ', @MirrorCleanupMode = ' + ISNULL('''' + REPLACE(@MirrorCleanupMode,'''','''''') + '''','NULL')
516  SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')
517  SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)
518  SET @StartMessage = @StartMessage + 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10)
519  SET @StartMessage = REPLACE(@StartMessage,'%','%%') + ' '
520  RAISERROR(@StartMessage,10,1) WITH NOWAIT
521
522  ----------------------------------------------------------------------------------------------------
523  --// Check core requirements                                                                    //--
524  ----------------------------------------------------------------------------------------------------
525
526  IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute')
527  BEGIN
528    SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
529    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
530    SET @Error = @@ERROR
531  END
532
533  IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute' AND (OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))
534  BEGIN
535    SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
536    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
537    SET @Error = @@ERROR
538  END
539
540  IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')
541  BEGIN
542    SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
543    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
544    SET @Error = @@ERROR
545  END
546
547  IF @AmazonRDS = 1
548  BEGIN
549    SET @ErrorMessage = 'The stored procedure DatabaseBackup is not supported on Amazon RDS.' + CHAR(13) + CHAR(10) + ' '
550    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
551    SET @Error = @@ERROR
552  END
553
554  IF @Error <> 0
555  BEGIN
556    SET @ReturnCode = @Error
557    GOTO Logging
558  END
559
560  ----------------------------------------------------------------------------------------------------
561  --// Select databases                                                                           //--
562  ----------------------------------------------------------------------------------------------------
563
564  SET @Databases = REPLACE(@Databases, ', ', ',');
565
566  WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
567  (
568  SELECT 1 AS StartPosition,
569         ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
570         SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem
571  WHERE @Databases IS NOT NULL
572  UNION ALL
573  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
574         ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
575         SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem
576  FROM Databases1
577  WHERE EndPosition < LEN(@Databases) + 1
578  ),
579  Databases2 (DatabaseItem, Selected) AS
580  (
581  SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
582         CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
583  FROM Databases1
584  ),
585  Databases3 (DatabaseItem, DatabaseType, Selected) AS
586  (
587  SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
588         CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
589         Selected
590  FROM Databases2
591  ),
592  Databases4 (DatabaseName, DatabaseType, Selected) AS
593  (
594  SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
595         DatabaseType,
596         Selected
597  FROM Databases3
598  )
599  INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
600  SELECT DatabaseName,
601         DatabaseType,
602         Selected
603  FROM Databases4
604  OPTION (MAXRECURSION 0)
605
606  INSERT INTO @tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, Selected, Completed)
607  SELECT [name] AS DatabaseName,
608         REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') AS DatabaseNameFS,
609         CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
610         0 AS Selected,
611         0 AS Completed
612  FROM sys.databases
613  WHERE [name] <> 'tempdb'
614  AND source_database_id IS NULL
615  ORDER BY [name] ASC
616
617  UPDATE tmpDatabases
618  SET tmpDatabases.Selected = SelectedDatabases.Selected
619  FROM @tmpDatabases tmpDatabases
620  INNER JOIN @SelectedDatabases SelectedDatabases
621  ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
622  AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
623  WHERE SelectedDatabases.Selected = 1
624
625  UPDATE tmpDatabases
626  SET tmpDatabases.Selected = SelectedDatabases.Selected
627  FROM @tmpDatabases tmpDatabases
628  INNER JOIN @SelectedDatabases SelectedDatabases
629  ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
630  AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
631  WHERE SelectedDatabases.Selected = 0
632
633  IF @Databases IS NULL OR NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')
634  BEGIN
635    SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '
636    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
637    SET @Error = @@ERROR
638  END
639
640  ----------------------------------------------------------------------------------------------------
641  --// Check database names                                                                       //--
642  ----------------------------------------------------------------------------------------------------
643
644  SET @ErrorMessage = ''
645  SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
646  FROM @tmpDatabases
647  WHERE Selected = 1
648  AND DatabaseNameFS = ''
649  ORDER BY DatabaseName ASC
650  IF @@ROWCOUNT > 0
651  BEGIN
652    SET @ErrorMessage = 'The names of the following databases are not supported: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '
653    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
654    SET @Error = @@ERROR
655  END
656
657  SET @ErrorMessage = ''
658  SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
659  FROM @tmpDatabases
660  WHERE UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases GROUP BY UPPER(DatabaseNameFS) HAVING COUNT(*) > 1)
661  AND UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases WHERE Selected = 1)
662  AND DatabaseNameFS <> ''
663  ORDER BY DatabaseName ASC
664  OPTION (RECOMPILE)
665  IF @@ROWCOUNT > 0
666  BEGIN
667    SET @ErrorMessage = 'The names of the following databases are not unique in the file system: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '
668    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
669    SET @Error = @@ERROR
670  END
671
672  ----------------------------------------------------------------------------------------------------
673  --// Select directories                                                                         //--
674  ----------------------------------------------------------------------------------------------------
675
676  IF @Directory IS NULL AND @URL IS NULL
677  BEGIN
678    EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultDirectory OUTPUT
679
680    INSERT INTO @Directories (ID, DirectoryPath, Mirror, Completed)
681    SELECT 1, @DefaultDirectory, 0, 0
682  END
683  ELSE
684  BEGIN
685    SET @Directory = REPLACE(@Directory, ', ', ',');
686
687    WITH Directories (StartPosition, EndPosition, Directory) AS
688    (
689    SELECT 1 AS StartPosition,
690           ISNULL(NULLIF(CHARINDEX(',', @Directory, 1), 0), LEN(@Directory) + 1) AS EndPosition,
691           SUBSTRING(@Directory, 1, ISNULL(NULLIF(CHARINDEX(',', @Directory, 1), 0), LEN(@Directory) + 1) - 1) AS Directory
692    WHERE @Directory IS NOT NULL
693    UNION ALL
694    SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
695           ISNULL(NULLIF(CHARINDEX(',', @Directory, EndPosition + 1), 0), LEN(@Directory) + 1) AS EndPosition,
696           SUBSTRING(@Directory, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Directory, EndPosition + 1), 0), LEN(@Directory) + 1) - EndPosition - 1) AS Directory
697    FROM Directories
698    WHERE EndPosition < LEN(@Directory) + 1
699    )
700    INSERT INTO @Directories (ID, DirectoryPath, Mirror, Completed)
701    SELECT ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS ID,
702           Directory,
703           0,
704           0
705    FROM Directories
706    OPTION (MAXRECURSION 0)
707  END
708
709  SET @MirrorDirectory = REPLACE(@MirrorDirectory, ', ', ',');
710
711  WITH Directories (StartPosition, EndPosition, Directory) AS
712  (
713  SELECT 1 AS StartPosition,
714         ISNULL(NULLIF(CHARINDEX(',', @MirrorDirectory, 1), 0), LEN(@MirrorDirectory) + 1) AS EndPosition,
715         SUBSTRING(@MirrorDirectory, 1, ISNULL(NULLIF(CHARINDEX(',', @MirrorDirectory, 1), 0), LEN(@MirrorDirectory) + 1) - 1) AS Directory
716  WHERE @MirrorDirectory IS NOT NULL
717  UNION ALL
718  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
719         ISNULL(NULLIF(CHARINDEX(',', @MirrorDirectory, EndPosition + 1), 0), LEN(@MirrorDirectory) + 1) AS EndPosition,
720         SUBSTRING(@MirrorDirectory, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @MirrorDirectory, EndPosition + 1), 0), LEN(@MirrorDirectory) + 1) - EndPosition - 1) AS Directory
721  FROM Directories
722  WHERE EndPosition < LEN(@MirrorDirectory) + 1
723  )
724  INSERT INTO @Directories (ID, DirectoryPath, Mirror, Completed)
725  SELECT (SELECT COUNT(*) FROM @Directories) + ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS ID,
726         Directory,
727         1,
728         0
729  FROM Directories
730  OPTION (MAXRECURSION 0)
731
732  ----------------------------------------------------------------------------------------------------
733  --// Check directories                                                                          //--
734  ----------------------------------------------------------------------------------------------------
735
736  SET @DirectoryCheck = 1
737
738  IF EXISTS(SELECT * FROM @Directories WHERE Mirror = 0 AND (NOT (DirectoryPath LIKE '_:' OR DirectoryPath LIKE '_:\%' OR DirectoryPath LIKE '\\%\%') OR DirectoryPath IS NULL OR LEFT(DirectoryPath,1) = ' ' OR RIGHT(DirectoryPath,1) = ' ')) OR EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) <> 1) OR ((SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) <> (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 0)
739  BEGIN
740    SET @ErrorMessage = 'The value for the parameter @Directory is not supported.' + CHAR(13) + CHAR(10) + ' '
741    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
742    SET @Error = @@ERROR
743    SET @DirectoryCheck = 0
744  END
745
746  IF EXISTS(SELECT * FROM @Directories WHERE Mirror = 1 AND (NOT (DirectoryPath LIKE '_:' OR DirectoryPath LIKE '_:\%' OR DirectoryPath LIKE '\\%\%') OR DirectoryPath IS NULL OR LEFT(DirectoryPath,1) = ' ' OR RIGHT(DirectoryPath,1) = ' ')) OR EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) <> 1) OR ((SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) <> (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 0) OR (@BackupSoftware IN('SQLBACKUP','SQLSAFE') AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 1) OR (@BackupSoftware IS NULL AND EXISTS(SELECT * FROM @Directories WHERE Mirror = 1) AND SERVERPROPERTY('EngineEdition') <> 3)
747  BEGIN
748    SET @ErrorMessage = 'The value for the parameter @MirrorDirectory is not supported.' + CHAR(13) + CHAR(10) + ' '
749    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
750    SET @Error = @@ERROR
751    SET @DirectoryCheck = 0
752  END
753
754  IF @DirectoryCheck = 1
755  BEGIN
756    WHILE EXISTS(SELECT * FROM @Directories WHERE Completed = 0)
757    BEGIN
758      SELECT TOP 1 @CurrentRootDirectoryID = ID,
759                   @CurrentRootDirectoryPath = DirectoryPath
760      FROM @Directories
761      WHERE Completed = 0
762      ORDER BY ID ASC
763
764      INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)
765      EXECUTE [master].dbo.xp_fileexist @CurrentRootDirectoryPath
766
767      IF NOT EXISTS (SELECT * FROM @DirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1)
768      BEGIN
769        SET @ErrorMessage = 'The directory ' + @CurrentRootDirectoryPath + ' does not exist.' + CHAR(13) + CHAR(10) + ' '
770        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
771        SET @Error = @@ERROR
772      END
773
774      UPDATE @Directories
775      SET Completed = 1
776      WHERE ID = @CurrentRootDirectoryID
777
778      SET @CurrentRootDirectoryID = NULL
779      SET @CurrentRootDirectoryPath = NULL
780
781      DELETE FROM @DirectoryInfo
782    END
783  END
784
785  ----------------------------------------------------------------------------------------------------
786  --// Get default compression                                                                    //--
787  ----------------------------------------------------------------------------------------------------
788
789  IF @Compress IS NULL
790  BEGIN
791    SELECT @Compress = CASE
792    WHEN @BackupSoftware IS NULL AND EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup compression default' AND value_in_use = 1) THEN 'Y'
793    WHEN @BackupSoftware IS NULL AND NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup compression default' AND value_in_use = 1) THEN 'N'
794    WHEN @BackupSoftware IS NOT NULL AND (@CompressionLevel IS NULL OR @CompressionLevel > 0)  THEN 'Y'
795    WHEN @BackupSoftware IS NOT NULL AND @CompressionLevel = 0  THEN 'N'
796    END
797  END
798
799  ----------------------------------------------------------------------------------------------------
800  --// Get number of files                                                                        //--
801  ----------------------------------------------------------------------------------------------------
802
803  IF @NumberOfFiles IS NULL
804  BEGIN
805    SELECT @NumberOfFiles = CASE WHEN @URL IS NOT NULL THEN 1 ELSE (SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) END
806  END
807
808  ----------------------------------------------------------------------------------------------------
809  --// Check input parameters                                                                     //--
810  ----------------------------------------------------------------------------------------------------
811
812  IF @BackupType NOT IN ('FULL','DIFF','LOG') OR @BackupType IS NULL
813  BEGIN
814    SET @ErrorMessage = 'The value for the parameter @BackupType is not supported.' + CHAR(13) + CHAR(10) + ' '
815    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
816    SET @Error = @@ERROR
817  END
818
819  IF @Verify NOT IN ('Y','N') OR @Verify IS NULL OR (@BackupSoftware = 'SQLSAFE' AND @Encrypt = 'Y' AND @Verify = 'Y')
820  BEGIN
821    SET @ErrorMessage = 'The value for the parameter @Verify is not supported.' + CHAR(13) + CHAR(10) + ' '
822    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
823    SET @Error = @@ERROR
824  END
825
826  IF @CleanupTime < 0 OR (@CleanupTime IS NOT NULL AND @URL IS NOT NULL)
827  BEGIN
828    SET @ErrorMessage = 'The value for the parameter @CleanupTime is not supported.' + CHAR(13) + CHAR(10) + ' '
829    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
830    SET @Error = @@ERROR
831  END
832
833  IF @CleanupMode NOT IN('BEFORE_BACKUP','AFTER_BACKUP') OR @CleanupMode IS NULL
834  BEGIN
835    SET @ErrorMessage = 'The value for the parameter @CleanupMode is not supported.' + CHAR(13) + CHAR(10) + ' '
836    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
837    SET @Error = @@ERROR
838  END
839
840  IF @Compress NOT IN ('Y','N') OR @Compress IS NULL OR (@Compress = 'Y' AND @BackupSoftware IS NULL AND NOT ((@Version >= 10 AND @Version < 10.5 AND SERVERPROPERTY('EngineEdition') = 3) OR (@Version >= 10.5 AND (SERVERPROPERTY('EngineEdition') = 3 OR SERVERPROPERTY('EditionID') IN (-1534726760, 284895786))))) OR (@Compress = 'N' AND @BackupSoftware IS NOT NULL AND (@CompressionLevel IS NULL OR @CompressionLevel >= 1)) OR (@Compress = 'Y' AND @BackupSoftware IS NOT NULL AND @CompressionLevel = 0)
841  BEGIN
842    SET @ErrorMessage = 'The value for the parameter @Compress is not supported.' + CHAR(13) + CHAR(10) + ' '
843    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
844    SET @Error = @@ERROR
845  END
846
847  IF @CopyOnly NOT IN ('Y','N') OR @CopyOnly IS NULL
848  BEGIN
849    SET @ErrorMessage = 'The value for the parameter @CopyOnly is not supported.' + CHAR(13) + CHAR(10) + ' '
850    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
851    SET @Error = @@ERROR
852  END
853
854  IF @ChangeBackupType NOT IN ('Y','N') OR @ChangeBackupType IS NULL
855  BEGIN
856    SET @ErrorMessage = 'The value for the parameter @ChangeBackupType is not supported.' + CHAR(13) + CHAR(10) + ' '
857    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
858    SET @Error = @@ERROR
859  END
860
861  IF @BackupSoftware NOT IN ('LITESPEED','SQLBACKUP','SQLSAFE')
862  BEGIN
863    SET @ErrorMessage = 'The value for the parameter @BackupSoftware is not supported.' + CHAR(13) + CHAR(10) + ' '
864    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
865    SET @Error = @@ERROR
866  END
867
868  IF @BackupSoftware = 'LITESPEED' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_backup_database')
869  BEGIN
870    SET @ErrorMessage = 'LiteSpeed for SQL Server is not installed. Download http://software.dell.com/products/litespeed-for-sql-server/.' + CHAR(13) + CHAR(10) + ' '
871    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
872    SET @Error = @@ERROR
873  END
874
875  IF @BackupSoftware = 'SQLBACKUP' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'sqlbackup')
876  BEGIN
877    SET @ErrorMessage = 'Red Gate SQL Backup Pro is not installed. Download http://www.red-gate.com/products/dba/sql-backup/.' + CHAR(13) + CHAR(10) + ' '
878    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
879    SET @Error = @@ERROR
880  END
881
882  IF @BackupSoftware = 'SQLSAFE' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_ss_backup')
883  BEGIN
884    SET @ErrorMessage = 'Idera SQL Safe Backup is not installed. Download https://www.idera.com/productssolutions/sqlserver/sqlsafebackup.' + CHAR(13) + CHAR(10) + ' '
885    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
886    SET @Error = @@ERROR
887  END
888
889  IF @CheckSum NOT IN ('Y','N') OR @CheckSum IS NULL
890  BEGIN
891    SET @ErrorMessage = 'The value for the parameter @CheckSum is not supported.' + CHAR(13) + CHAR(10) + ' '
892    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
893    SET @Error = @@ERROR
894  END
895
896  IF @BlockSize NOT IN (512,1024,2048,4096,8192,16384,32768,65536) OR (@BlockSize IS NOT NULL AND @BackupSoftware = 'SQLBACKUP') OR (@BlockSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE') OR (@BlockSize IS NOT NULL AND @URL IS NOT NULL)
897  BEGIN
898    SET @ErrorMessage = 'The value for the parameter @BlockSize is not supported.' + CHAR(13) + CHAR(10) + ' '
899    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
900    SET @Error = @@ERROR
901  END
902
903  IF @BufferCount <= 0 OR @BufferCount > 2147483647 OR (@BufferCount IS NOT NULL AND @BackupSoftware = 'SQLBACKUP') OR (@BufferCount IS NOT NULL AND @BackupSoftware = 'SQLSAFE')
904  BEGIN
905    SET @ErrorMessage = 'The value for the parameter @BufferCount is not supported.' + CHAR(13) + CHAR(10) + ' '
906    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
907    SET @Error = @@ERROR
908  END
909
910  IF @MaxTransferSize < 65536 OR @MaxTransferSize > 4194304 OR @MaxTransferSize % 65536 > 0 OR (@MaxTransferSize > 1048576 AND @BackupSoftware = 'SQLBACKUP') OR (@MaxTransferSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE') OR (@MaxTransferSize IS NOT NULL AND @URL IS NOT NULL)
911  BEGIN
912    SET @ErrorMessage = 'The value for the parameter @MaxTransferSize is not supported.' + CHAR(13) + CHAR(10) + ' '
913    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
914    SET @Error = @@ERROR
915  END
916
917  IF @NumberOfFiles < 1 OR @NumberOfFiles > 64 OR (@NumberOfFiles > 32 AND @BackupSoftware = 'SQLBACKUP') OR @NumberOfFiles IS NULL OR @NumberOfFiles < (SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) OR @NumberOfFiles % (SELECT NULLIF(COUNT(*),0) FROM @Directories WHERE Mirror = 0) > 0 OR (@URL IS NOT NULL AND @NumberOfFiles <> 1) OR (@NumberOfFiles > 1 AND @BackupSoftware IN('SQLBACKUP','SQLSAFE') AND EXISTS(SELECT * FROM @Directories WHERE Mirror = 1))
918  BEGIN
919    SET @ErrorMessage = 'The value for the parameter @NumberOfFiles is not supported.' + CHAR(13) + CHAR(10) + ' '
920    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
921    SET @Error = @@ERROR
922  END
923
924  IF (@BackupSoftware IS NULL AND @CompressionLevel IS NOT NULL) OR (@BackupSoftware = 'LITESPEED' AND (@CompressionLevel < 0 OR @CompressionLevel > 8)) OR (@BackupSoftware = 'SQLBACKUP' AND (@CompressionLevel < 0 OR @CompressionLevel > 4)) OR (@BackupSoftware = 'SQLSAFE' AND (@CompressionLevel < 1 OR @CompressionLevel > 4))
925  BEGIN
926    SET @ErrorMessage = 'The value for the parameter @CompressionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '
927    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
928    SET @Error = @@ERROR
929  END
930
931  IF LEN(@Description) > 255 OR (@BackupSoftware = 'LITESPEED' AND LEN(@Description) > 128)
932  BEGIN
933    SET @ErrorMessage = 'The value for the parameter @Description is not supported.' + CHAR(13) + CHAR(10) + ' '
934    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
935    SET @Error = @@ERROR
936  END
937
938  IF @Threads IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED','SQLBACKUP','SQLSAFE') OR @BackupSoftware IS NULL) OR (@BackupSoftware = 'LITESPEED' AND (@Threads < 1 OR @Threads > 32)) OR (@BackupSoftware = 'SQLBACKUP' AND (@Threads < 2 OR @Threads > 32)) OR (@BackupSoftware = 'SQLSAFE' AND (@Threads < 1 OR @Threads > 64))
939  BEGIN
940    SET @ErrorMessage = 'The value for the parameter @Threads is not supported.' + CHAR(13) + CHAR(10) + ' '
941    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
942    SET @Error = @@ERROR
943  END
944
945  IF @Throttle IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED') OR @BackupSoftware IS NULL) OR @Throttle < 1 OR @Throttle > 100
946  BEGIN
947    SET @ErrorMessage = 'The value for the parameter @Throttle is not supported.' + CHAR(13) + CHAR(10) + ' '
948    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
949    SET @Error = @@ERROR
950  END
951
952  IF @Encrypt NOT IN('Y','N') OR @Encrypt IS NULL OR (@Encrypt = 'Y' AND @BackupSoftware IS NULL AND NOT (@Version >= 12 AND (SERVERPROPERTY('EngineEdition') = 3) OR SERVERPROPERTY('EditionID') IN(-1534726760, 284895786)))
953  BEGIN
954    SET @ErrorMessage = 'The value for the parameter @Encrypt is not supported.' + CHAR(13) + CHAR(10) + ' '
955    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
956    SET @Error = @@ERROR
957  END
958
959  IF (@BackupSoftware IS NULL AND @Encrypt = 'Y' AND (@EncryptionAlgorithm NOT IN('AES_128','AES_192','AES_256','TRIPLE_DES_3KEY') OR @EncryptionAlgorithm IS NULL)) OR (@BackupSoftware = 'LITESPEED' AND @Encrypt = 'Y' AND (@EncryptionAlgorithm NOT IN('RC2_40','RC2_56','RC2_112','RC2_128','TRIPLE_DES_3KEY','RC4_128','AES_128','AES_192','AES_256') OR @EncryptionAlgorithm IS NULL)) OR (@BackupSoftware = 'SQLBACKUP' AND @Encrypt = 'Y' AND (@EncryptionAlgorithm NOT IN('AES_128','AES_256') OR @EncryptionAlgorithm IS NULL)) OR (@BackupSoftware = 'SQLSAFE' AND @Encrypt = 'Y' AND (@EncryptionAlgorithm NOT IN('AES_128','AES_256') OR @EncryptionAlgorithm IS NULL))
960  BEGIN
961    SET @ErrorMessage = 'The value for the parameter @EncryptionAlgorithm is not supported.' + CHAR(13) + CHAR(10) + ' '
962    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
963    SET @Error = @@ERROR
964  END
965
966  IF (NOT (@BackupSoftware IS NULL AND @Encrypt = 'Y') AND @ServerCertificate IS NOT NULL) OR (@BackupSoftware IS NULL AND @Encrypt = 'Y' AND @ServerCertificate IS NULL AND @ServerAsymmetricKey IS NULL) OR (@BackupSoftware IS NULL AND @Encrypt = 'Y' AND @ServerCertificate IS NOT NULL AND @ServerAsymmetricKey IS NOT NULL) OR (@ServerCertificate IS NOT NULL AND NOT EXISTS(SELECT * FROM master.sys.certificates WHERE name = @ServerCertificate))
967  BEGIN
968    SET @ErrorMessage = 'The value for the parameter @ServerCertificate is not supported.' + CHAR(13) + CHAR(10) + ' '
969    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
970    SET @Error = @@ERROR
971  END
972
973  IF (NOT (@BackupSoftware IS NULL AND @Encrypt = 'Y') AND @ServerAsymmetricKey IS NOT NULL) OR (@BackupSoftware IS NULL AND @Encrypt = 'Y' AND @ServerAsymmetricKey IS NULL AND @ServerCertificate IS NULL) OR (@BackupSoftware IS NULL AND @Encrypt = 'Y' AND @ServerAsymmetricKey IS NOT NULL AND @ServerCertificate IS NOT NULL) OR (@ServerAsymmetricKey IS NOT NULL AND NOT EXISTS(SELECT * FROM master.sys.asymmetric_keys WHERE name = @ServerAsymmetricKey))
974  BEGIN
975    SET @ErrorMessage = 'The value for the parameter @ServerAsymmetricKey is not supported.' + CHAR(13) + CHAR(10) + ' '
976    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
977    SET @Error = @@ERROR
978  END
979
980  IF (@EncryptionKey IS NOT NULL AND @BackupSoftware IS NULL) OR (@EncryptionKey IS NOT NULL AND @Encrypt = 'N') OR (@EncryptionKey IS NULL AND @Encrypt = 'Y' AND @BackupSoftware IN('LITESPEED','SQLBACKUP','SQLSAFE'))
981  BEGIN
982    SET @ErrorMessage = 'The value for the parameter @EncryptionKey is not supported.' + CHAR(13) + CHAR(10) + ' '
983    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
984    SET @Error = @@ERROR
985  END
986
987  IF @ReadWriteFileGroups NOT IN('Y','N') OR @ReadWriteFileGroups IS NULL OR (@ReadWriteFileGroups = 'Y' AND @BackupType = 'LOG')
988  BEGIN
989    SET @ErrorMessage = 'The value for the parameter @ReadWriteFileGroups is not supported.' + CHAR(13) + CHAR(10) + ' '
990    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
991    SET @Error = @@ERROR
992  END
993
994  IF @OverrideBackupPreference NOT IN('Y','N') OR @OverrideBackupPreference IS NULL
995  BEGIN
996    SET @ErrorMessage = 'The value for the parameter @OverrideBackupPreference is not supported.' + CHAR(13) + CHAR(10) + ' '
997    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
998    SET @Error = @@ERROR
999  END
1000
1001  IF @NoRecovery NOT IN('Y','N') OR @NoRecovery IS NULL OR (@NoRecovery = 'Y' AND @BackupType <> 'LOG') OR (@NoRecovery = 'Y' AND @BackupSoftware = 'SQLSAFE')
1002  BEGIN
1003    SET @ErrorMessage = 'The value for the parameter @NoRecovery is not supported.' + CHAR(13) + CHAR(10) + ' '
1004    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
1005    SET @Error = @@ERROR
1006  END
1007
1008  IF (@URL IS NOT NULL AND @Directory IS NOT NULL) OR (@URL IS NOT NULL AND @MirrorDirectory IS NOT NULL) OR (@URL IS NOT NULL AND @Version < 11.03339) OR (@URL IS NOT NULL AND @BackupSoftware IS NOT NULL) OR (@URL NOT LIKE 'https://%/%')
1009  BEGIN
1010    SET @ErrorMessage = 'The value for the parameter @URL is not supported.' + CHAR(13) + CHAR(10) + ' '
1011    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
1012    SET @Error = @@ERROR
1013  END
1014
1015  IF (@Credential IS NULL AND @URL IS NOT NULL) OR (@Credential IS NOT NULL AND @URL IS NULL) OR (@Credential IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.credentials WHERE name = @Credential))
1016  BEGIN
1017    SET @ErrorMessage = 'The value for the parameter @Credential is not supported.' + CHAR(13) + CHAR(10) + ' '
1018    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
1019    SET @Error = @@ERROR
1020  END
1021
1022  IF @MirrorCleanupTime < 0 OR (@MirrorCleanupTime IS NOT NULL AND @MirrorDirectory IS NULL)
1023  BEGIN
1024    SET @ErrorMessage = 'The value for the parameter @MirrorCleanupTime is not supported.' + CHAR(13) + CHAR(10) + ' '
1025    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
1026    SET @Error = @@ERROR
1027  END
1028
1029  IF @MirrorCleanupMode NOT IN('BEFORE_BACKUP','AFTER_BACKUP') OR @MirrorCleanupMode IS NULL
1030  BEGIN
1031    SET @ErrorMessage = 'The value for the parameter @MirrorCleanupMode is not supported.' + CHAR(13) + CHAR(10) + ' '
1032    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
1033    SET @Error = @@ERROR
1034  END
1035
1036  IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
1037  BEGIN
1038    SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '
1039    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
1040    SET @Error = @@ERROR
1041  END
1042
1043  IF @Execute NOT IN('Y','N') OR @Execute IS NULL
1044  BEGIN
1045    SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '
1046    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
1047    SET @Error = @@ERROR
1048  END
1049
1050  IF @Error <> 0
1051  BEGIN
1052    SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-backup.html.' + CHAR(13) + CHAR(10) + ' '
1053    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
1054    SET @ReturnCode = @Error
1055    GOTO Logging
1056  END
1057
1058  ----------------------------------------------------------------------------------------------------
1059  --// Check Availability Group cluster name                                                      //--
1060  ----------------------------------------------------------------------------------------------------
1061
1062  IF @Version >= 11
1063  BEGIN
1064    SELECT @Cluster = cluster_name
1065    FROM sys.dm_hadr_cluster
1066  END
1067
1068  ----------------------------------------------------------------------------------------------------
1069  --// Execute backup commands                                                                    //--
1070  ----------------------------------------------------------------------------------------------------
1071
1072  WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)
1073  BEGIN
1074
1075    SELECT TOP 1 @CurrentDBID = ID,
1076                 @CurrentDatabaseName = DatabaseName,
1077                 @CurrentDatabaseNameFS = DatabaseNameFS
1078    FROM @tmpDatabases
1079    WHERE Selected = 1
1080    AND Completed = 0
1081    ORDER BY ID ASC
1082
1083    SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)
1084
1085    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
1086    BEGIN
1087      IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)
1088      BEGIN
1089        SET @CurrentIsDatabaseAccessible = 1
1090      END
1091      ELSE
1092      BEGIN
1093        SET @CurrentIsDatabaseAccessible = 0
1094      END
1095    END
1096
1097    SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
1098    FROM sys.master_files
1099    WHERE database_id = @CurrentDatabaseID
1100    AND [type] = 0
1101    AND [file_id] = 1
1102
1103    -- Workaround for a bug in SQL Server 2005
1104    IF @Version >= 9 AND @Version < 10
1105    AND EXISTS(SELECT * FROM sys.master_files WHERE database_id = @CurrentDatabaseID AND [type] = 0 AND [file_id] = 1 AND differential_base_lsn IS NOT NULL AND differential_base_guid IS NOT NULL AND differential_base_time IS NULL)
1106    BEGIN
1107      SET @CurrentDifferentialBaseLSN = NULL
1108    END
1109
1110    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
1111    BEGIN
1112      SELECT @CurrentLogLSN = last_log_backup_lsn
1113      FROM sys.database_recovery_status
1114      WHERE database_id = @CurrentDatabaseID
1115    END
1116
1117    SET @CurrentBackupType = @BackupType
1118
1119    IF @ChangeBackupType = 'Y'
1120    BEGIN
1121      IF @CurrentBackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') <> 'SIMPLE' AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'
1122      BEGIN
1123        SET @CurrentBackupType = 'DIFF'
1124      END
1125      IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL AND @CurrentDatabaseName <> 'master'
1126      BEGIN
1127        SET @CurrentBackupType = 'FULL'
1128      END
1129    END
1130
1131    IF @CurrentBackupType = 'LOG' AND (@CleanupTime IS NOT NULL OR @MirrorCleanupTime IS NOT NULL)
1132    BEGIN
1133      SELECT @CurrentLatestBackup = MAX(backup_finish_date)
1134      FROM msdb.dbo.backupset
1135      WHERE ([type] IN('D','I')
1136      OR database_backup_lsn < @CurrentDifferentialBaseLSN)
1137      AND is_damaged = 0
1138      AND database_name = @CurrentDatabaseName
1139    END
1140
1141    IF @CurrentBackupType = 'DIFF'
1142    BEGIN
1143      SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot
1144      FROM msdb.dbo.backupset
1145      WHERE database_name = @CurrentDatabaseName
1146      AND [type] = 'D'
1147      AND checkpoint_lsn = @CurrentDifferentialBaseLSN
1148    END
1149
1150    IF @Version >= 11 AND @Cluster IS NOT NULL
1151    BEGIN
1152      SELECT @CurrentAvailabilityGroup = availability_groups.name,
1153             @CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc,
1154             @CurrentAvailabilityGroupBackupPreference = UPPER(availability_groups.automated_backup_preference_desc)
1155      FROM sys.databases databases
1156      INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
1157      INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
1158      INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
1159      WHERE databases.name = @CurrentDatabaseName
1160    END
1161
1162    IF @Version >= 11 AND @Cluster IS NOT NULL AND @CurrentAvailabilityGroup IS NOT NULL
1163    BEGIN
1164      SELECT @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)
1165    END
1166
1167    SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
1168    FROM sys.database_mirroring
1169    WHERE database_id = @CurrentDatabaseID
1170
1171    IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = @CurrentDatabaseName)
1172    BEGIN
1173      SET @CurrentLogShippingRole = 'PRIMARY'
1174    END
1175    ELSE
1176    IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondary_databases WHERE secondary_database = @CurrentDatabaseName)
1177    BEGIN
1178      SET @CurrentLogShippingRole = 'SECONDARY'
1179    END
1180
1181    -- Set database message
1182    SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
1183    SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)
1184    SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)
1185    SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
1186    SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)
1187    SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)
1188    IF @CurrentIsDatabaseAccessible IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
1189    SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)
1190    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group: ' + @CurrentAvailabilityGroup + CHAR(13) + CHAR(10)
1191    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group role: ' + @CurrentAvailabilityGroupRole + CHAR(13) + CHAR(10)
1192    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group backup preference: ' + @CurrentAvailabilityGroupBackupPreference + CHAR(13) + CHAR(10)
1193    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Is preferred backup replica: ' + CASE WHEN @CurrentIsPreferredBackupReplica = 1 THEN 'Yes' WHEN @CurrentIsPreferredBackupReplica = 0 THEN 'No' ELSE 'N/A' END + CHAR(13) + CHAR(10)
1194    IF @CurrentDatabaseMirroringRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Database mirroring role: ' + @CurrentDatabaseMirroringRole + CHAR(13) + CHAR(10)
1195    IF @CurrentLogShippingRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Log shipping role: ' + @CurrentLogShippingRole + CHAR(13) + CHAR(10)
1196    SET @DatabaseMessage = @DatabaseMessage + 'Differential base LSN: ' + ISNULL(CAST(@CurrentDifferentialBaseLSN AS nvarchar),'N/A') + CHAR(13) + CHAR(10)
1197    IF @CurrentBackupType = 'DIFF' SET @DatabaseMessage = @DatabaseMessage + 'Differential base is snapshot: ' + CASE WHEN @CurrentDifferentialBaseIsSnapshot = 1 THEN 'Yes' WHEN @CurrentDifferentialBaseIsSnapshot = 0 THEN 'No' ELSE 'N/A' END + CHAR(13) + CHAR(10)
1198    SET @DatabaseMessage = @DatabaseMessage + 'Last log backup LSN: ' + ISNULL(CAST(@CurrentLogLSN AS nvarchar),'N/A') + CHAR(13) + CHAR(10)
1199    SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%') + ' '
1200    RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
1201
1202    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
1203    AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
1204    AND DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 0
1205    AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') = 'SIMPLE' OR @CurrentLogLSN IS NULL))
1206    AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL)
1207    AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabaseName = 'master')
1208    AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'N' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
1209    AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'Y' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
1210    AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'DIFF' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
1211    AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'N' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
1212    AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'Y' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
1213    AND NOT ((@CurrentLogShippingRole = 'PRIMARY' AND @CurrentLogShippingRole IS NOT NULL) AND @CurrentBackupType = 'LOG')
1214    BEGIN
1215
1216      -- Set variables
1217      SET @CurrentDate = GETDATE()
1218
1219      INSERT INTO @CurrentCleanupDates (CleanupDate)
1220      SELECT @CurrentDate
1221
1222      IF @CurrentBackupType = 'LOG'
1223      BEGIN
1224        INSERT INTO @CurrentCleanupDates (CleanupDate)
1225        SELECT @CurrentLatestBackup
1226      END
1227
1228      SELECT @CurrentFileExtension = CASE
1229      WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'FULL' THEN 'bak'
1230      WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'DIFF' THEN 'bak'
1231      WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'LOG' THEN 'trn'
1232      WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'FULL' THEN 'bak'
1233      WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'DIFF' THEN 'bak'
1234      WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'LOG' THEN 'trn'
1235      WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'FULL' THEN 'sqb'
1236      WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'DIFF' THEN 'sqb'
1237      WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'LOG' THEN 'sqb'
1238      WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'FULL' THEN 'safe'
1239      WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'DIFF' THEN 'safe'
1240      WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'LOG' THEN 'safe'
1241      END
1242
1243      INSERT INTO @CurrentDirectories (ID, DirectoryPath, Mirror, DirectoryNumber, CreateCompleted, CleanupCompleted)
1244      SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '\' + @CurrentDatabaseNameFS + '\' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, Mirror, ROW_NUMBER() OVER (PARTITION BY Mirror ORDER BY ID ASC), 0, 0
1245      FROM @Directories
1246      ORDER BY ID ASC
1247
1248      IF EXISTS (SELECT * FROM @CurrentDirectories WHERE Mirror = 0)
1249      BEGIN
1250        SET @CurrentFileNumber = 0
1251
1252        WHILE @CurrentFileNumber < @NumberOfFiles
1253        BEGIN
1254          SET @CurrentFileNumber = @CurrentFileNumber + 1
1255
1256          SELECT @CurrentDirectoryPath = DirectoryPath
1257          FROM @CurrentDirectories
1258          WHERE @CurrentFileNumber >= (DirectoryNumber - 1) * (SELECT @NumberOfFiles / COUNT(*) FROM @CurrentDirectories WHERE Mirror = 0) + 1
1259          AND @CurrentFileNumber <= DirectoryNumber * (SELECT @NumberOfFiles / COUNT(*) FROM @CurrentDirectories WHERE Mirror = 0)
1260          AND Mirror = 0
1261
1262          SET @CurrentFilePath = @CurrentDirectoryPath + '\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '.' + @CurrentFileExtension
1263
1264          IF LEN(@CurrentFilePath) > 259
1265          BEGIN
1266            SET @CurrentFilePath = @CurrentDirectoryPath + '\' + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '.' + @CurrentFileExtension
1267          END
1268
1269          IF LEN(@CurrentFilePath) > 259
1270          BEGIN
1271            SET @CurrentFilePath = @CurrentDirectoryPath + '\' + LEFT(@CurrentDatabaseNameFS,CASE WHEN (LEN(@CurrentDatabaseNameFS) + 259 - LEN(@CurrentFilePath) - 3) < 20 THEN 20 ELSE (LEN(@CurrentDatabaseNameFS) + 259 - LEN(@CurrentFilePath) - 3) END) + '...' + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '.' + @CurrentFileExtension
1272          END
1273
1274          INSERT INTO @CurrentFiles ([Type], FilePath, Mirror)
1275          SELECT 'DISK', @CurrentFilePath, 0
1276
1277          SET @CurrentDirectoryPath = NULL
1278          SET @CurrentFilePath = NULL
1279        END
1280
1281        INSERT INTO @CurrentBackupSet (Mirror, VerifyCompleted)
1282        SELECT 0, 0
1283      END
1284      ELSE
1285      IF @URL IS NOT NULL
1286      BEGIN
1287        SET @CurrentFilePath = @URL + CASE WHEN RIGHT(@URL,1) = '/' THEN '' ELSE '/' END + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + '.' + @CurrentFileExtension
1288
1289        IF LEN(@CurrentFilePath) > 259
1290        BEGIN
1291          SET @CurrentFilePath = @URL + CASE WHEN RIGHT(@URL,1) = '/' THEN '' ELSE '/' END + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + '.' + @CurrentFileExtension
1292        END
1293
1294        IF LEN(@CurrentFilePath) > 259
1295        BEGIN
1296          SET @CurrentFilePath = @URL + CASE WHEN RIGHT(@URL,1) = '/' THEN '' ELSE '/' END + LEFT(@CurrentDatabaseNameFS,CASE WHEN (LEN(@CurrentDatabaseNameFS) + 259 - LEN(@CurrentFilePath) - 3) < 20 THEN 20 ELSE (LEN(@CurrentDatabaseNameFS) + 259 - LEN(@CurrentFilePath) - 3) END) + '...' + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + '.' + @CurrentFileExtension
1297        END
1298
1299        INSERT INTO @CurrentFiles ([Type], FilePath, Mirror)
1300        SELECT 'URL', @CurrentFilePath, 0
1301
1302        INSERT INTO @CurrentBackupSet (Mirror, VerifyCompleted)
1303        SELECT 0, 0
1304      END
1305
1306      IF EXISTS (SELECT * FROM @CurrentDirectories WHERE Mirror = 1)
1307      BEGIN
1308        SET @CurrentFileNumber = 0
1309
1310        WHILE @CurrentFileNumber < @NumberOfFiles
1311        BEGIN
1312          SET @CurrentFileNumber = @CurrentFileNumber + 1
1313
1314          SELECT @CurrentDirectoryPath = DirectoryPath
1315          FROM @CurrentDirectories
1316          WHERE @CurrentFileNumber >= (DirectoryNumber - 1) * (SELECT @NumberOfFiles / COUNT(*) FROM @CurrentDirectories WHERE Mirror = 1) + 1
1317          AND @CurrentFileNumber <= DirectoryNumber * (SELECT @NumberOfFiles / COUNT(*) FROM @CurrentDirectories WHERE Mirror = 1)
1318          AND Mirror = 1
1319
1320          SET @CurrentFilePath = @CurrentDirectoryPath + '\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '.' + @CurrentFileExtension
1321
1322          IF LEN(@CurrentFilePath) > 259
1323          BEGIN
1324            SET @CurrentFilePath = @CurrentDirectoryPath + '\' + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '.' + @CurrentFileExtension
1325          END
1326
1327          IF LEN(@CurrentFilePath) > 259
1328          BEGIN
1329            SET @CurrentFilePath = @CurrentDirectoryPath + '\' + LEFT(@CurrentDatabaseNameFS,CASE WHEN (LEN(@CurrentDatabaseNameFS) + 259 - LEN(@CurrentFilePath) - 3) < 20 THEN 20 ELSE (LEN(@CurrentDatabaseNameFS) + 259 - LEN(@CurrentFilePath) - 3) END) + '...' + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '.' + @CurrentFileExtension
1330          END
1331
1332          INSERT INTO @CurrentFiles ([Type], FilePath, Mirror)
1333          SELECT 'DISK', @CurrentFilePath, 1
1334
1335          SET @CurrentDirectoryPath = NULL
1336          SET @CurrentFilePath = NULL
1337        END
1338
1339        INSERT INTO @CurrentBackupSet (Mirror, VerifyCompleted)
1340        SELECT 1, 0
1341      END
1342
1343      -- Create directory
1344      WHILE EXISTS (SELECT * FROM @CurrentDirectories WHERE CreateCompleted = 0)
1345      BEGIN
1346        SELECT TOP 1 @CurrentDirectoryID = ID,
1347                     @CurrentDirectoryPath = DirectoryPath
1348        FROM @CurrentDirectories
1349        WHERE CreateCompleted = 0
1350        ORDER BY ID ASC
1351
1352        SET @CurrentCommandType01 = 'xp_create_subdir'
1353        SET @CurrentCommand01 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''' IF @ReturnCode <> 0 RAISERROR(''Error creating directory.'', 16, 1)'
1354        EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01, @CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
1355        SET @Error = @@ERROR
1356        IF @Error <> 0 SET @CurrentCommandOutput01 = @Error
1357        IF @CurrentCommandOutput01 <> 0 SET @ReturnCode = @CurrentCommandOutput01
1358
1359        UPDATE @CurrentDirectories
1360        SET CreateCompleted = 1,
1361            CreateOutput = @CurrentCommandOutput01
1362        WHERE ID = @CurrentDirectoryID
1363
1364        SET @CurrentDirectoryID = NULL
1365        SET @CurrentDirectoryPath = NULL
1366
1367        SET @CurrentCommand01 = NULL
1368
1369        SET @CurrentCommandOutput01 = NULL
1370
1371        SET @CurrentCommandType01 = NULL
1372      END
1373
1374      IF @CleanupMode = 'BEFORE_BACKUP'
1375      BEGIN
1376        INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
1377        SELECT DATEADD(hh,-(@CleanupTime),GETDATE()), 0
1378
1379        IF NOT EXISTS(SELECT * FROM @CurrentCleanupDates WHERE (Mirror = 0 OR Mirror IS NULL) AND CleanupDate IS NULL)
1380        BEGIN
1381          UPDATE @CurrentDirectories
1382          SET CleanupDate = (SELECT MIN(CleanupDate)
1383                             FROM @CurrentCleanupDates
1384                             WHERE (Mirror = 0 OR Mirror IS NULL)),
1385              CleanupMode = 'BEFORE_BACKUP'
1386          WHERE Mirror = 0
1387        END
1388      END
1389
1390      IF @MirrorCleanupMode = 'BEFORE_BACKUP'
1391      BEGIN
1392        INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
1393        SELECT DATEADD(hh,-(@MirrorCleanupTime),GETDATE()), 1
1394
1395        IF NOT EXISTS(SELECT * FROM @CurrentCleanupDates WHERE (Mirror = 1 OR Mirror IS NULL) AND CleanupDate IS NULL)
1396        BEGIN
1397          UPDATE @CurrentDirectories
1398          SET CleanupDate = (SELECT MIN(CleanupDate)
1399                             FROM @CurrentCleanupDates
1400                             WHERE (Mirror = 1 OR Mirror IS NULL)),
1401              CleanupMode = 'BEFORE_BACKUP'
1402          WHERE Mirror = 1
1403        END
1404      END
1405
1406      -- Delete old backup files, before backup
1407      IF NOT EXISTS (SELECT * FROM @CurrentDirectories WHERE CreateOutput <> 0 OR CreateOutput IS NULL)
1408      AND @CurrentBackupType = @BackupType
1409      BEGIN
1410        WHILE EXISTS (SELECT * FROM @CurrentDirectories WHERE CleanupDate IS NOT NULL AND CleanupMode = 'BEFORE_BACKUP' AND CleanupCompleted = 0)
1411        BEGIN
1412          SELECT TOP 1 @CurrentDirectoryID = ID,
1413                       @CurrentDirectoryPath = DirectoryPath,
1414                       @CurrentCleanupDate = CleanupDate
1415          FROM @CurrentDirectories
1416          WHERE CleanupDate IS NOT NULL
1417          AND CleanupCompleted = 0
1418          ORDER BY ID ASC
1419
1420          IF @BackupSoftware IS NULL
1421          BEGIN
1422            SET @CurrentCommandType02 = 'xp_delete_file'
1423
1424            SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', ''' + @CurrentFileExtension + ''', ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' IF @ReturnCode <> 0 RAISERROR(''Error deleting files.'', 16, 1)'
1425          END
1426
1427          IF @BackupSoftware = 'LITESPEED'
1428          BEGIN
1429            SET @CurrentCommandType02 = 'xp_slssqlmaint'
1430
1431            SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_slssqlmaint N''-MAINTDEL -DELFOLDER "' + REPLACE(@CurrentDirectoryPath,'''','''''') + '" -DELEXTENSION "' + @CurrentFileExtension + '" -DELUNIT "' + CAST(DATEDIFF(mi,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + '" -DELUNITTYPE "minutes" -DELUSEAGE'' IF @ReturnCode <> 0 RAISERROR(''Error deleting LiteSpeed backup files.'', 16, 1)'
1432          END
1433
1434          IF @BackupSoftware = 'SQLBACKUP'
1435          BEGIN
1436            SET @CurrentCommandType02 = 'sqbutility'
1437
1438            SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqbutility 1032, N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''', N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', ''' + CASE WHEN @CurrentBackupType = 'FULL' THEN 'D' WHEN @CurrentBackupType = 'DIFF' THEN 'I' WHEN @CurrentBackupType = 'LOG' THEN 'L' END + ''', ''' + CAST(DATEDIFF(hh,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + 'h'', ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL') + ' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLBackup backup files.'', 16, 1)'
1439          END
1440
1441          IF @BackupSoftware = 'SQLSAFE'
1442          BEGIN
1443            SET @CurrentCommandType02 = 'xp_ss_delete'
1444
1445            SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_delete @filename = N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + '\*.' + @CurrentFileExtension + ''', @age = ''' + CAST(DATEDIFF(mi,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + 'Minutes'' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLsafe backup files.'', 16, 1)'
1446          END
1447
1448          EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @Command = @CurrentCommand02, @CommandType = @CurrentCommandType02, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
1449          SET @Error = @@ERROR
1450          IF @Error <> 0 SET @CurrentCommandOutput02 = @Error
1451          IF @CurrentCommandOutput02 <> 0 SET @ReturnCode = @CurrentCommandOutput02
1452
1453          UPDATE @CurrentDirectories
1454          SET CleanupCompleted = 1,
1455              CleanupOutput = @CurrentCommandOutput02
1456          WHERE ID = @CurrentDirectoryID
1457
1458          SET @CurrentDirectoryID = NULL
1459          SET @CurrentDirectoryPath = NULL
1460          SET @CurrentCleanupDate = NULL
1461
1462          SET @CurrentCommand02 = NULL
1463
1464          SET @CurrentCommandOutput02 = NULL
1465
1466          SET @CurrentCommandType02 = NULL
1467        END
1468      END
1469
1470      -- Perform a backup
1471      IF NOT EXISTS (SELECT * FROM @CurrentDirectories WHERE CreateOutput <> 0 OR CreateOutput IS NULL)
1472      BEGIN
1473        IF @BackupSoftware IS NULL
1474        BEGIN
1475          SELECT @CurrentCommandType03 = CASE
1476          WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP_DATABASE'
1477          WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP_LOG'
1478          END
1479
1480          SELECT @CurrentCommand03 = CASE
1481          WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName)
1482          WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName)
1483          END
1484
1485          IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand03 = @CurrentCommand03 + ' READ_WRITE_FILEGROUPS'
1486
1487          SET @CurrentCommand03 = @CurrentCommand03 + ' TO'
1488
1489          SELECT @CurrentCommand03 = @CurrentCommand03 + ' ' + [Type] + ' = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
1490          FROM @CurrentFiles
1491          WHERE Mirror = 0
1492          ORDER BY FilePath ASC
1493
1494          IF EXISTS(SELECT * FROM @CurrentFiles WHERE Mirror = 1)
1495          BEGIN
1496            SET @CurrentCommand03 = @CurrentCommand03 + ' MIRROR TO'
1497
1498            SELECT @CurrentCommand03 = @CurrentCommand03 + ' ' + [Type] + ' = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
1499            FROM @CurrentFiles
1500            WHERE Mirror = 1
1501            ORDER BY FilePath ASC
1502          END
1503
1504          SET @CurrentCommand03 = @CurrentCommand03 + ' WITH '
1505          IF @CheckSum = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + 'CHECKSUM'
1506          IF @CheckSum = 'N' SET @CurrentCommand03 = @CurrentCommand03 + 'NO_CHECKSUM'
1507          IF @Compress = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + ', COMPRESSION'
1508          IF @Compress = 'N' AND @Version >= 10 SET @CurrentCommand03 = @CurrentCommand03 + ', NO_COMPRESSION'
1509          IF @CurrentBackupType = 'DIFF' SET @CurrentCommand03 = @CurrentCommand03 + ', DIFFERENTIAL'
1510
1511          IF EXISTS(SELECT * FROM @CurrentFiles WHERE Mirror = 1)
1512          BEGIN
1513            SET @CurrentCommand03 = @CurrentCommand03 + ', FORMAT'
1514          END
1515
1516          IF @CopyOnly = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + ', COPY_ONLY'
1517          IF @NoRecovery = 'Y' AND @CurrentBackupType = 'LOG' SET @CurrentCommand03 = @CurrentCommand03 + ', NORECOVERY'
1518          IF @BlockSize IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)
1519          IF @BufferCount IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', BUFFERCOUNT = ' + CAST(@BufferCount AS nvarchar)
1520          IF @MaxTransferSize IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', MAXTRANSFERSIZE = ' + CAST(@MaxTransferSize AS nvarchar)
1521          IF @Description IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''
1522          IF @Encrypt = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + ', ENCRYPTION (ALGORITHM = ' + UPPER(@EncryptionAlgorithm) + ', '
1523          IF @Encrypt = 'Y' AND @ServerCertificate IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + 'SERVER CERTIFICATE = ' + QUOTENAME(@ServerCertificate)
1524          IF @Encrypt = 'Y' AND @ServerAsymmetricKey IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + 'SERVER ASYMMETRIC KEY = ' + QUOTENAME(@ServerAsymmetricKey)
1525          IF @Encrypt = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + ')'
1526          IF @URL IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', CREDENTIAL = N''' + REPLACE(@Credential,'''','''''') + ''''
1527        END
1528
1529        IF @BackupSoftware = 'LITESPEED'
1530        BEGIN
1531          SELECT @CurrentCommandType03 = CASE
1532          WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'xp_backup_database'
1533          WHEN @CurrentBackupType = 'LOG' THEN 'xp_backup_log'
1534          END
1535
1536          SELECT @CurrentCommand03 = CASE
1537          WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_backup_database @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
1538          WHEN @CurrentBackupType = 'LOG' THEN 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_backup_log @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
1539          END
1540
1541          SELECT @CurrentCommand03 = @CurrentCommand03 + ', @filename = N''' + REPLACE(FilePath,'''','''''') + ''''
1542          FROM @CurrentFiles
1543          WHERE Mirror = 0
1544          ORDER BY FilePath ASC
1545
1546          IF EXISTS(SELECT * FROM @CurrentFiles WHERE Mirror = 1)
1547          BEGIN
1548            SELECT @CurrentCommand03 = @CurrentCommand03 + ', @mirror = N''' + REPLACE(FilePath,'''','''''') + ''''
1549            FROM @CurrentFiles
1550            WHERE Mirror = 1
1551            ORDER BY FilePath ASC
1552          END
1553
1554          SET @CurrentCommand03 = @CurrentCommand03 + ', @with = '''
1555          IF @CheckSum = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + 'CHECKSUM'
1556          IF @CheckSum = 'N' SET @CurrentCommand03 = @CurrentCommand03 + 'NO_CHECKSUM'
1557          IF @CurrentBackupType = 'DIFF' SET @CurrentCommand03 = @CurrentCommand03 + ', DIFFERENTIAL'
1558          IF @CopyOnly = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + ', COPY_ONLY'
1559          IF @NoRecovery = 'Y' AND @CurrentBackupType = 'LOG' SET @CurrentCommand03 = @CurrentCommand03 + ', NORECOVERY'
1560          IF @BlockSize IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)
1561          SET @CurrentCommand03 = @CurrentCommand03 + ''''
1562          IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand03 = @CurrentCommand03 + ', @read_write_filegroups = 1'
1563          IF @CompressionLevel IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @compressionlevel = ' + CAST(@CompressionLevel AS nvarchar)
1564          IF @BufferCount IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @buffercount = ' + CAST(@BufferCount AS nvarchar)
1565          IF @MaxTransferSize IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @maxtransfersize = ' + CAST(@MaxTransferSize AS nvarchar)
1566          IF @Threads IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @threads = ' + CAST(@Threads AS nvarchar)
1567          IF @Throttle IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @throttle = ' + CAST(@Throttle AS nvarchar)
1568          IF @Description IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @desc = N''' + REPLACE(@Description,'''','''''') + ''''
1569
1570          IF @EncryptionAlgorithm IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @cryptlevel = ' + CASE
1571          WHEN @EncryptionAlgorithm = 'RC2_40' THEN '0'
1572          WHEN @EncryptionAlgorithm = 'RC2_56' THEN '1'
1573          WHEN @EncryptionAlgorithm = 'RC2_112' THEN '2'
1574          WHEN @EncryptionAlgorithm = 'RC2_128' THEN '3'
1575          WHEN @EncryptionAlgorithm = 'TRIPLE_DES_3KEY' THEN '4'
1576          WHEN @EncryptionAlgorithm = 'RC4_128' THEN '5'
1577          WHEN @EncryptionAlgorithm = 'AES_128' THEN '6'
1578          WHEN @EncryptionAlgorithm = 'AES_192' THEN '7'
1579          WHEN @EncryptionAlgorithm = 'AES_256' THEN '8'
1580          END
1581
1582          IF @EncryptionKey IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @encryptionkey = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
1583          SET @CurrentCommand03 = @CurrentCommand03 + ' IF @ReturnCode <> 0 RAISERROR(''Error performing LiteSpeed backup.'', 16, 1)'
1584        END
1585
1586        IF @BackupSoftware = 'SQLBACKUP'
1587        BEGIN
1588          SET @CurrentCommandType03 = 'sqlbackup'
1589
1590          SELECT @CurrentCommand03 = CASE
1591          WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName)
1592          WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName)
1593          END
1594
1595          IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand03 = @CurrentCommand03 + ' READ_WRITE_FILEGROUPS'
1596
1597          SET @CurrentCommand03 = @CurrentCommand03 + ' TO'
1598
1599          SELECT @CurrentCommand03 = @CurrentCommand03 + ' DISK = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
1600          FROM @CurrentFiles
1601          WHERE Mirror = 0
1602          ORDER BY FilePath ASC
1603
1604          SET @CurrentCommand03 = @CurrentCommand03 + ' WITH '
1605
1606          IF EXISTS(SELECT * FROM @CurrentFiles WHERE Mirror = 1)
1607          BEGIN
1608            SET @CurrentCommand03 = @CurrentCommand03 + ' MIRRORFILE' + ' = N''' + REPLACE((SELECT FilePath FROM @CurrentFiles WHERE Mirror = 1),'''','''''') + ''', '
1609          END
1610
1611          IF @CheckSum = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + 'CHECKSUM'
1612          IF @CheckSum = 'N' SET @CurrentCommand03 = @CurrentCommand03 + 'NO_CHECKSUM'
1613          IF @CurrentBackupType = 'DIFF' SET @CurrentCommand03 = @CurrentCommand03 + ', DIFFERENTIAL'
1614          IF @CopyOnly = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + ', COPY_ONLY'
1615          IF @NoRecovery = 'Y' AND @CurrentBackupType = 'LOG' SET @CurrentCommand03 = @CurrentCommand03 + ', NORECOVERY'
1616          IF @CompressionLevel IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', COMPRESSION = ' + CAST(@CompressionLevel AS nvarchar)
1617          IF @Threads IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', THREADCOUNT = ' + CAST(@Threads AS nvarchar)
1618          IF @MaxTransferSize IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', MAXTRANSFERSIZE = ' + CAST(@MaxTransferSize AS nvarchar)
1619          IF @Description IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''
1620
1621          IF @EncryptionAlgorithm IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', KEYSIZE = ' + CASE
1622          WHEN @EncryptionAlgorithm = 'AES_128' THEN '128'
1623          WHEN @EncryptionAlgorithm = 'AES_256' THEN '256'
1624          END
1625
1626          IF @EncryptionKey IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', PASSWORD = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
1627          SET @CurrentCommand03 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqlbackup N''-SQL "' + REPLACE(@CurrentCommand03,'''','''''') + '"''' + ' IF @ReturnCode <> 0 RAISERROR(''Error performing SQLBackup backup.'', 16, 1)'
1628        END
1629
1630        IF @BackupSoftware = 'SQLSAFE'
1631        BEGIN
1632          SET @CurrentCommandType03 = 'xp_ss_backup'
1633
1634          SET @CurrentCommand03 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_backup @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
1635
1636          SELECT @CurrentCommand03 = @CurrentCommand03 + ', ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) = 1 THEN '@filename' ELSE '@backupfile' END + ' = N''' + REPLACE(FilePath,'''','''''') + ''''
1637          FROM @CurrentFiles
1638          WHERE Mirror = 0
1639          ORDER BY FilePath ASC
1640
1641          SELECT @CurrentCommand03 = @CurrentCommand03 + ', @mirrorfile = N''' + REPLACE(FilePath,'''','''''') + ''''
1642          FROM @CurrentFiles
1643          WHERE Mirror = 1
1644          ORDER BY FilePath ASC
1645
1646          SET @CurrentCommand03 = @CurrentCommand03 + ', @backuptype = ' + CASE WHEN @CurrentBackupType = 'FULL' THEN '''Full''' WHEN @CurrentBackupType = 'DIFF' THEN '''Differential''' WHEN @CurrentBackupType = 'LOG' THEN '''Log''' END
1647          IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand03 = @CurrentCommand03 + ', @readwritefilegroups = 1'
1648          SET @CurrentCommand03 = @CurrentCommand03 + ', @checksum = ' + CASE WHEN @CheckSum = 'Y' THEN '1' WHEN @CheckSum = 'N' THEN '0' END
1649          SET @CurrentCommand03 = @CurrentCommand03 + ', @copyonly = ' + CASE WHEN @CopyOnly = 'Y' THEN '1' WHEN @CopyOnly = 'N' THEN '0' END
1650          IF @CompressionLevel IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @compressionlevel = ' + CAST(@CompressionLevel AS nvarchar)
1651          IF @Threads IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @threads = ' + CAST(@Threads AS nvarchar)
1652          IF @Description IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @desc = N''' + REPLACE(@Description,'''','''''') + ''''
1653
1654          IF @EncryptionAlgorithm IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @encryptiontype = N''' + CASE
1655          WHEN @EncryptionAlgorithm = 'AES_128' THEN 'AES128'
1656          WHEN @EncryptionAlgorithm = 'AES_256' THEN 'AES256'
1657          END + ''''
1658
1659          IF @EncryptionKey IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @encryptedbackuppassword = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
1660          SET @CurrentCommand03 = @CurrentCommand03 + ' IF @ReturnCode <> 0 RAISERROR(''Error performing SQLsafe backup.'', 16, 1)'
1661        END
1662
1663        EXECUTE @CurrentCommandOutput03 = [dbo].[CommandExecute] @Command = @CurrentCommand03, @CommandType = @CurrentCommandType03, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
1664        SET @Error = @@ERROR
1665        IF @Error <> 0 SET @CurrentCommandOutput03 = @Error
1666        IF @CurrentCommandOutput03 <> 0 SET @ReturnCode = @CurrentCommandOutput03
1667      END
1668
1669      -- Verify the backup
1670      IF @CurrentCommandOutput03 = 0 AND @Verify = 'Y'
1671      BEGIN
1672      WHILE EXISTS (SELECT * FROM @CurrentBackupSet WHERE VerifyCompleted = 0)
1673        BEGIN
1674          SELECT TOP 1 @CurrentBackupSetID = ID,
1675                       @CurrentIsMirror = Mirror
1676          FROM @CurrentBackupSet
1677          WHERE VerifyCompleted = 0
1678          ORDER BY ID ASC
1679
1680          IF @BackupSoftware IS NULL
1681          BEGIN
1682            SET @CurrentCommandType04 = 'RESTORE_VERIFYONLY'
1683
1684            SET @CurrentCommand04 = 'RESTORE VERIFYONLY FROM'
1685
1686            SELECT @CurrentCommand04 = @CurrentCommand04 + ' ' + [Type] + ' = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
1687            FROM @CurrentFiles
1688            WHERE Mirror = @CurrentIsMirror
1689            ORDER BY FilePath ASC
1690
1691            SET @CurrentCommand04 = @CurrentCommand04 + ' WITH '
1692            IF @CheckSum = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + 'CHECKSUM'
1693            IF @CheckSum = 'N' SET @CurrentCommand04 = @CurrentCommand04 + 'NO_CHECKSUM'
1694            IF @URL IS NOT NULL SET @CurrentCommand04 = @CurrentCommand04 + ', CREDENTIAL = N''' + REPLACE(@Credential,'''','''''') + ''''
1695          END
1696
1697          IF @BackupSoftware = 'LITESPEED'
1698          BEGIN
1699            SET @CurrentCommandType04 = 'xp_restore_verifyonly'
1700
1701            SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_restore_verifyonly'
1702
1703            SELECT @CurrentCommand04 = @CurrentCommand04 + ' @filename = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
1704            FROM @CurrentFiles
1705            WHERE Mirror = @CurrentIsMirror
1706            ORDER BY FilePath ASC
1707
1708            SET @CurrentCommand04 = @CurrentCommand04 + ', @with = '''
1709            IF @CheckSum = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + 'CHECKSUM'
1710            IF @CheckSum = 'N' SET @CurrentCommand04 = @CurrentCommand04 + 'NO_CHECKSUM'
1711            SET @CurrentCommand04 = @CurrentCommand04 + ''''
1712            IF @EncryptionKey IS NOT NULL SET @CurrentCommand04 = @CurrentCommand04 + ', @encryptionkey = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
1713
1714            SET @CurrentCommand04 = @CurrentCommand04 + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying LiteSpeed backup.'', 16, 1)'
1715          END
1716
1717          IF @BackupSoftware = 'SQLBACKUP'
1718          BEGIN
1719            SET @CurrentCommandType04 = 'sqlbackup'
1720
1721            SET @CurrentCommand04 = 'RESTORE VERIFYONLY FROM'
1722
1723            SELECT @CurrentCommand04 = @CurrentCommand04 + ' DISK = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
1724            FROM @CurrentFiles
1725            WHERE Mirror = @CurrentIsMirror
1726            ORDER BY FilePath ASC
1727
1728            SET @CurrentCommand04 = @CurrentCommand04 + ' WITH '
1729            IF @CheckSum = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + 'CHECKSUM'
1730            IF @CheckSum = 'N' SET @CurrentCommand04 = @CurrentCommand04 + 'NO_CHECKSUM'
1731            IF @EncryptionKey IS NOT NULL SET @CurrentCommand04 = @CurrentCommand04 + ', PASSWORD = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
1732
1733            SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqlbackup N''-SQL "' + REPLACE(@CurrentCommand04,'''','''''') + '"''' + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying SQLBackup backup.'', 16, 1)'
1734          END
1735
1736          IF @BackupSoftware = 'SQLSAFE'
1737          BEGIN
1738            SET @CurrentCommandType04 = 'xp_ss_verify'
1739
1740            SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_verify @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
1741
1742            SELECT @CurrentCommand04 = @CurrentCommand04 + ', ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) = 1 THEN '@filename' ELSE '@backupfile' END + ' = N''' + REPLACE(FilePath,'''','''''') + ''''
1743            FROM @CurrentFiles
1744            WHERE Mirror = @CurrentIsMirror
1745            ORDER BY FilePath ASC
1746
1747            SET @CurrentCommand04 = @CurrentCommand04 + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying SQLsafe backup.'', 16, 1)'
1748          END
1749
1750          EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @Command = @CurrentCommand04, @CommandType = @CurrentCommandType04, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
1751          SET @Error = @@ERROR
1752          IF @Error <> 0 SET @CurrentCommandOutput04 = @Error
1753          IF @CurrentCommandOutput04 <> 0 SET @ReturnCode = @CurrentCommandOutput04
1754
1755          UPDATE @CurrentBackupSet
1756          SET VerifyCompleted = 1,
1757              VerifyOutput = @CurrentCommandOutput04
1758          WHERE ID = @CurrentBackupSetID
1759
1760          SET @CurrentBackupSetID = NULL
1761          SET @CurrentIsMirror = NULL
1762
1763          SET @CurrentCommand04 = NULL
1764
1765          SET @CurrentCommandOutput04 = NULL
1766
1767          SET @CurrentCommandType04 = NULL
1768        END
1769      END
1770
1771      IF @CleanupMode = 'AFTER_BACKUP'
1772      BEGIN
1773        INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
1774        SELECT DATEADD(hh,-(@CleanupTime),GETDATE()), 0
1775
1776        IF NOT EXISTS(SELECT * FROM @CurrentCleanupDates WHERE (Mirror = 0 OR Mirror IS NULL) AND CleanupDate IS NULL)
1777        BEGIN
1778          UPDATE @CurrentDirectories
1779          SET CleanupDate = (SELECT MIN(CleanupDate)
1780                             FROM @CurrentCleanupDates
1781                             WHERE (Mirror = 0 OR Mirror IS NULL)),
1782              CleanupMode = 'AFTER_BACKUP'
1783          WHERE Mirror = 0
1784        END
1785      END
1786
1787      IF @MirrorCleanupMode = 'AFTER_BACKUP'
1788      BEGIN
1789        INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
1790        SELECT DATEADD(hh,-(@MirrorCleanupTime),GETDATE()), 1
1791
1792        IF NOT EXISTS(SELECT * FROM @CurrentCleanupDates WHERE (Mirror = 1 OR Mirror IS NULL) AND CleanupDate IS NULL)
1793        BEGIN
1794          UPDATE @CurrentDirectories
1795          SET CleanupDate = (SELECT MIN(CleanupDate)
1796                             FROM @CurrentCleanupDates
1797                             WHERE (Mirror = 1 OR Mirror IS NULL)),
1798              CleanupMode = 'AFTER_BACKUP'
1799          WHERE Mirror = 1
1800        END
1801      END
1802
1803      -- Delete old backup files, after backup
1804      IF ((@CurrentCommandOutput03 = 0 AND @Verify = 'N')
1805      OR (@CurrentCommandOutput03 = 0 AND @Verify = 'Y' AND NOT EXISTS (SELECT * FROM @CurrentBackupSet WHERE VerifyOutput <> 0 OR VerifyOutput IS NULL)))
1806      AND @CurrentBackupType = @BackupType
1807      BEGIN
1808        WHILE EXISTS (SELECT * FROM @CurrentDirectories WHERE CleanupDate IS NOT NULL AND CleanupMode = 'AFTER_BACKUP' AND CleanupCompleted = 0)
1809        BEGIN
1810          SELECT TOP 1 @CurrentDirectoryID = ID,
1811                       @CurrentDirectoryPath = DirectoryPath,
1812                       @CurrentCleanupDate = CleanupDate
1813          FROM @CurrentDirectories
1814          WHERE CleanupDate IS NOT NULL
1815          AND CleanupCompleted = 0
1816          ORDER BY ID ASC
1817
1818          IF @BackupSoftware IS NULL
1819          BEGIN
1820            SET @CurrentCommandType05 = 'xp_delete_file'
1821
1822            SET @CurrentCommand05 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', ''' + @CurrentFileExtension + ''', ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' IF @ReturnCode <> 0 RAISERROR(''Error deleting files.'', 16, 1)'
1823          END
1824
1825          IF @BackupSoftware = 'LITESPEED'
1826          BEGIN
1827            SET @CurrentCommandType05 = 'xp_slssqlmaint'
1828
1829            SET @CurrentCommand05 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_slssqlmaint N''-MAINTDEL -DELFOLDER "' + REPLACE(@CurrentDirectoryPath,'''','''''') + '" -DELEXTENSION "' + @CurrentFileExtension + '" -DELUNIT "' + CAST(DATEDIFF(mi,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + '" -DELUNITTYPE "minutes" -DELUSEAGE'' IF @ReturnCode <> 0 RAISERROR(''Error deleting LiteSpeed backup files.'', 16, 1)'
1830          END
1831
1832          IF @BackupSoftware = 'SQLBACKUP'
1833          BEGIN
1834            SET @CurrentCommandType05 = 'sqbutility'
1835
1836            SET @CurrentCommand05 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqbutility 1032, N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''', N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', ''' + CASE WHEN @CurrentBackupType = 'FULL' THEN 'D' WHEN @CurrentBackupType = 'DIFF' THEN 'I' WHEN @CurrentBackupType = 'LOG' THEN 'L' END + ''', ''' + CAST(DATEDIFF(hh,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + 'h'', ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL') + ' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLBackup backup files.'', 16, 1)'
1837          END
1838
1839          IF @BackupSoftware = 'SQLSAFE'
1840          BEGIN
1841            SET @CurrentCommandType05 = 'xp_ss_delete'
1842
1843            SET @CurrentCommand05 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_delete @filename = N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + '\*.' + @CurrentFileExtension + ''', @age = ''' + CAST(DATEDIFF(mi,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + 'Minutes'' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLsafe backup files.'', 16, 1)'
1844          END
1845
1846          EXECUTE @CurrentCommandOutput05 = [dbo].[CommandExecute] @Command = @CurrentCommand05, @CommandType = @CurrentCommandType05, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
1847          SET @Error = @@ERROR
1848          IF @Error <> 0 SET @CurrentCommandOutput05 = @Error
1849          IF @CurrentCommandOutput05 <> 0 SET @ReturnCode = @CurrentCommandOutput05
1850
1851          UPDATE @CurrentDirectories
1852          SET CleanupCompleted = 1,
1853              CleanupOutput = @CurrentCommandOutput05
1854          WHERE ID = @CurrentDirectoryID
1855
1856          SET @CurrentDirectoryID = NULL
1857          SET @CurrentDirectoryPath = NULL
1858          SET @CurrentCleanupDate = NULL
1859
1860          SET @CurrentCommand05 = NULL
1861
1862          SET @CurrentCommandOutput05 = NULL
1863
1864          SET @CurrentCommandType05 = NULL
1865        END
1866      END
1867    END
1868
1869    -- Update that the database is completed
1870    UPDATE @tmpDatabases
1871    SET Completed = 1
1872    WHERE Selected = 1
1873    AND Completed = 0
1874    AND ID = @CurrentDBID
1875
1876    -- Clear variables
1877    SET @CurrentDBID = NULL
1878    SET @CurrentDatabaseID = NULL
1879    SET @CurrentDatabaseName = NULL
1880    SET @CurrentBackupType = NULL
1881    SET @CurrentFileExtension = NULL
1882    SET @CurrentFileNumber = NULL
1883    SET @CurrentDifferentialBaseLSN = NULL
1884    SET @CurrentDifferentialBaseIsSnapshot = NULL
1885    SET @CurrentLogLSN = NULL
1886    SET @CurrentLatestBackup = NULL
1887    SET @CurrentDatabaseNameFS = NULL
1888    SET @CurrentDate = NULL
1889    SET @CurrentCleanupDate = NULL
1890    SET @CurrentIsDatabaseAccessible = NULL
1891    SET @CurrentAvailabilityGroup = NULL
1892    SET @CurrentAvailabilityGroupRole = NULL
1893    SET @CurrentAvailabilityGroupBackupPreference = NULL
1894    SET @CurrentIsPreferredBackupReplica = NULL
1895    SET @CurrentDatabaseMirroringRole = NULL
1896    SET @CurrentLogShippingRole = NULL
1897
1898    SET @CurrentCommand03 = NULL
1899
1900    SET @CurrentCommandOutput03 = NULL
1901
1902    SET @CurrentCommandType03 = NULL
1903
1904    DELETE FROM @CurrentDirectories
1905    DELETE FROM @CurrentFiles
1906    DELETE FROM @CurrentCleanupDates
1907    DELETE FROM @CurrentBackupSet
1908
1909  END
1910
1911  ----------------------------------------------------------------------------------------------------
1912  --// Log completing information                                                                 //--
1913  ----------------------------------------------------------------------------------------------------
1914
1915  Logging:
1916  SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)
1917  SET @EndMessage = REPLACE(@EndMessage,'%','%%')
1918  RAISERROR(@EndMessage,10,1) WITH NOWAIT
1919
1920  IF @ReturnCode <> 0
1921  BEGIN
1922    RETURN @ReturnCode
1923  END
1924
1925  ----------------------------------------------------------------------------------------------------
1926
1927END
1928GO
1929SET ANSI_NULLS ON
1930GO
1931SET QUOTED_IDENTIFIER ON
1932GO
1933CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]
1934
1935@Databases nvarchar(max),
1936@CheckCommands nvarchar(max) = 'CHECKDB',
1937@PhysicalOnly nvarchar(max) = 'N',
1938@NoIndex nvarchar(max) = 'N',
1939@ExtendedLogicalChecks nvarchar(max) = 'N',
1940@TabLock nvarchar(max) = 'N',
1941@FileGroups nvarchar(max) = NULL,
1942@Objects nvarchar(max) = NULL,
1943@LockTimeout int = NULL,
1944@LogToTable nvarchar(max) = 'N',
1945@Execute nvarchar(max) = 'Y'
1946
1947AS
1948
1949BEGIN
1950
1951  ----------------------------------------------------------------------------------------------------
1952  --// Source: https://ola.hallengren.com                                                          //--
1953  ----------------------------------------------------------------------------------------------------
1954
1955  SET NOCOUNT ON
1956
1957  DECLARE @StartMessage nvarchar(max)
1958  DECLARE @EndMessage nvarchar(max)
1959  DECLARE @DatabaseMessage nvarchar(max)
1960  DECLARE @ErrorMessage nvarchar(max)
1961
1962  DECLARE @Version numeric(18,10)
1963  DECLARE @AmazonRDS bit
1964
1965  DECLARE @Cluster nvarchar(max)
1966
1967  DECLARE @CurrentDBID int
1968  DECLARE @CurrentDatabaseID int
1969  DECLARE @CurrentDatabaseName nvarchar(max)
1970  DECLARE @CurrentIsDatabaseAccessible bit
1971  DECLARE @CurrentAvailabilityGroup nvarchar(max)
1972  DECLARE @CurrentAvailabilityGroupRole nvarchar(max)
1973  DECLARE @CurrentDatabaseMirroringRole nvarchar(max)
1974
1975  DECLARE @CurrentFGID int
1976  DECLARE @CurrentFileGroupID int
1977  DECLARE @CurrentFileGroupName nvarchar(max)
1978  DECLARE @CurrentFileGroupExists bit
1979
1980  DECLARE @CurrentOID int
1981  DECLARE @CurrentSchemaID int
1982  DECLARE @CurrentSchemaName nvarchar(max)
1983  DECLARE @CurrentObjectID int
1984  DECLARE @CurrentObjectName nvarchar(max)
1985  DECLARE @CurrentObjectType nvarchar(max)
1986  DECLARE @CurrentObjectExists bit
1987
1988  DECLARE @CurrentCommand01 nvarchar(max)
1989  DECLARE @CurrentCommand02 nvarchar(max)
1990  DECLARE @CurrentCommand03 nvarchar(max)
1991  DECLARE @CurrentCommand04 nvarchar(max)
1992  DECLARE @CurrentCommand05 nvarchar(max)
1993  DECLARE @CurrentCommand06 nvarchar(max)
1994  DECLARE @CurrentCommand07 nvarchar(max)
1995  DECLARE @CurrentCommand08 nvarchar(max)
1996  DECLARE @CurrentCommand09 nvarchar(max)
1997
1998  DECLARE @CurrentCommandOutput01 int
1999  DECLARE @CurrentCommandOutput04 int
2000  DECLARE @CurrentCommandOutput05 int
2001  DECLARE @CurrentCommandOutput08 int
2002  DECLARE @CurrentCommandOutput09 int
2003
2004  DECLARE @CurrentCommandType01 nvarchar(max)
2005  DECLARE @CurrentCommandType04 nvarchar(max)
2006  DECLARE @CurrentCommandType05 nvarchar(max)
2007  DECLARE @CurrentCommandType08 nvarchar(max)
2008  DECLARE @CurrentCommandType09 nvarchar(max)
2009
2010  DECLARE @tmpDatabases TABLE (ID int IDENTITY,
2011                               DatabaseName nvarchar(max),
2012                               DatabaseType nvarchar(max),
2013                               [Snapshot] bit,
2014                               Selected bit,
2015                               Completed bit,
2016                               PRIMARY KEY(Selected, Completed, ID))
2017
2018  DECLARE @tmpFileGroups TABLE (ID int IDENTITY,
2019                                FileGroupID int,
2020                                FileGroupName nvarchar(max),
2021                                Selected bit,
2022                                Completed bit,
2023                                PRIMARY KEY(Selected, Completed, ID))
2024
2025  DECLARE @tmpObjects TABLE (ID int IDENTITY,
2026                             SchemaID int,
2027                             SchemaName nvarchar(max),
2028                             ObjectID int,
2029                             ObjectName nvarchar(max),
2030                             ObjectType nvarchar(max),
2031                             Selected bit,
2032                             Completed bit,
2033                             PRIMARY KEY(Selected, Completed, ID))
2034
2035  DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
2036                                    DatabaseType nvarchar(max),
2037                                    Selected bit)
2038
2039  DECLARE @SelectedFileGroups TABLE (DatabaseName nvarchar(max),
2040                                     FileGroupName nvarchar(max),
2041                                     Selected bit)
2042
2043  DECLARE @SelectedObjects TABLE (DatabaseName nvarchar(max),
2044                                  SchemaName nvarchar(max),
2045                                  ObjectName nvarchar(max),
2046                                  Selected bit)
2047
2048  DECLARE @SelectedCheckCommands TABLE (CheckCommand nvarchar(max))
2049
2050  DECLARE @Error int
2051  DECLARE @ReturnCode int
2052
2053  SET @Error = 0
2054  SET @ReturnCode = 0
2055
2056  SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
2057
2058  SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END
2059
2060  ----------------------------------------------------------------------------------------------------
2061  --// Log initial information                                                                    //--
2062  ----------------------------------------------------------------------------------------------------
2063
2064  SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
2065  SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)
2066  SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)
2067  SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)
2068  SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
2069  SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')
2070  SET @StartMessage = @StartMessage + ', @CheckCommands = ' + ISNULL('''' + REPLACE(@CheckCommands,'''','''''') + '''','NULL')
2071  SET @StartMessage = @StartMessage + ', @PhysicalOnly = ' + ISNULL('''' + REPLACE(@PhysicalOnly,'''','''''') + '''','NULL')
2072  SET @StartMessage = @StartMessage + ', @NoIndex = ' + ISNULL('''' + REPLACE(@NoIndex,'''','''''') + '''','NULL')
2073  SET @StartMessage = @StartMessage + ', @ExtendedLogicalChecks = ' + ISNULL('''' + REPLACE(@ExtendedLogicalChecks,'''','''''') + '''','NULL')
2074  SET @StartMessage = @StartMessage + ', @TabLock = ' + ISNULL('''' + REPLACE(@TabLock,'''','''''') + '''','NULL')
2075  SET @StartMessage = @StartMessage + ', @FileGroups = ' + ISNULL('''' + REPLACE(@FileGroups,'''','''''') + '''','NULL')
2076  SET @StartMessage = @StartMessage + ', @Objects = ' + ISNULL('''' + REPLACE(@Objects,'''','''''') + '''','NULL')
2077  SET @StartMessage = @StartMessage + ', @LockTimeout = ' + ISNULL(CAST(@LockTimeout AS nvarchar),'NULL')
2078  SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')
2079  SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)
2080  SET @StartMessage = @StartMessage + 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10)
2081  SET @StartMessage = REPLACE(@StartMessage,'%','%%') + ' '
2082  RAISERROR(@StartMessage,10,1) WITH NOWAIT
2083
2084  ----------------------------------------------------------------------------------------------------
2085  --// Check core requirements                                                                    //--
2086  ----------------------------------------------------------------------------------------------------
2087
2088  IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute')
2089  BEGIN
2090    SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
2091    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2092    SET @Error = @@ERROR
2093  END
2094
2095  IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute' AND (OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))
2096  BEGIN
2097    SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
2098    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2099    SET @Error = @@ERROR
2100  END
2101
2102  IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')
2103  BEGIN
2104    SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
2105    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2106    SET @Error = @@ERROR
2107  END
2108
2109  IF SERVERPROPERTY('EngineEdition') = 5 AND @Version < 12
2110  BEGIN
2111    SET @ErrorMessage = 'The stored procedure DatabaseIntegrityCheck is not supported on this version of Azure SQL Database.' + CHAR(13) + CHAR(10) + ' '
2112    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2113    SET @Error = @@ERROR
2114  END
2115
2116  IF @Error <> 0
2117  BEGIN
2118    SET @ReturnCode = @Error
2119    GOTO Logging
2120  END
2121
2122  ----------------------------------------------------------------------------------------------------
2123  --// Select databases                                                                           //--
2124  ----------------------------------------------------------------------------------------------------
2125
2126  SET @Databases = REPLACE(@Databases, ', ', ',');
2127
2128  WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
2129  (
2130  SELECT 1 AS StartPosition,
2131         ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
2132         SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem
2133  WHERE @Databases IS NOT NULL
2134  UNION ALL
2135  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
2136         ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
2137         SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem
2138  FROM Databases1
2139  WHERE EndPosition < LEN(@Databases) + 1
2140  ),
2141  Databases2 (DatabaseItem, Selected) AS
2142  (
2143  SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
2144         CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
2145  FROM Databases1
2146  ),
2147  Databases3 (DatabaseItem, DatabaseType, Selected) AS
2148  (
2149  SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
2150         CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
2151         Selected
2152  FROM Databases2
2153  ),
2154  Databases4 (DatabaseName, DatabaseType, Selected) AS
2155  (
2156  SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
2157         DatabaseType,
2158         Selected
2159  FROM Databases3
2160  )
2161  INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
2162  SELECT DatabaseName,
2163         DatabaseType,
2164         Selected
2165  FROM Databases4
2166  OPTION (MAXRECURSION 0)
2167
2168  INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, [Snapshot], Selected, Completed)
2169  SELECT [name] AS DatabaseName,
2170         CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
2171         CASE WHEN source_database_id IS NOT NULL THEN 1 ELSE 0 END AS [Snapshot],
2172         0 AS Selected,
2173         0 AS Completed
2174  FROM sys.databases
2175  ORDER BY [name] ASC
2176
2177  UPDATE tmpDatabases
2178  SET tmpDatabases.Selected = SelectedDatabases.Selected
2179  FROM @tmpDatabases tmpDatabases
2180  INNER JOIN @SelectedDatabases SelectedDatabases
2181  ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
2182  AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
2183  AND NOT ((tmpDatabases.DatabaseName = 'tempdb' OR tmpDatabases.[Snapshot] = 1) AND tmpDatabases.DatabaseName <> SelectedDatabases.DatabaseName)
2184  WHERE SelectedDatabases.Selected = 1
2185
2186  UPDATE tmpDatabases
2187  SET tmpDatabases.Selected = SelectedDatabases.Selected
2188  FROM @tmpDatabases tmpDatabases
2189  INNER JOIN @SelectedDatabases SelectedDatabases
2190  ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
2191  AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
2192  AND NOT ((tmpDatabases.DatabaseName = 'tempdb' OR tmpDatabases.[Snapshot] = 1) AND tmpDatabases.DatabaseName <> SelectedDatabases.DatabaseName)
2193  WHERE SelectedDatabases.Selected = 0
2194
2195  IF @Databases IS NULL OR NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')
2196  BEGIN
2197    SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '
2198    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2199    SET @Error = @@ERROR
2200  END
2201
2202  ----------------------------------------------------------------------------------------------------
2203  --// Select filegroups                                                                          //--
2204  ----------------------------------------------------------------------------------------------------
2205
2206  SET @FileGroups = REPLACE(@FileGroups, ', ', ',');
2207
2208  WITH FileGroups1 (StartPosition, EndPosition, FileGroupItem) AS
2209  (
2210  SELECT 1 AS StartPosition,
2211         ISNULL(NULLIF(CHARINDEX(',', @FileGroups, 1), 0), LEN(@FileGroups) + 1) AS EndPosition,
2212         SUBSTRING(@FileGroups, 1, ISNULL(NULLIF(CHARINDEX(',', @FileGroups, 1), 0), LEN(@FileGroups) + 1) - 1) AS FileGroupItem
2213  WHERE @FileGroups IS NOT NULL
2214  UNION ALL
2215  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
2216         ISNULL(NULLIF(CHARINDEX(',', @FileGroups, EndPosition + 1), 0), LEN(@FileGroups) + 1) AS EndPosition,
2217         SUBSTRING(@FileGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FileGroups, EndPosition + 1), 0), LEN(@FileGroups) + 1) - EndPosition - 1) AS FileGroupItem
2218  FROM FileGroups1
2219  WHERE EndPosition < LEN(@FileGroups) + 1
2220  ),
2221  FileGroups2 (FileGroupItem, Selected) AS
2222  (
2223  SELECT CASE WHEN FileGroupItem LIKE '-%' THEN RIGHT(FileGroupItem,LEN(FileGroupItem) - 1) ELSE FileGroupItem END AS FileGroupItem,
2224         CASE WHEN FileGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
2225  FROM FileGroups1
2226  ),
2227  FileGroups3 (FileGroupItem, Selected) AS
2228  (
2229  SELECT CASE WHEN FileGroupItem = 'ALL_FILEGROUPS' THEN '%.%' ELSE FileGroupItem END AS FileGroupItem,
2230         Selected
2231  FROM FileGroups2
2232  ),
2233  FileGroups4 (DatabaseName, FileGroupName, Selected) AS
2234  (
2235  SELECT CASE WHEN PARSENAME(FileGroupItem,4) IS NULL AND PARSENAME(FileGroupItem,3) IS NULL THEN PARSENAME(FileGroupItem,2) ELSE NULL END AS DatabaseName,
2236         CASE WHEN PARSENAME(FileGroupItem,4) IS NULL AND PARSENAME(FileGroupItem,3) IS NULL THEN PARSENAME(FileGroupItem,1) ELSE NULL END AS FileGroupName,
2237         Selected
2238  FROM FileGroups3
2239  )
2240  INSERT INTO @SelectedFileGroups (DatabaseName, FileGroupName, Selected)
2241  SELECT DatabaseName, FileGroupName, Selected
2242  FROM FileGroups4
2243  OPTION (MAXRECURSION 0)
2244
2245  ----------------------------------------------------------------------------------------------------
2246  --// Select objects                                                                             //--
2247  ----------------------------------------------------------------------------------------------------
2248
2249  SET @Objects = REPLACE(@Objects, ', ', ',');
2250
2251  WITH Objects1 (StartPosition, EndPosition, ObjectItem) AS
2252  (
2253  SELECT 1 AS StartPosition,
2254         ISNULL(NULLIF(CHARINDEX(',', @Objects, 1), 0), LEN(@Objects) + 1) AS EndPosition,
2255         SUBSTRING(@Objects, 1, ISNULL(NULLIF(CHARINDEX(',', @Objects, 1), 0), LEN(@Objects) + 1) - 1) AS ObjectItem
2256  WHERE @Objects IS NOT NULL
2257  UNION ALL
2258  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
2259         ISNULL(NULLIF(CHARINDEX(',', @Objects, EndPosition + 1), 0), LEN(@Objects) + 1) AS EndPosition,
2260         SUBSTRING(@Objects, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Objects, EndPosition + 1), 0), LEN(@Objects) + 1) - EndPosition - 1) AS ObjectItem
2261  FROM Objects1
2262  WHERE EndPosition < LEN(@Objects) + 1
2263  ),
2264  Objects2 (ObjectItem, Selected) AS
2265  (
2266  SELECT CASE WHEN ObjectItem LIKE '-%' THEN RIGHT(ObjectItem,LEN(ObjectItem) - 1) ELSE ObjectItem END AS ObjectItem,
2267         CASE WHEN ObjectItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
2268  FROM Objects1
2269  ),
2270  Objects3 (ObjectItem, Selected) AS
2271  (
2272  SELECT CASE WHEN ObjectItem = 'ALL_OBJECTS' THEN '%.%.%' ELSE ObjectItem END AS ObjectItem,
2273         Selected
2274  FROM Objects2
2275  ),
2276  Objects4 (DatabaseName, SchemaName, ObjectName, Selected) AS
2277  (
2278  SELECT CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,3) ELSE NULL END AS DatabaseName,
2279         CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,2) ELSE NULL END AS SchemaName,
2280         CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,1) ELSE NULL END AS ObjectName,
2281         Selected
2282  FROM Objects3
2283  )
2284  INSERT INTO @SelectedObjects (DatabaseName, SchemaName, ObjectName, Selected)
2285  SELECT DatabaseName, SchemaName, ObjectName, Selected
2286  FROM Objects4
2287  OPTION (MAXRECURSION 0);
2288
2289  ----------------------------------------------------------------------------------------------------
2290  --// Select check commands                                                                      //--
2291  ----------------------------------------------------------------------------------------------------
2292
2293  WITH CheckCommands (StartPosition, EndPosition, CheckCommand) AS
2294  (
2295  SELECT 1 AS StartPosition,
2296         ISNULL(NULLIF(CHARINDEX(',', @CheckCommands, 1), 0), LEN(@CheckCommands) + 1) AS EndPosition,
2297         SUBSTRING(@CheckCommands, 1, ISNULL(NULLIF(CHARINDEX(',', @CheckCommands, 1), 0), LEN(@CheckCommands) + 1) - 1) AS CheckCommand
2298  WHERE @CheckCommands IS NOT NULL
2299  UNION ALL
2300  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
2301         ISNULL(NULLIF(CHARINDEX(',', @CheckCommands, EndPosition + 1), 0), LEN(@CheckCommands) + 1) AS EndPosition,
2302         SUBSTRING(@CheckCommands, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @CheckCommands, EndPosition + 1), 0), LEN(@CheckCommands) + 1) - EndPosition - 1) AS CheckCommand
2303  FROM CheckCommands
2304  WHERE EndPosition < LEN(@CheckCommands) + 1
2305  )
2306  INSERT INTO @SelectedCheckCommands (CheckCommand)
2307  SELECT CheckCommand
2308  FROM CheckCommands
2309  OPTION (MAXRECURSION 0)
2310
2311  ----------------------------------------------------------------------------------------------------
2312  --// Check input parameters                                                                     //--
2313  ----------------------------------------------------------------------------------------------------
2314
2315  IF EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand NOT IN('CHECKDB','CHECKFILEGROUP','CHECKALLOC','CHECKTABLE','CHECKCATALOG')) OR EXISTS (SELECT * FROM @SelectedCheckCommands GROUP BY CheckCommand HAVING COUNT(*) > 1) OR NOT EXISTS (SELECT * FROM @SelectedCheckCommands) OR (EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKDB')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP','CHECKALLOC','CHECKTABLE','CHECKCATALOG'))) OR (EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKALLOC','CHECKTABLE')))
2316  BEGIN
2317    SET @ErrorMessage = 'The value for the parameter @CheckCommands is not supported.' + CHAR(13) + CHAR(10) + ' '
2318    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2319    SET @Error = @@ERROR
2320  END
2321
2322  IF @PhysicalOnly NOT IN ('Y','N') OR @PhysicalOnly IS NULL
2323  BEGIN
2324    SET @ErrorMessage = 'The value for the parameter @PhysicalOnly is not supported.' + CHAR(13) + CHAR(10) + ' '
2325    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2326    SET @Error = @@ERROR
2327  END
2328
2329  IF @NoIndex NOT IN ('Y','N') OR @NoIndex IS NULL
2330  BEGIN
2331    SET @ErrorMessage = 'The value for the parameter @NoIndex is not supported.' + CHAR(13) + CHAR(10) + ' '
2332    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2333    SET @Error = @@ERROR
2334  END
2335
2336  IF @ExtendedLogicalChecks NOT IN ('Y','N') OR @ExtendedLogicalChecks IS NULL OR (@ExtendedLogicalChecks = 'Y' AND NOT @Version >= 10) OR (@PhysicalOnly = 'Y' AND @ExtendedLogicalChecks = 'Y')
2337  BEGIN
2338    SET @ErrorMessage = 'The value for the parameter @ExtendedLogicalChecks is not supported.' + CHAR(13) + CHAR(10) + ' '
2339    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2340    SET @Error = @@ERROR
2341  END
2342
2343  IF @TabLock NOT IN ('Y','N') OR @TabLock IS NULL
2344  BEGIN
2345    SET @ErrorMessage = 'The value for the parameter @TabLock is not supported.' + CHAR(13) + CHAR(10) + ' '
2346    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2347    SET @Error = @@ERROR
2348  END
2349
2350  IF EXISTS(SELECT * FROM @SelectedFileGroups WHERE DatabaseName IS NULL OR FileGroupName IS NULL) OR (@FileGroups IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedFileGroups)) OR (@FileGroups IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP'))
2351  BEGIN
2352    SET @ErrorMessage = 'The value for the parameter @FileGroups is not supported.' + CHAR(13) + CHAR(10) + ' '
2353    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2354    SET @Error = @@ERROR
2355  END
2356
2357  IF EXISTS(SELECT * FROM @SelectedObjects WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL) OR (@Objects IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedObjects)) OR (@Objects IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE'))
2358  BEGIN
2359    SET @ErrorMessage = 'The value for the parameter @Objects is not supported.' + CHAR(13) + CHAR(10) + ' '
2360    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2361    SET @Error = @@ERROR
2362  END
2363
2364  IF @LockTimeout < 0
2365  BEGIN
2366    SET @ErrorMessage = 'The value for the parameter @LockTimeout is not supported.' + CHAR(13) + CHAR(10) + ' '
2367    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2368    SET @Error = @@ERROR
2369  END
2370
2371  IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
2372  BEGIN
2373    SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '
2374    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2375    SET @Error = @@ERROR
2376  END
2377
2378  IF @Execute NOT IN('Y','N') OR @Execute IS NULL
2379  BEGIN
2380    SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '
2381    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2382    SET @Error = @@ERROR
2383  END
2384
2385  IF @Error <> 0
2386  BEGIN
2387    SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-integrity-check.html.' + CHAR(13) + CHAR(10) + ' '
2388    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2389    SET @ReturnCode = @Error
2390    GOTO Logging
2391  END
2392
2393  ----------------------------------------------------------------------------------------------------
2394  --// Check Availability Group cluster name                                                      //--
2395  ----------------------------------------------------------------------------------------------------
2396
2397  IF @Version >= 11 AND SERVERPROPERTY('EngineEdition') <> 5
2398  BEGIN
2399    SELECT @Cluster = cluster_name
2400    FROM sys.dm_hadr_cluster
2401  END
2402
2403  ----------------------------------------------------------------------------------------------------
2404  --// Execute commands                                                                           //--
2405  ----------------------------------------------------------------------------------------------------
2406
2407  WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)
2408  BEGIN
2409
2410    SELECT TOP 1 @CurrentDBID = ID,
2411                 @CurrentDatabaseName = DatabaseName
2412    FROM @tmpDatabases
2413    WHERE Selected = 1
2414    AND Completed = 0
2415    ORDER BY ID ASC
2416
2417    SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)
2418
2419    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE' AND SERVERPROPERTY('EngineEdition') <> 5
2420    BEGIN
2421      IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)
2422      BEGIN
2423        SET @CurrentIsDatabaseAccessible = 1
2424      END
2425      ELSE
2426      BEGIN
2427        SET @CurrentIsDatabaseAccessible = 0
2428      END
2429    END
2430
2431    IF @Version >= 11 AND @Cluster IS NOT NULL
2432    BEGIN
2433      SELECT @CurrentAvailabilityGroup = availability_groups.name,
2434             @CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc
2435      FROM sys.databases databases
2436      INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
2437      INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
2438      INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
2439      WHERE databases.name = @CurrentDatabaseName
2440    END
2441
2442    IF SERVERPROPERTY('EngineEdition') <> 5
2443    BEGIN
2444      SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
2445      FROM sys.database_mirroring
2446      WHERE database_id = @CurrentDatabaseID
2447    END
2448
2449    -- Set database message
2450    SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
2451    SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)
2452    SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)
2453    SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
2454    SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)
2455    SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)
2456    IF @CurrentIsDatabaseAccessible IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
2457    SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)
2458    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group: ' + @CurrentAvailabilityGroup + CHAR(13) + CHAR(10)
2459    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group role: ' + @CurrentAvailabilityGroupRole + CHAR(13) + CHAR(10)
2460    IF @CurrentDatabaseMirroringRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Database mirroring role: ' + @CurrentDatabaseMirroringRole + CHAR(13) + CHAR(10)
2461    SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%') + ' '
2462    RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
2463
2464    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
2465    AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
2466    BEGIN
2467
2468      -- Check database
2469      IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKDB')
2470      BEGIN
2471        SET @CurrentCommandType01 = 'DBCC_CHECKDB'
2472
2473        SET @CurrentCommand01 = ''
2474        IF @LockTimeout IS NOT NULL SET @CurrentCommand01 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
2475        SET @CurrentCommand01 = @CurrentCommand01 + 'DBCC CHECKDB (' + QUOTENAME(@CurrentDatabaseName)
2476        IF @NoIndex = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', NOINDEX'
2477        SET @CurrentCommand01 = @CurrentCommand01 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
2478        IF @PhysicalOnly = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', DATA_PURITY'
2479        IF @PhysicalOnly = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PHYSICAL_ONLY'
2480        IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', EXTENDED_LOGICAL_CHECKS'
2481        IF @TabLock = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', TABLOCK'
2482
2483        EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01, @CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
2484        SET @Error = @@ERROR
2485        IF @Error <> 0 SET @CurrentCommandOutput01 = @Error
2486        IF @CurrentCommandOutput01 <> 0 SET @ReturnCode = @CurrentCommandOutput01
2487      END
2488
2489      -- Check filegroups
2490      IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP')
2491      BEGIN
2492        SET @CurrentCommand02 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT data_space_id AS FileGroupID, name AS FileGroupName, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups WHERE [type] <> ''FX'' ORDER BY CASE WHEN filegroups.name = ''PRIMARY'' THEN 1 ELSE 0 END DESC, filegroups.name ASC'
2493
2494        INSERT INTO @tmpFileGroups (FileGroupID, FileGroupName, Selected, Completed)
2495        EXECUTE sp_executesql @statement = @CurrentCommand02
2496        SET @Error = @@ERROR
2497        IF @Error <> 0 SET @ReturnCode = @Error
2498
2499        IF @FileGroups IS NULL
2500        BEGIN
2501          UPDATE tmpFileGroups
2502          SET tmpFileGroups.Selected = 1
2503          FROM @tmpFileGroups tmpFileGroups
2504        END
2505        ELSE
2506        BEGIN
2507          UPDATE tmpFileGroups
2508          SET tmpFileGroups.Selected = SelectedFileGroups.Selected
2509          FROM @tmpFileGroups tmpFileGroups
2510          INNER JOIN @SelectedFileGroups SelectedFileGroups
2511          ON @CurrentDatabaseName LIKE REPLACE(SelectedFileGroups.DatabaseName,'_','[_]') AND tmpFileGroups.FileGroupName LIKE REPLACE(SelectedFileGroups.FileGroupName,'_','[_]')
2512          WHERE SelectedFileGroups.Selected = 1
2513
2514          UPDATE tmpFileGroups
2515          SET tmpFileGroups.Selected = SelectedFileGroups.Selected
2516          FROM @tmpFileGroups tmpFileGroups
2517          INNER JOIN @SelectedFileGroups SelectedFileGroups
2518          ON @CurrentDatabaseName LIKE REPLACE(SelectedFileGroups.DatabaseName,'_','[_]') AND tmpFileGroups.FileGroupName LIKE REPLACE(SelectedFileGroups.FileGroupName,'_','[_]')
2519          WHERE SelectedFileGroups.Selected = 0
2520        END
2521
2522        WHILE EXISTS (SELECT * FROM @tmpFileGroups WHERE Selected = 1 AND Completed = 0)
2523        BEGIN
2524          SELECT TOP 1 @CurrentFGID = ID,
2525                       @CurrentFileGroupID = FileGroupID,
2526                       @CurrentFileGroupName = FileGroupName
2527          FROM @tmpFileGroups
2528          WHERE Selected = 1
2529          AND Completed = 0
2530          ORDER BY ID ASC
2531
2532          -- Does the filegroup exist?
2533          SET @CurrentCommand03 = ''
2534          IF @LockTimeout IS NOT NULL SET @CurrentCommand03 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
2535          SET @CurrentCommand03 = @CurrentCommand03 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups WHERE [type] <> ''FX'' AND filegroups.data_space_id = @ParamFileGroupID AND filegroups.[name] = @ParamFileGroupName) BEGIN SET @ParamFileGroupExists = 1 END'
2536
2537          EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N'@ParamFileGroupID int, @ParamFileGroupName sysname, @ParamFileGroupExists bit OUTPUT', @ParamFileGroupID = @CurrentFileGroupID, @ParamFileGroupName = @CurrentFileGroupName, @ParamFileGroupExists = @CurrentFileGroupExists OUTPUT
2538          SET @Error = @@ERROR
2539          IF @Error = 0 AND @CurrentFileGroupExists IS NULL SET @CurrentFileGroupExists = 0
2540          IF @Error = 1222
2541          BEGIN
2542            SET @ErrorMessage = 'The file group ' + QUOTENAME(@CurrentFileGroupName) + ' in the database ' + QUOTENAME(@CurrentDatabaseName) + ' is locked. It could not be checked if the filegroup exists.' + CHAR(13) + CHAR(10) + ' '
2543            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
2544            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2545          END
2546          IF @Error <> 0
2547          BEGIN
2548            SET @ReturnCode = @Error
2549          END
2550
2551          IF @CurrentFileGroupExists = 1
2552          BEGIN
2553            SET @CurrentCommandType04 = 'DBCC_CHECKFILEGROUP'
2554
2555            SET @CurrentCommand04 = ''
2556            IF @LockTimeout IS NOT NULL SET @CurrentCommand04 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
2557            SET @CurrentCommand04 = @CurrentCommand04 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; DBCC CHECKFILEGROUP (' + QUOTENAME(@CurrentFileGroupName)
2558            IF @NoIndex = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', NOINDEX'
2559            SET @CurrentCommand04 = @CurrentCommand04 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
2560            IF @PhysicalOnly = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', PHYSICAL_ONLY'
2561            IF @TabLock = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', TABLOCK'
2562
2563            EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @Command = @CurrentCommand04, @CommandType = @CurrentCommandType04, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
2564            SET @Error = @@ERROR
2565            IF @Error <> 0 SET @CurrentCommandOutput04 = @Error
2566            IF @CurrentCommandOutput04 <> 0 SET @ReturnCode = @CurrentCommandOutput04
2567          END
2568
2569          UPDATE @tmpFileGroups
2570          SET Completed = 1
2571          WHERE Selected = 1
2572          AND Completed = 0
2573          AND ID = @CurrentFGID
2574
2575          SET @CurrentFGID = NULL
2576          SET @CurrentFileGroupID = NULL
2577          SET @CurrentFileGroupName = NULL
2578          SET @CurrentFileGroupExists = NULL
2579
2580          SET @CurrentCommand03 = NULL
2581          SET @CurrentCommand04 = NULL
2582
2583          SET @CurrentCommandOutput04 = NULL
2584
2585          SET @CurrentCommandType04 = NULL
2586        END
2587      END
2588
2589      -- Check disk space allocation structures
2590      IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKALLOC')
2591      BEGIN
2592        SET @CurrentCommandType05 = 'DBCC_CHECKALLOC'
2593
2594        SET @CurrentCommand05 = ''
2595        IF @LockTimeout IS NOT NULL SET @CurrentCommand05 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
2596        SET @CurrentCommand05 = @CurrentCommand05 + 'DBCC CHECKALLOC (' + QUOTENAME(@CurrentDatabaseName)
2597        SET @CurrentCommand05 = @CurrentCommand05 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
2598        IF @TabLock = 'Y' SET @CurrentCommand05 = @CurrentCommand05 + ', TABLOCK'
2599
2600        EXECUTE @CurrentCommandOutput05 = [dbo].[CommandExecute] @Command = @CurrentCommand05, @CommandType = @CurrentCommandType05, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
2601        SET @Error = @@ERROR
2602        IF @Error <> 0 SET @CurrentCommandOutput05 = @Error
2603        IF @CurrentCommandOutput05 <> 0 SET @ReturnCode = @CurrentCommandOutput05
2604      END
2605
2606      -- Check objects
2607      IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE')
2608      BEGIN
2609        SET @CurrentCommand06 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(''U'',''V'') AND EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.object_id = objects.object_id)' + CASE WHEN @Version >= 12 THEN ' AND (tables.is_memory_optimized = 0 OR is_memory_optimized IS NULL)' ELSE '' END + ' ORDER BY schemas.name ASC, objects.name ASC'
2610
2611        INSERT INTO @tmpObjects (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, Selected, Completed)
2612        EXECUTE sp_executesql @statement = @CurrentCommand06
2613        SET @Error = @@ERROR
2614        IF @Error <> 0 SET @ReturnCode = @Error
2615
2616        IF @Objects IS NULL
2617        BEGIN
2618          UPDATE tmpObjects
2619          SET tmpObjects.Selected = 1
2620          FROM @tmpObjects tmpObjects
2621        END
2622        ELSE
2623        BEGIN
2624          UPDATE tmpObjects
2625          SET tmpObjects.Selected = SelectedObjects.Selected
2626          FROM @tmpObjects tmpObjects
2627          INNER JOIN @SelectedObjects SelectedObjects
2628          ON @CurrentDatabaseName LIKE REPLACE(SelectedObjects.DatabaseName,'_','[_]') AND tmpObjects.SchemaName LIKE REPLACE(SelectedObjects.SchemaName,'_','[_]') AND tmpObjects.ObjectName LIKE REPLACE(SelectedObjects.ObjectName,'_','[_]')
2629          WHERE SelectedObjects.Selected = 1
2630
2631          UPDATE tmpObjects
2632          SET tmpObjects.Selected = SelectedObjects.Selected
2633          FROM @tmpObjects tmpObjects
2634          INNER JOIN @SelectedObjects SelectedObjects
2635          ON @CurrentDatabaseName LIKE REPLACE(SelectedObjects.DatabaseName,'_','[_]') AND tmpObjects.SchemaName LIKE REPLACE(SelectedObjects.SchemaName,'_','[_]') AND tmpObjects.ObjectName LIKE REPLACE(SelectedObjects.ObjectName,'_','[_]')
2636          WHERE SelectedObjects.Selected = 0
2637        END
2638
2639        WHILE EXISTS (SELECT * FROM @tmpObjects WHERE Selected = 1 AND Completed = 0)
2640        BEGIN
2641          SELECT TOP 1 @CurrentOID = ID,
2642                       @CurrentSchemaID = SchemaID,
2643                       @CurrentSchemaName = SchemaName,
2644                       @CurrentObjectID = ObjectID,
2645                       @CurrentObjectName = ObjectName,
2646                       @CurrentObjectType = ObjectType
2647          FROM @tmpObjects
2648          WHERE Selected = 1
2649          AND Completed = 0
2650          ORDER BY ID ASC
2651
2652          -- Does the object exist?
2653          SET @CurrentCommand07 = ''
2654          IF @LockTimeout IS NOT NULL SET @CurrentCommand07 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
2655          SET @CurrentCommand07 = @CurrentCommand07 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(''U'',''V'') AND EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.object_id = objects.object_id)' + CASE WHEN @Version >= 12 THEN ' AND (tables.is_memory_optimized = 0 OR is_memory_optimized IS NULL)' ELSE '' END + ' AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType) BEGIN SET @ParamObjectExists = 1 END'
2656
2657          EXECUTE sp_executesql @statement = @CurrentCommand07, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamObjectExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamObjectExists = @CurrentObjectExists OUTPUT
2658          SET @Error = @@ERROR
2659          IF @Error = 0 AND @CurrentObjectExists IS NULL SET @CurrentObjectExists = 0
2660          IF @Error = 1222
2661          BEGIN
2662            SET @ErrorMessage = 'The object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the object exists.' + CHAR(13) + CHAR(10) + ' '
2663            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
2664            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
2665          END
2666          IF @Error <> 0
2667          BEGIN
2668            SET @ReturnCode = @Error
2669          END
2670
2671          IF @CurrentObjectExists = 1
2672          BEGIN
2673            SET @CurrentCommandType08 = 'DBCC_CHECKTABLE'
2674
2675            SET @CurrentCommand08 = ''
2676            IF @LockTimeout IS NOT NULL SET @CurrentCommand08 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
2677            SET @CurrentCommand08 = @CurrentCommand08 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; DBCC CHECKTABLE (''' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ''''
2678            IF @NoIndex = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', NOINDEX'
2679            SET @CurrentCommand08 = @CurrentCommand08 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
2680            IF @PhysicalOnly = 'N' SET @CurrentCommand08 = @CurrentCommand08 + ', DATA_PURITY'
2681            IF @PhysicalOnly = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', PHYSICAL_ONLY'
2682            IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', EXTENDED_LOGICAL_CHECKS'
2683            IF @TabLock = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', TABLOCK'
2684
2685            EXECUTE @CurrentCommandOutput08 = [dbo].[CommandExecute] @Command = @CurrentCommand08, @CommandType = @CurrentCommandType08, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @LogToTable = @LogToTable, @Execute = @Execute
2686            SET @Error = @@ERROR
2687            IF @Error <> 0 SET @CurrentCommandOutput08 = @Error
2688            IF @CurrentCommandOutput08 <> 0 SET @ReturnCode = @CurrentCommandOutput08
2689          END
2690
2691          UPDATE @tmpObjects
2692          SET Completed = 1
2693          WHERE Selected = 1
2694          AND Completed = 0
2695          AND ID = @CurrentOID
2696
2697          SET @CurrentOID = NULL
2698          SET @CurrentSchemaID = NULL
2699          SET @CurrentSchemaName = NULL
2700          SET @CurrentObjectID = NULL
2701          SET @CurrentObjectName = NULL
2702          SET @CurrentObjectType = NULL
2703          SET @CurrentObjectExists = NULL
2704
2705          SET @CurrentCommand07 = NULL
2706          SET @CurrentCommand08 = NULL
2707
2708          SET @CurrentCommandOutput08 = NULL
2709
2710          SET @CurrentCommandType08 = NULL
2711        END
2712      END
2713
2714      -- Check catalog
2715      IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKCATALOG')
2716      BEGIN
2717        SET @CurrentCommandType09 = 'DBCC_CHECKCATALOG'
2718
2719        SET @CurrentCommand09 = ''
2720        IF @LockTimeout IS NOT NULL SET @CurrentCommand09 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
2721        SET @CurrentCommand09 = @CurrentCommand09 + 'DBCC CHECKCATALOG (' + QUOTENAME(@CurrentDatabaseName)
2722        SET @CurrentCommand09 = @CurrentCommand09 + ') WITH NO_INFOMSGS'
2723
2724        EXECUTE @CurrentCommandOutput09 = [dbo].[CommandExecute] @Command = @CurrentCommand09, @CommandType = @CurrentCommandType09, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
2725        SET @Error = @@ERROR
2726        IF @Error <> 0 SET @CurrentCommandOutput09 = @Error
2727        IF @CurrentCommandOutput09 <> 0 SET @ReturnCode = @CurrentCommandOutput09
2728      END
2729
2730    END
2731
2732    -- Update that the database is completed
2733    UPDATE @tmpDatabases
2734    SET Completed = 1
2735    WHERE Selected = 1
2736    AND Completed = 0
2737    AND ID = @CurrentDBID
2738
2739    -- Clear variables
2740    SET @CurrentDBID = NULL
2741    SET @CurrentDatabaseID = NULL
2742    SET @CurrentDatabaseName = NULL
2743    SET @CurrentIsDatabaseAccessible = NULL
2744    SET @CurrentAvailabilityGroup = NULL
2745    SET @CurrentAvailabilityGroupRole = NULL
2746    SET @CurrentDatabaseMirroringRole = NULL
2747
2748    SET @CurrentCommand01 = NULL
2749    SET @CurrentCommand02 = NULL
2750    SET @CurrentCommand05 = NULL
2751    SET @CurrentCommand06 = NULL
2752    SET @CurrentCommand09 = NULL
2753
2754    SET @CurrentCommandOutput01 = NULL
2755    SET @CurrentCommandOutput05 = NULL
2756    SET @CurrentCommandOutput09 = NULL
2757
2758    SET @CurrentCommandType01 = NULL
2759    SET @CurrentCommandType05 = NULL
2760    SET @CurrentCommandType09 = NULL
2761
2762    DELETE FROM @tmpFileGroups
2763    DELETE FROM @tmpObjects
2764
2765  END
2766
2767  ----------------------------------------------------------------------------------------------------
2768  --// Log completing information                                                                 //--
2769  ----------------------------------------------------------------------------------------------------
2770
2771  Logging:
2772  SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)
2773  SET @EndMessage = REPLACE(@EndMessage,'%','%%')
2774  RAISERROR(@EndMessage,10,1) WITH NOWAIT
2775
2776  IF @ReturnCode <> 0
2777  BEGIN
2778    RETURN @ReturnCode
2779  END
2780
2781  ----------------------------------------------------------------------------------------------------
2782
2783END
2784GO
2785SET ANSI_NULLS ON
2786GO
2787SET QUOTED_IDENTIFIER ON
2788GO
2789CREATE PROCEDURE [dbo].[IndexOptimize]
2790
2791@Databases nvarchar(max),
2792@FragmentationLow nvarchar(max) = NULL,
2793@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
2794@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
2795@FragmentationLevel1 int = 5,
2796@FragmentationLevel2 int = 30,
2797@PageCountLevel int = 1000,
2798@SortInTempdb nvarchar(max) = 'N',
2799@MaxDOP int = NULL,
2800@FillFactor int = NULL,
2801@PadIndex nvarchar(max) = NULL,
2802@LOBCompaction nvarchar(max) = 'Y',
2803@UpdateStatistics nvarchar(max) = NULL,
2804@OnlyModifiedStatistics nvarchar(max) = 'N',
2805@StatisticsSample int = NULL,
2806@StatisticsResample nvarchar(max) = 'N',
2807@PartitionLevel nvarchar(max) = 'Y',
2808@MSShippedObjects nvarchar(max) = 'N',
2809@Indexes nvarchar(max) = NULL,
2810@TimeLimit int = NULL,
2811@Delay int = NULL,
2812@WaitAtLowPriorityMaxDuration int = NULL,
2813@WaitAtLowPriorityAbortAfterWait nvarchar(max) = NULL,
2814@LockTimeout int = NULL,
2815@LogToTable nvarchar(max) = 'N',
2816@Execute nvarchar(max) = 'Y'
2817
2818AS
2819
2820BEGIN
2821
2822  ----------------------------------------------------------------------------------------------------
2823  --// Source: https://ola.hallengren.com                                                          //--
2824  ----------------------------------------------------------------------------------------------------
2825
2826  SET NOCOUNT ON
2827
2828  SET ARITHABORT ON
2829
2830  SET NUMERIC_ROUNDABORT OFF
2831
2832  DECLARE @StartMessage nvarchar(max)
2833  DECLARE @EndMessage nvarchar(max)
2834  DECLARE @DatabaseMessage nvarchar(max)
2835  DECLARE @ErrorMessage nvarchar(max)
2836
2837  DECLARE @Version numeric(18,10)
2838  DECLARE @AmazonRDS bit
2839
2840  DECLARE @Cluster nvarchar(max)
2841
2842  DECLARE @StartTime datetime
2843
2844  DECLARE @CurrentDBID int
2845  DECLARE @CurrentDatabaseID int
2846  DECLARE @CurrentDatabaseName nvarchar(max)
2847  DECLARE @CurrentIsDatabaseAccessible bit
2848  DECLARE @CurrentAvailabilityGroup nvarchar(max)
2849  DECLARE @CurrentAvailabilityGroupRole nvarchar(max)
2850  DECLARE @CurrentDatabaseMirroringRole nvarchar(max)
2851
2852  DECLARE @CurrentCommand01 nvarchar(max)
2853  DECLARE @CurrentCommand02 nvarchar(max)
2854  DECLARE @CurrentCommand03 nvarchar(max)
2855  DECLARE @CurrentCommand04 nvarchar(max)
2856  DECLARE @CurrentCommand05 nvarchar(max)
2857  DECLARE @CurrentCommand06 nvarchar(max)
2858  DECLARE @CurrentCommand07 nvarchar(max)
2859  DECLARE @CurrentCommand08 nvarchar(max)
2860  DECLARE @CurrentCommand09 nvarchar(max)
2861  DECLARE @CurrentCommand10 nvarchar(max)
2862  DECLARE @CurrentCommand11 nvarchar(max)
2863  DECLARE @CurrentCommand12 nvarchar(max)
2864  DECLARE @CurrentCommand13 nvarchar(max)
2865  DECLARE @CurrentCommand14 nvarchar(max)
2866
2867  DECLARE @CurrentCommandOutput13 int
2868  DECLARE @CurrentCommandOutput14 int
2869
2870  DECLARE @CurrentCommandType13 nvarchar(max)
2871  DECLARE @CurrentCommandType14 nvarchar(max)
2872
2873  DECLARE @CurrentIxID int
2874  DECLARE @CurrentSchemaID int
2875  DECLARE @CurrentSchemaName nvarchar(max)
2876  DECLARE @CurrentObjectID int
2877  DECLARE @CurrentObjectName nvarchar(max)
2878  DECLARE @CurrentObjectType nvarchar(max)
2879  DECLARE @CurrentIsMemoryOptimized bit
2880  DECLARE @CurrentIndexID int
2881  DECLARE @CurrentIndexName nvarchar(max)
2882  DECLARE @CurrentIndexType int
2883  DECLARE @CurrentStatisticsID int
2884  DECLARE @CurrentStatisticsName nvarchar(max)
2885  DECLARE @CurrentPartitionID bigint
2886  DECLARE @CurrentPartitionNumber int
2887  DECLARE @CurrentPartitionCount int
2888  DECLARE @CurrentIsPartition bit
2889  DECLARE @CurrentIndexExists bit
2890  DECLARE @CurrentStatisticsExists bit
2891  DECLARE @CurrentIsImageText bit
2892  DECLARE @CurrentIsNewLOB bit
2893  DECLARE @CurrentIsFileStream bit
2894  DECLARE @CurrentIsColumnStore bit
2895  DECLARE @CurrentAllowPageLocks bit
2896  DECLARE @CurrentNoRecompute bit
2897  DECLARE @CurrentStatisticsModified bit
2898  DECLARE @CurrentOnReadOnlyFileGroup bit
2899  DECLARE @CurrentFragmentationLevel float
2900  DECLARE @CurrentPageCount bigint
2901  DECLARE @CurrentFragmentationGroup nvarchar(max)
2902  DECLARE @CurrentAction nvarchar(max)
2903  DECLARE @CurrentMaxDOP int
2904  DECLARE @CurrentUpdateStatistics nvarchar(max)
2905  DECLARE @CurrentComment nvarchar(max)
2906  DECLARE @CurrentExtendedInfo xml
2907  DECLARE @CurrentDelay datetime
2908
2909  DECLARE @tmpDatabases TABLE (ID int IDENTITY,
2910                               DatabaseName nvarchar(max),
2911                               DatabaseType nvarchar(max),
2912                               Selected bit,
2913                               Completed bit,
2914                               PRIMARY KEY(Selected, Completed, ID))
2915
2916  DECLARE @tmpIndexesStatistics TABLE (ID int IDENTITY,
2917                                       SchemaID int,
2918                                       SchemaName nvarchar(max),
2919                                       ObjectID int,
2920                                       ObjectName nvarchar(max),
2921                                       ObjectType nvarchar(max),
2922                                       IsMemoryOptimized bit,
2923                                       IndexID int,
2924                                       IndexName nvarchar(max),
2925                                       IndexType int,
2926                                       StatisticsID int,
2927                                       StatisticsName nvarchar(max),
2928                                       PartitionID bigint,
2929                                       PartitionNumber int,
2930                                       PartitionCount int,
2931                                       Selected bit,
2932                                       Completed bit,
2933                                       PRIMARY KEY(Selected, Completed, ID))
2934
2935  DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
2936                                    DatabaseType nvarchar(max),
2937                                    Selected bit)
2938
2939  DECLARE @SelectedIndexes TABLE (DatabaseName nvarchar(max),
2940                                  SchemaName nvarchar(max),
2941                                  ObjectName nvarchar(max),
2942                                  IndexName nvarchar(max),
2943                                  Selected bit)
2944
2945  DECLARE @Actions TABLE ([Action] nvarchar(max))
2946
2947  INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')
2948  INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')
2949  INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')
2950
2951  DECLARE @ActionsPreferred TABLE (FragmentationGroup nvarchar(max),
2952                                   [Priority] int,
2953                                   [Action] nvarchar(max))
2954
2955  DECLARE @CurrentActionsAllowed TABLE ([Action] nvarchar(max))
2956
2957  DECLARE @Error int
2958  DECLARE @ReturnCode int
2959
2960  SET @Error = 0
2961  SET @ReturnCode = 0
2962
2963  SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
2964
2965  SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END
2966
2967  ----------------------------------------------------------------------------------------------------
2968  --// Log initial information                                                                    //--
2969  ----------------------------------------------------------------------------------------------------
2970
2971  SET @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
2972
2973  SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)
2974  SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)
2975  SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)
2976  SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)
2977  SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
2978  SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')
2979  SET @StartMessage = @StartMessage + ', @FragmentationLow = ' + ISNULL('''' + REPLACE(@FragmentationLow,'''','''''') + '''','NULL')
2980  SET @StartMessage = @StartMessage + ', @FragmentationMedium = ' + ISNULL('''' + REPLACE(@FragmentationMedium,'''','''''') + '''','NULL')
2981  SET @StartMessage = @StartMessage + ', @FragmentationHigh = ' + ISNULL('''' + REPLACE(@FragmentationHigh,'''','''''') + '''','NULL')
2982  SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS nvarchar),'NULL')
2983  SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS nvarchar),'NULL')
2984  SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS nvarchar),'NULL')
2985  SET @StartMessage = @StartMessage + ', @SortInTempdb = ' + ISNULL('''' + REPLACE(@SortInTempdb,'''','''''') + '''','NULL')
2986  SET @StartMessage = @StartMessage + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')
2987  SET @StartMessage = @StartMessage + ', @FillFactor = ' + ISNULL(CAST(@FillFactor AS nvarchar),'NULL')
2988  SET @StartMessage = @StartMessage + ', @PadIndex = ' + ISNULL('''' + REPLACE(@PadIndex,'''','''''') + '''','NULL')
2989  SET @StartMessage = @StartMessage + ', @LOBCompaction = ' + ISNULL('''' + REPLACE(@LOBCompaction,'''','''''') + '''','NULL')
2990  SET @StartMessage = @StartMessage + ', @UpdateStatistics = ' + ISNULL('''' + REPLACE(@UpdateStatistics,'''','''''') + '''','NULL')
2991  SET @StartMessage = @StartMessage + ', @OnlyModifiedStatistics = ' + ISNULL('''' + REPLACE(@OnlyModifiedStatistics,'''','''''') + '''','NULL')
2992  SET @StartMessage = @StartMessage + ', @StatisticsSample = ' + ISNULL(CAST(@StatisticsSample AS nvarchar),'NULL')
2993  SET @StartMessage = @StartMessage + ', @StatisticsResample = ' + ISNULL('''' + REPLACE(@StatisticsResample,'''','''''') + '''','NULL')
2994  SET @StartMessage = @StartMessage + ', @PartitionLevel = ' + ISNULL('''' + REPLACE(@PartitionLevel,'''','''''') + '''','NULL')
2995  SET @StartMessage = @StartMessage + ', @MSShippedObjects = ' + ISNULL('''' + REPLACE(@MSShippedObjects,'''','''''') + '''','NULL')
2996  SET @StartMessage = @StartMessage + ', @Indexes = ' + ISNULL('''' + REPLACE(@Indexes,'''','''''') + '''','NULL')
2997  SET @StartMessage = @StartMessage + ', @TimeLimit = ' + ISNULL(CAST(@TimeLimit AS nvarchar),'NULL')
2998  SET @StartMessage = @StartMessage + ', @Delay = ' + ISNULL(CAST(@Delay AS nvarchar),'NULL')
2999  SET @StartMessage = @StartMessage + ', @WaitAtLowPriorityMaxDuration = ' + ISNULL(CAST(@WaitAtLowPriorityMaxDuration AS nvarchar),'NULL')
3000  SET @StartMessage = @StartMessage + ', @WaitAtLowPriorityAbortAfterWait = ' + ISNULL('''' + REPLACE(@WaitAtLowPriorityAbortAfterWait,'''','''''') + '''','NULL')
3001  SET @StartMessage = @StartMessage + ', @LockTimeout = ' + ISNULL(CAST(@LockTimeout AS nvarchar),'NULL')
3002  SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')
3003  SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)
3004  SET @StartMessage = @StartMessage + 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10)
3005  SET @StartMessage = REPLACE(@StartMessage,'%','%%') + ' '
3006  RAISERROR(@StartMessage,10,1) WITH NOWAIT
3007
3008  ----------------------------------------------------------------------------------------------------
3009  --// Check core requirements                                                                    //--
3010  ----------------------------------------------------------------------------------------------------
3011
3012  IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute')
3013  BEGIN
3014    SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
3015    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3016    SET @Error = @@ERROR
3017  END
3018
3019  IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute' AND (OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))
3020  BEGIN
3021    SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
3022    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3023    SET @Error = @@ERROR
3024  END
3025
3026  IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')
3027  BEGIN
3028    SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
3029    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3030    SET @Error = @@ERROR
3031  END
3032
3033  IF SERVERPROPERTY('EngineEdition') = 5 AND @Version < 12
3034  BEGIN
3035    SET @ErrorMessage = 'The stored procedure IndexOptimize is not supported on this version of Azure SQL Database.' + CHAR(13) + CHAR(10) + ' '
3036    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3037    SET @Error = @@ERROR
3038  END
3039
3040  IF @Error <> 0
3041  BEGIN
3042    SET @ReturnCode = @Error
3043    GOTO Logging
3044  END
3045
3046  ----------------------------------------------------------------------------------------------------
3047  --// Select databases                                                                           //--
3048  ----------------------------------------------------------------------------------------------------
3049
3050  SET @Databases = REPLACE(@Databases, ', ', ',');
3051
3052  WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
3053  (
3054  SELECT 1 AS StartPosition,
3055         ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
3056         SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem
3057  WHERE @Databases IS NOT NULL
3058  UNION ALL
3059  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
3060         ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
3061         SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem
3062  FROM Databases1
3063  WHERE EndPosition < LEN(@Databases) + 1
3064  ),
3065  Databases2 (DatabaseItem, Selected) AS
3066  (
3067  SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
3068         CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
3069  FROM Databases1
3070  ),
3071  Databases3 (DatabaseItem, DatabaseType, Selected) AS
3072  (
3073  SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
3074         CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
3075         Selected
3076  FROM Databases2
3077  ),
3078  Databases4 (DatabaseName, DatabaseType, Selected) AS
3079  (
3080  SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
3081         DatabaseType,
3082         Selected
3083  FROM Databases3
3084  )
3085  INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
3086  SELECT DatabaseName,
3087         DatabaseType,
3088         Selected
3089  FROM Databases4
3090  OPTION (MAXRECURSION 0)
3091
3092  INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, Selected, Completed)
3093  SELECT [name] AS DatabaseName,
3094         CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
3095         0 AS Selected,
3096         0 AS Completed
3097  FROM sys.databases
3098  WHERE [name] <> 'tempdb'
3099  AND source_database_id IS NULL
3100  ORDER BY [name] ASC
3101
3102  UPDATE tmpDatabases
3103  SET tmpDatabases.Selected = SelectedDatabases.Selected
3104  FROM @tmpDatabases tmpDatabases
3105  INNER JOIN @SelectedDatabases SelectedDatabases
3106  ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
3107  AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
3108  WHERE SelectedDatabases.Selected = 1
3109
3110  UPDATE tmpDatabases
3111  SET tmpDatabases.Selected = SelectedDatabases.Selected
3112  FROM @tmpDatabases tmpDatabases
3113  INNER JOIN @SelectedDatabases SelectedDatabases
3114  ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
3115  AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
3116  WHERE SelectedDatabases.Selected = 0
3117
3118  IF @Databases IS NULL OR NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')
3119  BEGIN
3120    SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '
3121    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3122    SET @Error = @@ERROR
3123  END
3124
3125  ----------------------------------------------------------------------------------------------------
3126  --// Select indexes                                                                             //--
3127  ----------------------------------------------------------------------------------------------------
3128
3129  SET @Indexes = REPLACE(@Indexes, ', ', ',');
3130
3131  WITH Indexes1 (StartPosition, EndPosition, IndexItem) AS
3132  (
3133  SELECT 1 AS StartPosition,
3134         ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) AS EndPosition,
3135         SUBSTRING(@Indexes, 1, ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) - 1) AS IndexItem
3136  WHERE @Indexes IS NOT NULL
3137  UNION ALL
3138  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
3139         ISNULL(NULLIF(CHARINDEX(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) AS EndPosition,
3140         SUBSTRING(@Indexes, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) - EndPosition - 1) AS IndexItem
3141  FROM Indexes1
3142  WHERE EndPosition < LEN(@Indexes) + 1
3143  ),
3144  Indexes2 (IndexItem, Selected) AS
3145  (
3146  SELECT CASE WHEN IndexItem LIKE '-%' THEN RIGHT(IndexItem,LEN(IndexItem) - 1) ELSE IndexItem END AS IndexItem,
3147         CASE WHEN IndexItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
3148  FROM Indexes1
3149  ),
3150  Indexes3 (IndexItem, Selected) AS
3151  (
3152  SELECT CASE WHEN IndexItem = 'ALL_INDEXES' THEN '%.%.%.%' ELSE IndexItem END AS IndexItem,
3153         Selected
3154  FROM Indexes2
3155  ),
3156  Indexes4 (DatabaseName, SchemaName, ObjectName, IndexName, Selected) AS
3157  (
3158  SELECT CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END AS DatabaseName,
3159         CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END AS SchemaName,
3160         CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END AS ObjectName,
3161         CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END AS IndexName,
3162         Selected
3163  FROM Indexes3
3164  )
3165  INSERT INTO @SelectedIndexes (DatabaseName, SchemaName, ObjectName, IndexName, Selected)
3166  SELECT DatabaseName, SchemaName, ObjectName, IndexName, Selected
3167  FROM Indexes4
3168  OPTION (MAXRECURSION 0);
3169
3170  ----------------------------------------------------------------------------------------------------
3171  --// Select actions                                                                             //--
3172  ----------------------------------------------------------------------------------------------------
3173
3174  WITH FragmentationLow (StartPosition, EndPosition, [Action]) AS
3175  (
3176  SELECT 1 AS StartPosition,
3177         ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,
3178         SUBSTRING(@FragmentationLow, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) - 1) AS [Action]
3179  WHERE @FragmentationLow IS NOT NULL
3180  UNION ALL
3181  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
3182         ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,
3183         SUBSTRING(@FragmentationLow, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) - EndPosition - 1) AS [Action]
3184  FROM FragmentationLow
3185  WHERE EndPosition < LEN(@FragmentationLow) + 1
3186  )
3187  INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])
3188  SELECT 'Low' AS FragmentationGroup,
3189         ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],
3190         [Action]
3191  FROM FragmentationLow
3192  OPTION (MAXRECURSION 0);
3193
3194  WITH FragmentationMedium (StartPosition, EndPosition, [Action]) AS
3195  (
3196  SELECT 1 AS StartPosition,
3197         ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,
3198         SUBSTRING(@FragmentationMedium, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) - 1) AS [Action]
3199  WHERE @FragmentationMedium IS NOT NULL
3200  UNION ALL
3201  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
3202         ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,
3203         SUBSTRING(@FragmentationMedium, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) - EndPosition - 1) AS [Action]
3204  FROM FragmentationMedium
3205  WHERE EndPosition < LEN(@FragmentationMedium) + 1
3206  )
3207  INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])
3208  SELECT 'Medium' AS FragmentationGroup,
3209         ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],
3210         [Action]
3211  FROM FragmentationMedium
3212  OPTION (MAXRECURSION 0);
3213
3214  WITH FragmentationHigh (StartPosition, EndPosition, [Action]) AS
3215  (
3216  SELECT 1 AS StartPosition,
3217         ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,
3218         SUBSTRING(@FragmentationHigh, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) - 1) AS [Action]
3219  WHERE @FragmentationHigh IS NOT NULL
3220  UNION ALL
3221  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
3222         ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,
3223         SUBSTRING(@FragmentationHigh, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) - EndPosition - 1) AS [Action]
3224  FROM FragmentationHigh
3225  WHERE EndPosition < LEN(@FragmentationHigh) + 1
3226  )
3227  INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])
3228  SELECT 'High' AS FragmentationGroup,
3229         ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],
3230         [Action]
3231  FROM FragmentationHigh
3232  OPTION (MAXRECURSION 0)
3233
3234  ----------------------------------------------------------------------------------------------------
3235  --// Check input parameters                                                                     //--
3236  ----------------------------------------------------------------------------------------------------
3237
3238  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' AND [Action] NOT IN(SELECT * FROM @Actions))
3239  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' GROUP BY [Action] HAVING COUNT(*) > 1)
3240  BEGIN
3241    SET @ErrorMessage = 'The value for the parameter @FragmentationLow is not supported.' + CHAR(13) + CHAR(10) + ' '
3242    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3243    SET @Error = @@ERROR
3244  END
3245
3246  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' AND [Action] NOT IN(SELECT * FROM @Actions))
3247  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' GROUP BY [Action] HAVING COUNT(*) > 1)
3248  BEGIN
3249    SET @ErrorMessage = 'The value for the parameter @FragmentationMedium is not supported.' + CHAR(13) + CHAR(10) + ' '
3250    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3251    SET @Error = @@ERROR
3252  END
3253
3254  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'High' AND [Action] NOT IN(SELECT * FROM @Actions))
3255  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'High' GROUP BY [Action] HAVING COUNT(*) > 1)
3256  BEGIN
3257    SET @ErrorMessage = 'The value for the parameter @FragmentationHigh is not supported.' + CHAR(13) + CHAR(10) + ' '
3258    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3259    SET @Error = @@ERROR
3260  END
3261
3262  IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL
3263  BEGIN
3264    SET @ErrorMessage = 'The value for the parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10) + ' '
3265    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3266    SET @Error = @@ERROR
3267  END
3268
3269  IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL
3270  BEGIN
3271    SET @ErrorMessage = 'The value for the parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10) + ' '
3272    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3273    SET @Error = @@ERROR
3274  END
3275
3276  IF @PageCountLevel < 0 OR @PageCountLevel IS NULL
3277  BEGIN
3278    SET @ErrorMessage = 'The value for the parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10) + ' '
3279    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3280    SET @Error = @@ERROR
3281  END
3282
3283  IF @SortInTempdb NOT IN('Y','N') OR @SortInTempdb IS NULL
3284  BEGIN
3285    SET @ErrorMessage = 'The value for the parameter @SortInTempdb is not supported.' + CHAR(13) + CHAR(10) + ' '
3286    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3287    SET @Error = @@ERROR
3288  END
3289
3290  IF @MaxDOP < 0 OR @MaxDOP > 64 OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') NOT IN (3,5))
3291  BEGIN
3292    SET @ErrorMessage = 'The value for the parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '
3293    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3294    SET @Error = @@ERROR
3295  END
3296
3297  IF @FillFactor <= 0 OR @FillFactor > 100
3298  BEGIN
3299    SET @ErrorMessage = 'The value for the parameter @FillFactor is not supported.' + CHAR(13) + CHAR(10) + ' '
3300    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3301    SET @Error = @@ERROR
3302  END
3303
3304  IF @PadIndex NOT IN('Y','N')
3305  BEGIN
3306    SET @ErrorMessage = 'The value for the parameter @PadIndex is not supported.' + CHAR(13) + CHAR(10) + ' '
3307    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3308    SET @Error = @@ERROR
3309  END
3310
3311  IF @LOBCompaction NOT IN('Y','N') OR @LOBCompaction IS NULL
3312  BEGIN
3313    SET @ErrorMessage = 'The value for the parameter @LOBCompaction is not supported.' + CHAR(13) + CHAR(10) + ' '
3314    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3315    SET @Error = @@ERROR
3316  END
3317
3318  IF @UpdateStatistics NOT IN('ALL','COLUMNS','INDEX')
3319  BEGIN
3320    SET @ErrorMessage = 'The value for the parameter @UpdateStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '
3321    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3322    SET @Error = @@ERROR
3323  END
3324
3325  IF @OnlyModifiedStatistics NOT IN('Y','N') OR @OnlyModifiedStatistics IS NULL
3326  BEGIN
3327    SET @ErrorMessage = 'The value for the parameter @OnlyModifiedStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '
3328    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3329    SET @Error = @@ERROR
3330  END
3331
3332  IF @StatisticsSample <= 0 OR @StatisticsSample  > 100
3333  BEGIN
3334    SET @ErrorMessage = 'The value for the parameter @StatisticsSample is not supported.' + CHAR(13) + CHAR(10) + ' '
3335    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3336    SET @Error = @@ERROR
3337  END
3338
3339  IF @StatisticsResample NOT IN('Y','N') OR @StatisticsResample IS NULL OR (@StatisticsResample = 'Y' AND @StatisticsSample IS NOT NULL)
3340  BEGIN
3341    SET @ErrorMessage = 'The value for the parameter @StatisticsResample is not supported.' + CHAR(13) + CHAR(10) + ' '
3342    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3343    SET @Error = @@ERROR
3344  END
3345
3346  IF @PartitionLevel NOT IN('Y','N') OR @PartitionLevel IS NULL
3347  BEGIN
3348    SET @ErrorMessage = 'The value for the parameter @PartitionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '
3349    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3350    SET @Error = @@ERROR
3351  END
3352
3353  IF @MSShippedObjects NOT IN('Y','N') OR @MSShippedObjects IS NULL
3354  BEGIN
3355    SET @ErrorMessage = 'The value for the parameter @MSShippedObjects is not supported.' + CHAR(13) + CHAR(10) + ' '
3356    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3357    SET @Error = @@ERROR
3358  END
3359
3360  IF EXISTS(SELECT * FROM @SelectedIndexes WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL OR IndexName IS NULL) OR (@Indexes IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedIndexes))
3361  BEGIN
3362    SET @ErrorMessage = 'The value for the parameter @Indexes is not supported.' + CHAR(13) + CHAR(10) + ' '
3363    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3364    SET @Error = @@ERROR
3365  END
3366
3367  IF @TimeLimit < 0
3368  BEGIN
3369    SET @ErrorMessage = 'The value for the parameter @TimeLimit is not supported.' + CHAR(13) + CHAR(10) + ' '
3370    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3371    SET @Error = @@ERROR
3372  END
3373
3374  IF @Delay < 0
3375  BEGIN
3376    SET @ErrorMessage = 'The value for the parameter @Delay is not supported.' + CHAR(13) + CHAR(10) + ' '
3377    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3378    SET @Error = @@ERROR
3379  END
3380
3381  IF @WaitAtLowPriorityMaxDuration < 0 OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @WaitAtLowPriorityAbortAfterWait IS NULL) OR (@WaitAtLowPriorityMaxDuration IS NULL AND @WaitAtLowPriorityAbortAfterWait IS NOT NULL)
3382  BEGIN
3383    SET @ErrorMessage = 'The value for the parameter @WaitAtLowPriorityMaxDuration is not supported.' + CHAR(13) + CHAR(10) + ' '
3384    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3385    SET @Error = @@ERROR
3386  END
3387
3388  IF @WaitAtLowPriorityAbortAfterWait NOT IN('NONE','SELF','BLOCKERS') OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @WaitAtLowPriorityMaxDuration IS NULL) OR (@WaitAtLowPriorityAbortAfterWait IS NULL AND @WaitAtLowPriorityMaxDuration IS NOT NULL)
3389  BEGIN
3390    SET @ErrorMessage = 'The value for the parameter @WaitAtLowPriorityAbortAfterWait is not supported.' + CHAR(13) + CHAR(10) + ' '
3391    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3392    SET @Error = @@ERROR
3393  END
3394
3395  IF @LockTimeout < 0
3396  BEGIN
3397    SET @ErrorMessage = 'The value for the parameter @LockTimeout is not supported.' + CHAR(13) + CHAR(10) + ' '
3398    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3399    SET @Error = @@ERROR
3400  END
3401
3402  IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
3403  BEGIN
3404    SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '
3405    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3406    SET @Error = @@ERROR
3407  END
3408
3409  IF @Execute NOT IN('Y','N') OR @Execute IS NULL
3410  BEGIN
3411    SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '
3412    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3413    SET @Error = @@ERROR
3414  END
3415
3416  IF @Error <> 0
3417  BEGIN
3418    SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.' + CHAR(13) + CHAR(10) + ' '
3419    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3420    SET @ReturnCode = @Error
3421    GOTO Logging
3422  END
3423
3424  ----------------------------------------------------------------------------------------------------
3425  --// Check Availability Group cluster name                                                      //--
3426  ----------------------------------------------------------------------------------------------------
3427
3428  IF @Version >= 11 AND SERVERPROPERTY('EngineEdition') <> 5
3429  BEGIN
3430    SELECT @Cluster = cluster_name
3431    FROM sys.dm_hadr_cluster
3432  END
3433
3434  ----------------------------------------------------------------------------------------------------
3435  --// Execute commands                                                                           //--
3436  ----------------------------------------------------------------------------------------------------
3437
3438  WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)
3439  BEGIN
3440
3441    SELECT TOP 1 @CurrentDBID = ID,
3442                 @CurrentDatabaseName = DatabaseName
3443    FROM @tmpDatabases
3444    WHERE Selected = 1
3445    AND Completed = 0
3446    ORDER BY ID ASC
3447
3448    SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)
3449
3450    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE' AND SERVERPROPERTY('EngineEdition') <> 5
3451    BEGIN
3452      IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)
3453      BEGIN
3454        SET @CurrentIsDatabaseAccessible = 1
3455      END
3456      ELSE
3457      BEGIN
3458        SET @CurrentIsDatabaseAccessible = 0
3459      END
3460    END
3461
3462    IF @Version >= 11 AND @Cluster IS NOT NULL
3463    BEGIN
3464      SELECT @CurrentAvailabilityGroup = availability_groups.name,
3465             @CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc
3466      FROM sys.databases databases
3467      INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
3468      INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
3469      INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
3470      WHERE databases.name = @CurrentDatabaseName
3471    END
3472
3473    IF SERVERPROPERTY('EngineEdition') <> 5
3474    BEGIN
3475      SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
3476      FROM sys.database_mirroring
3477      WHERE database_id = @CurrentDatabaseID
3478    END
3479
3480    -- Set database message
3481    SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
3482    SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)
3483    SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)
3484    SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
3485    SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)
3486    SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)
3487    IF @CurrentIsDatabaseAccessible IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
3488    SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)
3489    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group: ' + @CurrentAvailabilityGroup + CHAR(13) + CHAR(10)
3490    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group role: ' + @CurrentAvailabilityGroupRole + CHAR(13) + CHAR(10)
3491    IF @CurrentDatabaseMirroringRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Database mirroring role: ' + @CurrentDatabaseMirroringRole + CHAR(13) + CHAR(10)
3492    SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%') + ' '
3493    RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
3494
3495    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
3496    AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
3497    AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'
3498    BEGIN
3499
3500      -- Select indexes in the current database
3501      IF (EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL) AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
3502      BEGIN
3503        SET @CurrentCommand01 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed FROM ('
3504
3505        IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')
3506        BEGIN
3507          SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE 'NULL' END + ' AS IsMemoryOptimized, indexes.index_id AS IndexID, indexes.[name] AS IndexName, indexes.[type] AS IndexType, stats.stats_id AS StatisticsID, stats.name AS StatisticsName'
3508          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', partitions.partition_id AS PartitionID, partitions.partition_number AS PartitionNumber, IndexPartitions.partition_count AS PartitionCount'
3509          IF @PartitionLevel = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount'
3510          SET @CurrentCommand01 = @CurrentCommand01 + ', 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.[object_id] = tables.[object_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]'
3511          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(*) AS partition_count FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id]'
3512          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.dm_db_partition_stats dm_db_partition_stats ON indexes.[object_id] = dm_db_partition_stats.[object_id] AND indexes.[index_id] = dm_db_partition_stats.[index_id] AND partitions.partition_id = dm_db_partition_stats.partition_id'
3513          IF @PartitionLevel = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN (SELECT dm_db_partition_stats.[object_id], dm_db_partition_stats.[index_id], SUM(dm_db_partition_stats.in_row_data_page_count) AS in_row_data_page_count FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.dm_db_partition_stats dm_db_partition_stats GROUP BY dm_db_partition_stats.[object_id], dm_db_partition_stats.[index_id]) dm_db_partition_stats ON indexes.[object_id] = dm_db_partition_stats.[object_id] AND indexes.[index_id] = dm_db_partition_stats.[index_id]'
3514          SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0'
3515          IF (@UpdateStatistics NOT IN('ALL','INDEX') OR @UpdateStatistics IS NULL) AND @PageCountLevel > 0 SET @CurrentCommand01 = @CurrentCommand01 + ' AND (dm_db_partition_stats.in_row_data_page_count >= @ParamPageCountLevel OR dm_db_partition_stats.in_row_data_page_count IS NULL)'
3516          IF NOT EXISTS(SELECT * FROM @ActionsPreferred) SET @CurrentCommand01 = @CurrentCommand01 + ' AND stats.stats_id IS NOT NULL'
3517        END
3518
3519        IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL' SET @CurrentCommand01 = @CurrentCommand01 + ' UNION '
3520
3521        IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE 'NULL' END + ' AS IsMemoryOptimized, NULL AS IndexID, NULL AS IndexName, NULL AS IndexType, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.[object_id] = tables.[object_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND NOT EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)'
3522
3523        SET @CurrentCommand01 = @CurrentCommand01 + ') IndexesStatistics ORDER BY SchemaName ASC, ObjectName ASC'
3524        IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL' SET @CurrentCommand01 = @CurrentCommand01 + ', CASE WHEN IndexType IS NULL THEN 1 ELSE 0 END ASC'
3525        IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX') SET @CurrentCommand01 = @CurrentCommand01 + ', IndexType ASC, IndexName ASC'
3526        IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommand01 = @CurrentCommand01 + ', StatisticsName ASC'
3527        IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PartitionNumber ASC'
3528
3529        INSERT INTO @tmpIndexesStatistics (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed)
3530        EXECUTE sp_executesql @statement = @CurrentCommand01, @params = N'@ParamPageCountLevel int', @ParamPageCountLevel = @PageCountLevel
3531        SET @Error = @@ERROR
3532        IF @Error <> 0
3533        BEGIN
3534          SET @ReturnCode = @Error
3535        END
3536      END
3537
3538      IF @Indexes IS NULL
3539      BEGIN
3540        UPDATE tmpIndexesStatistics
3541        SET tmpIndexesStatistics.Selected = 1
3542        FROM @tmpIndexesStatistics tmpIndexesStatistics
3543      END
3544      ELSE
3545      BEGIN
3546        UPDATE tmpIndexesStatistics
3547        SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected
3548        FROM @tmpIndexesStatistics tmpIndexesStatistics
3549        INNER JOIN @SelectedIndexes SelectedIndexes
3550        ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')
3551        WHERE SelectedIndexes.Selected = 1
3552
3553        UPDATE tmpIndexesStatistics
3554        SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected
3555        FROM @tmpIndexesStatistics tmpIndexesStatistics
3556        INNER JOIN @SelectedIndexes SelectedIndexes
3557        ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')
3558        WHERE SelectedIndexes.Selected = 0
3559      END
3560
3561      WHILE EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE Selected = 1 AND Completed = 0 AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL))
3562      BEGIN
3563
3564        SELECT TOP 1 @CurrentIxID = ID,
3565                     @CurrentSchemaID = SchemaID,
3566                     @CurrentSchemaName = SchemaName,
3567                     @CurrentObjectID = ObjectID,
3568                     @CurrentObjectName = ObjectName,
3569                     @CurrentObjectType = ObjectType,
3570                     @CurrentIsMemoryOptimized = IsMemoryOptimized,
3571                     @CurrentIndexID = IndexID,
3572                     @CurrentIndexName = IndexName,
3573                     @CurrentIndexType = IndexType,
3574                     @CurrentStatisticsID = StatisticsID,
3575                     @CurrentStatisticsName = StatisticsName,
3576                     @CurrentPartitionID = PartitionID,
3577                     @CurrentPartitionNumber = PartitionNumber,
3578                     @CurrentPartitionCount = PartitionCount
3579        FROM @tmpIndexesStatistics
3580        WHERE Selected = 1
3581        AND Completed = 0
3582        ORDER BY ID ASC
3583
3584        -- Is the index a partition?
3585        IF @CurrentPartitionNumber IS NULL OR @CurrentPartitionCount = 1 BEGIN SET @CurrentIsPartition = 0 END ELSE BEGIN SET @CurrentIsPartition = 1 END
3586
3587        -- Does the index exist?
3588        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
3589        BEGIN
3590          SET @CurrentCommand02 = ''
3591          IF @LockTimeout IS NOT NULL SET @CurrentCommand02 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3592          IF @CurrentIsPartition = 0 SET @CurrentCommand02 = @CurrentCommand02 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType) BEGIN SET @ParamIndexExists = 1 END'
3593          IF @CurrentIsPartition = 1 SET @CurrentCommand02 = @CurrentCommand02 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType AND partitions.partition_id = @ParamPartitionID AND partitions.partition_number = @ParamPartitionNumber) BEGIN SET @ParamIndexExists = 1 END'
3594
3595          EXECUTE sp_executesql @statement = @CurrentCommand02, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamIndexID int, @ParamIndexName sysname, @ParamIndexType int, @ParamPartitionID bigint, @ParamPartitionNumber int, @ParamIndexExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamIndexID = @CurrentIndexID, @ParamIndexName = @CurrentIndexName, @ParamIndexType = @CurrentIndexType, @ParamPartitionID = @CurrentPartitionID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamIndexExists = @CurrentIndexExists OUTPUT
3596          SET @Error = @@ERROR
3597          IF @Error = 0 AND @CurrentIndexExists IS NULL SET @CurrentIndexExists = 0
3598          IF @Error = 1222
3599          BEGIN
3600            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index exists.' + CHAR(13) + CHAR(10) + ' '
3601            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3602            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3603          END
3604          IF @Error <> 0
3605          BEGIN
3606            SET @ReturnCode = @Error
3607            GOTO NoAction
3608          END
3609          IF @CurrentIndexExists = 0 GOTO NoAction
3610        END
3611
3612        -- Does the statistics exist?
3613        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL
3614        BEGIN
3615          SET @CurrentCommand03 = ''
3616          IF @LockTimeout IS NOT NULL SET @CurrentCommand03 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3617          SET @CurrentCommand03 = @CurrentCommand03 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND stats.stats_id = @ParamStatisticsID AND stats.[name] = @ParamStatisticsName) BEGIN SET @ParamStatisticsExists = 1 END'
3618
3619          EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamStatisticsID int, @ParamStatisticsName sysname, @ParamStatisticsExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsName = @CurrentStatisticsName, @ParamStatisticsExists = @CurrentStatisticsExists OUTPUT
3620          SET @Error = @@ERROR
3621          IF @Error = 0 AND @CurrentStatisticsExists IS NULL SET @CurrentStatisticsExists = 0
3622          IF @Error = 1222
3623          BEGIN
3624            SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the statistics exists.' + CHAR(13) + CHAR(10) + ' '
3625            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3626            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3627          END
3628          IF @Error <> 0
3629          BEGIN
3630            SET @ReturnCode = @Error
3631            GOTO NoAction
3632          END
3633          IF @CurrentStatisticsExists = 0 GOTO NoAction
3634        END
3635
3636        -- Is one of the columns in the index an image, text or ntext data type?
3637        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType = 1 AND EXISTS(SELECT * FROM @ActionsPreferred)
3638        BEGIN
3639          SET @CurrentCommand04 = ''
3640          IF @LockTimeout IS NOT NULL SET @CurrentCommand04 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3641          SET @CurrentCommand04 = @CurrentCommand04 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = @ParamObjectID AND types.name IN(''image'',''text'',''ntext'')) BEGIN SET @ParamIsImageText = 1 END'
3642
3643          EXECUTE sp_executesql @statement = @CurrentCommand04, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsImageText bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsImageText = @CurrentIsImageText OUTPUT
3644          SET @Error = @@ERROR
3645          IF @Error = 0 AND @CurrentIsImageText IS NULL SET @CurrentIsImageText = 0
3646          IF @Error = 1222
3647          BEGIN
3648            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any image, text, or ntext data types.' + CHAR(13) + CHAR(10) + ' '
3649            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3650            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3651          END
3652          IF @Error <> 0
3653          BEGIN
3654            SET @ReturnCode = @Error
3655            GOTO NoAction
3656          END
3657        END
3658
3659        -- Is one of the columns in the index an xml, varchar(max), nvarchar(max), varbinary(max) or large CLR data type?
3660        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN(1,2) AND EXISTS(SELECT * FROM @ActionsPreferred)
3661        BEGIN
3662          SET @CurrentCommand05 = ''
3663          IF @LockTimeout IS NOT NULL SET @CurrentCommand05 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3664          IF @CurrentIndexType = 1 SET @CurrentCommand05 = @CurrentCommand05 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = @ParamObjectID AND (types.name IN(''xml'') OR (types.name IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) BEGIN SET @ParamIsNewLOB = 1 END'
3665          IF @CurrentIndexType = 2 SET @CurrentCommand05 = @CurrentCommand05 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.index_columns index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = @ParamObjectID AND index_columns.index_id = @ParamIndexID AND (types.[name] IN(''xml'') OR (types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) BEGIN SET @ParamIsNewLOB = 1 END'
3666
3667          EXECUTE sp_executesql @statement = @CurrentCommand05, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsNewLOB bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsNewLOB = @CurrentIsNewLOB OUTPUT
3668          SET @Error = @@ERROR
3669          IF @Error = 0 AND @CurrentIsNewLOB IS NULL SET @CurrentIsNewLOB = 0
3670          IF @Error = 1222
3671          BEGIN
3672            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any xml, varchar(max), nvarchar(max), varbinary(max), or large CLR data types.' + CHAR(13) + CHAR(10) + ' '
3673            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3674            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3675          END
3676          IF @Error <> 0
3677          BEGIN
3678            SET @ReturnCode = @Error
3679            GOTO NoAction
3680          END
3681        END
3682
3683        -- Is one of the columns in the index a file stream column?
3684        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType = 1 AND EXISTS(SELECT * FROM @ActionsPreferred)
3685        BEGIN
3686          SET @CurrentCommand06 = ''
3687          IF @LockTimeout IS NOT NULL SET @CurrentCommand06 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3688          SET @CurrentCommand06 = @CurrentCommand06 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns WHERE columns.[object_id] = @ParamObjectID  AND columns.is_filestream = 1) BEGIN SET @ParamIsFileStream = 1 END'
3689
3690          EXECUTE sp_executesql @statement = @CurrentCommand06, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsFileStream bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsFileStream = @CurrentIsFileStream OUTPUT
3691          SET @Error = @@ERROR
3692          IF @Error = 0 AND @CurrentIsFileStream IS NULL SET @CurrentIsFileStream = 0
3693          IF @Error = 1222
3694          BEGIN
3695            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any file stream columns.' + CHAR(13) + CHAR(10) + ' '
3696            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3697            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3698          END
3699          IF @Error <> 0
3700          BEGIN
3701            SET @ReturnCode = @Error
3702            GOTO NoAction
3703          END
3704        END
3705
3706        -- Is there a columnstore index on the table?
3707        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred) AND @Version >= 11
3708        BEGIN
3709          SET @CurrentCommand07 = ''
3710          IF @LockTimeout IS NOT NULL SET @CurrentCommand07 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3711          SET @CurrentCommand07 = @CurrentCommand07 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = @ParamObjectID AND [type] IN(5,6)) BEGIN SET @ParamIsColumnStore = 1 END'
3712
3713          EXECUTE sp_executesql @statement = @CurrentCommand07, @params = N'@ParamObjectID int, @ParamIsColumnStore bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIsColumnStore = @CurrentIsColumnStore OUTPUT
3714          SET @Error = @@ERROR
3715          IF @Error = 0 AND @CurrentIsColumnStore IS NULL SET @CurrentIsColumnStore = 0
3716          IF @Error = 1222
3717          BEGIN
3718            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if there is a columnstore index on the table.' + CHAR(13) + CHAR(10) + ' '
3719            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3720            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3721          END
3722          IF @Error <> 0
3723          BEGIN
3724            SET @ReturnCode = @Error
3725            GOTO NoAction
3726          END
3727        END
3728
3729        -- Is Allow_Page_Locks set to On?
3730        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
3731        BEGIN
3732          SET @CurrentCommand08 = ''
3733          IF @LockTimeout IS NOT NULL SET @CurrentCommand08 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3734          SET @CurrentCommand08 = @CurrentCommand08 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID AND indexes.[allow_page_locks] = 1) BEGIN SET @ParamAllowPageLocks = 1 END'
3735
3736          EXECUTE sp_executesql @statement = @CurrentCommand08, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamAllowPageLocks bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamAllowPageLocks = @CurrentAllowPageLocks OUTPUT
3737          SET @Error = @@ERROR
3738          IF @Error = 0 AND @CurrentAllowPageLocks IS NULL SET @CurrentAllowPageLocks = 0
3739          IF @Error = 1222
3740          BEGIN
3741            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if page locking is enabled on the index.' + CHAR(13) + CHAR(10) + ' '
3742            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3743            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3744          END
3745          IF @Error <> 0
3746          BEGIN
3747            SET @ReturnCode = @Error
3748            GOTO NoAction
3749          END
3750        END
3751
3752        -- Is No_Recompute set to On?
3753        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL
3754        BEGIN
3755          SET @CurrentCommand09 = ''
3756          IF @LockTimeout IS NOT NULL SET @CurrentCommand09 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3757          SET @CurrentCommand09 = @CurrentCommand09 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats WHERE stats.[object_id] = @ParamObjectID AND stats.[stats_id] = @ParamStatisticsID AND stats.[no_recompute] = 1) BEGIN SET @ParamNoRecompute = 1 END'
3758
3759          EXECUTE sp_executesql @statement = @CurrentCommand09, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamNoRecompute bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamNoRecompute = @CurrentNoRecompute OUTPUT
3760          SET @Error = @@ERROR
3761          IF @Error = 0 AND @CurrentNoRecompute IS NULL SET @CurrentNoRecompute = 0
3762          IF @Error = 1222
3763          BEGIN
3764            SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if automatic statistics update is enabled.' + CHAR(13) + CHAR(10) + ' '
3765            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3766            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3767          END
3768          IF @Error <> 0
3769          BEGIN
3770            SET @ReturnCode = @Error
3771            GOTO NoAction
3772          END
3773        END
3774
3775        -- Has the data in the statistics been modified since the statistics was last updated?
3776        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL AND @OnlyModifiedStatistics = 'Y'
3777        BEGIN
3778          SET @CurrentCommand10 = ''
3779          IF @LockTimeout IS NOT NULL SET @CurrentCommand10 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3780          IF (@Version >= 10.504000 AND @Version < 11) OR @Version >= 11.03000
3781          BEGIN
3782            SET @CurrentCommand10 = @CurrentCommand10 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; IF EXISTS(SELECT * FROM sys.dm_db_stats_properties (@ParamObjectID, @ParamStatisticsID) WHERE modification_counter > 0) BEGIN SET @ParamStatisticsModified = 1 END'
3783          END
3784          ELSE
3785          BEGIN
3786            SET @CurrentCommand10 = @CurrentCommand10 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.sysindexes sysindexes WHERE sysindexes.[id] = @ParamObjectID AND sysindexes.[indid] = @ParamStatisticsID AND sysindexes.[rowmodctr] <> 0) BEGIN SET @ParamStatisticsModified = 1 END'
3787          END
3788
3789          EXECUTE sp_executesql @statement = @CurrentCommand10, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamStatisticsModified bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsModified = @CurrentStatisticsModified OUTPUT
3790          SET @Error = @@ERROR
3791          IF @Error = 0 AND @CurrentStatisticsModified IS NULL SET @CurrentStatisticsModified = 0
3792          IF @Error = 1222
3793          BEGIN
3794            SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if any rows has been modified since the most recent statistics update.' + CHAR(13) + CHAR(10) + ' '
3795            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3796            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3797          END
3798          IF @Error <> 0
3799          BEGIN
3800            SET @ReturnCode = @Error
3801            GOTO NoAction
3802          END
3803        END
3804
3805        -- Is the index on a read-only filegroup?
3806        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
3807        BEGIN
3808          SET @CurrentCommand11 = ''
3809          IF @LockTimeout IS NOT NULL SET @CurrentCommand11 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3810          SET @CurrentCommand11 = @CurrentCommand11 + 'IF EXISTS(SELECT * FROM (SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID'
3811          IF @CurrentIsPartition = 1 SET @CurrentCommand11 = @CurrentCommand11 + ' AND destination_data_spaces.destination_id = @ParamPartitionNumber'
3812          SET @CurrentCommand11 = @CurrentCommand11 + ' UNION SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID'
3813          IF @CurrentIndexType = 1 SET @CurrentCommand11 = @CurrentCommand11 + ' UNION SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = @ParamObjectID'
3814          SET @CurrentCommand11 = @CurrentCommand11 + ') ReadOnlyFileGroups) BEGIN SET @ParamOnReadOnlyFileGroup = 1 END'
3815
3816          EXECUTE sp_executesql @statement = @CurrentCommand11, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamOnReadOnlyFileGroup bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamOnReadOnlyFileGroup = @CurrentOnReadOnlyFileGroup OUTPUT
3817          SET @Error = @@ERROR
3818          IF @Error = 0 AND @CurrentOnReadOnlyFileGroup IS NULL SET @CurrentOnReadOnlyFileGroup = 0
3819          IF @Error = 1222
3820          BEGIN
3821            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index is on a read-only filegroup.' + CHAR(13) + CHAR(10) + ' '
3822            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3823            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3824          END
3825          IF @Error <> 0
3826          BEGIN
3827            SET @ReturnCode = @Error
3828            GOTO NoAction
3829          END
3830        END
3831
3832        -- Is the index fragmented?
3833        IF @CurrentIndexID IS NOT NULL
3834        AND @CurrentOnReadOnlyFileGroup = 0
3835        AND EXISTS(SELECT * FROM @ActionsPreferred)
3836        AND (EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3) OR @PageCountLevel > 0)
3837        BEGIN
3838          SET @CurrentCommand12 = ''
3839          IF @LockTimeout IS NOT NULL SET @CurrentCommand12 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3840          SET @CurrentCommand12 = @CurrentCommand12 + 'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), @ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(@ParamDatabaseID, @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0'
3841
3842          EXECUTE sp_executesql @statement = @CurrentCommand12, @params = N'@ParamDatabaseID int, @ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamFragmentationLevel float OUTPUT, @ParamPageCount bigint OUTPUT', @ParamDatabaseID = @CurrentDatabaseID, @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamFragmentationLevel = @CurrentFragmentationLevel OUTPUT, @ParamPageCount = @CurrentPageCount OUTPUT
3843          SET @Error = @@ERROR
3844          IF @Error = 1222
3845          BEGIN
3846            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. The size and fragmentation of the index could not be checked.' + CHAR(13) + CHAR(10) + ' '
3847            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
3848            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
3849          END
3850          IF @Error <> 0
3851          BEGIN
3852            SET @ReturnCode = @Error
3853            GOTO NoAction
3854          END
3855        END
3856
3857        -- Select fragmentation group
3858        IF @CurrentIndexID IS NOT NULL AND @CurrentOnReadOnlyFileGroup = 0 AND EXISTS(SELECT * FROM @ActionsPreferred)
3859        BEGIN
3860          SET @CurrentFragmentationGroup = CASE
3861          WHEN @CurrentFragmentationLevel >= @FragmentationLevel2 THEN 'High'
3862          WHEN @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 THEN 'Medium'
3863          WHEN @CurrentFragmentationLevel < @FragmentationLevel1 THEN 'Low'
3864          END
3865        END
3866
3867        -- Which actions are allowed?
3868        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
3869        BEGIN
3870          IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) AND (@CurrentAllowPageLocks = 1 OR @CurrentIndexType = 5)
3871          BEGIN
3872            INSERT INTO @CurrentActionsAllowed ([Action])
3873            VALUES ('INDEX_REORGANIZE')
3874          END
3875          IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)
3876          BEGIN
3877            INSERT INTO @CurrentActionsAllowed ([Action])
3878            VALUES ('INDEX_REBUILD_OFFLINE')
3879          END
3880          IF @CurrentOnReadOnlyFileGroup = 0
3881          AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)
3882          AND (@CurrentIsPartition = 0 OR @Version >= 12)
3883          AND ((@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsNewLOB = 0)
3884          OR (@CurrentIndexType = 2 AND @CurrentIsNewLOB = 0)
3885          OR (@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsFileStream = 0 AND @Version >= 11)
3886          OR (@CurrentIndexType = 2 AND @Version >= 11))
3887          AND (@CurrentIsColumnStore = 0 OR @Version < 11)
3888          AND SERVERPROPERTY('EngineEdition') IN (3,5)
3889          BEGIN
3890            INSERT INTO @CurrentActionsAllowed ([Action])
3891            VALUES ('INDEX_REBUILD_ONLINE')
3892          END
3893        END
3894
3895        -- Decide action
3896        IF @CurrentIndexID IS NOT NULL
3897        AND EXISTS(SELECT * FROM @ActionsPreferred)
3898        AND (@CurrentPageCount >= @PageCountLevel OR @PageCountLevel = 0)
3899        BEGIN
3900          IF EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3)
3901          BEGIN
3902            SELECT @CurrentAction = [Action]
3903            FROM @ActionsPreferred
3904            WHERE FragmentationGroup = @CurrentFragmentationGroup
3905            AND [Priority] = (SELECT MIN([Priority])
3906                              FROM @ActionsPreferred
3907                              WHERE FragmentationGroup = @CurrentFragmentationGroup
3908                              AND [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))
3909          END
3910          ELSE
3911          BEGIN
3912            SELECT @CurrentAction = [Action]
3913            FROM @ActionsPreferred
3914            WHERE [Priority] = (SELECT MIN([Priority])
3915                                FROM @ActionsPreferred
3916                                WHERE [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))
3917          END
3918        END
3919
3920        -- Workaround for limitation in SQL Server, http://support.microsoft.com/kb/2292737
3921        IF @CurrentIndexID IS NOT NULL
3922        BEGIN
3923          SET @CurrentMaxDOP = @MaxDOP
3924          IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentAllowPageLocks = 0
3925          BEGIN
3926            SET @CurrentMaxDOP = 1
3927          END
3928        END
3929
3930        -- Update statistics?
3931        IF @CurrentStatisticsID IS NOT NULL
3932        AND ((@UpdateStatistics = 'ALL' AND (@CurrentIndexType IN (1,2,3,4,7) OR @CurrentIndexID IS NULL)) OR (@UpdateStatistics = 'INDEX' AND @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN (1,2,3,4,7)) OR (@UpdateStatistics = 'COLUMNS' AND @CurrentIndexID IS NULL))
3933        AND (@CurrentStatisticsModified = 1 OR @OnlyModifiedStatistics = 'N' OR @CurrentIsMemoryOptimized = 1)
3934        AND ((@CurrentIsPartition = 0 AND (@CurrentAction NOT IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') OR @CurrentAction IS NULL)) OR (@CurrentIsPartition = 1 AND @CurrentPartitionNumber = @CurrentPartitionCount))
3935        BEGIN
3936          SET @CurrentUpdateStatistics = 'Y'
3937        END
3938        ELSE
3939        BEGIN
3940          SET @CurrentUpdateStatistics = 'N'
3941        END
3942
3943        -- Create comment
3944        IF @CurrentIndexID IS NOT NULL
3945        BEGIN
3946          SET @CurrentComment = 'ObjectType: ' + CASE WHEN @CurrentObjectType = 'U' THEN 'Table' WHEN @CurrentObjectType = 'V' THEN 'View' ELSE 'N/A' END + ', '
3947          SET @CurrentComment = @CurrentComment + 'IndexType: ' + CASE WHEN @CurrentIndexType = 1 THEN 'Clustered' WHEN @CurrentIndexType = 2 THEN 'NonClustered' WHEN @CurrentIndexType = 3 THEN 'XML' WHEN @CurrentIndexType = 4 THEN 'Spatial' WHEN @CurrentIndexType = 5 THEN 'Clustered Columnstore' WHEN @CurrentIndexType = 6 THEN 'NonClustered Columnstore' WHEN @CurrentIndexType = 7 THEN 'NonClustered Hash' ELSE 'N/A' END + ', '
3948          SET @CurrentComment = @CurrentComment + 'ImageText: ' + CASE WHEN @CurrentIsImageText = 1 THEN 'Yes' WHEN @CurrentIsImageText = 0 THEN 'No' ELSE 'N/A' END + ', '
3949          SET @CurrentComment = @CurrentComment + 'NewLOB: ' + CASE WHEN @CurrentIsNewLOB = 1 THEN 'Yes' WHEN @CurrentIsNewLOB = 0 THEN 'No' ELSE 'N/A' END + ', '
3950          SET @CurrentComment = @CurrentComment + 'FileStream: ' + CASE WHEN @CurrentIsFileStream = 1 THEN 'Yes' WHEN @CurrentIsFileStream = 0 THEN 'No' ELSE 'N/A' END + ', '
3951          IF @Version >= 11 SET @CurrentComment = @CurrentComment + 'ColumnStore: ' + CASE WHEN @CurrentIsColumnStore = 1 THEN 'Yes' WHEN @CurrentIsColumnStore = 0 THEN 'No' ELSE 'N/A' END + ', '
3952          SET @CurrentComment = @CurrentComment + 'AllowPageLocks: ' + CASE WHEN @CurrentAllowPageLocks = 1 THEN 'Yes' WHEN @CurrentAllowPageLocks = 0 THEN 'No' ELSE 'N/A' END + ', '
3953          SET @CurrentComment = @CurrentComment + 'PageCount: ' + ISNULL(CAST(@CurrentPageCount AS nvarchar),'N/A') + ', '
3954          SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + ISNULL(CAST(@CurrentFragmentationLevel AS nvarchar),'N/A')
3955        END
3956
3957        IF @CurrentIndexID IS NOT NULL AND (@CurrentPageCount IS NOT NULL OR @CurrentFragmentationLevel IS NOT NULL)
3958        BEGIN
3959        SET @CurrentExtendedInfo = (SELECT *
3960                                    FROM (SELECT CAST(@CurrentPageCount AS nvarchar) AS [PageCount],
3961                                                 CAST(@CurrentFragmentationLevel AS nvarchar) AS Fragmentation
3962                                    ) ExtendedInfo FOR XML AUTO, ELEMENTS)
3963        END
3964
3965        IF @CurrentIndexID IS NOT NULL AND @CurrentAction IS NOT NULL AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
3966        BEGIN
3967          SET @CurrentCommandType13 = 'ALTER_INDEX'
3968
3969          SET @CurrentCommand13 = ''
3970          IF @LockTimeout IS NOT NULL SET @CurrentCommand13 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
3971          SET @CurrentCommand13 = @CurrentCommand13 + 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)
3972
3973          IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE')
3974          BEGIN
3975            SET @CurrentCommand13 = @CurrentCommand13 + ' REBUILD'
3976            IF @CurrentIsPartition = 1 SET @CurrentCommand13 = @CurrentCommand13 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)
3977            SET @CurrentCommand13 = @CurrentCommand13 + ' WITH ('
3978            IF @SortInTempdb = 'Y' AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + 'SORT_IN_TEMPDB = ON'
3979            IF @SortInTempdb = 'N' AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + 'SORT_IN_TEMPDB = OFF'
3980            IF @CurrentIndexType IN(1,2,3,4) AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + ', '
3981            IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + 'ONLINE = ON'
3982            IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @WaitAtLowPriorityMaxDuration IS NOT NULL SET @CurrentCommand13 = @CurrentCommand13 + ' (WAIT_AT_LOW_PRIORITY (MAX_DURATION = ' + CAST(@WaitAtLowPriorityMaxDuration AS nvarchar) + ', ABORT_AFTER_WAIT = ' + UPPER(@WaitAtLowPriorityAbortAfterWait) + '))'
3983            IF @CurrentAction = 'INDEX_REBUILD_OFFLINE' AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + 'ONLINE = OFF'
3984            IF @CurrentMaxDOP IS NOT NULL SET @CurrentCommand13 = @CurrentCommand13 + ', MAXDOP = ' + CAST(@CurrentMaxDOP AS nvarchar)
3985            IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)
3986            IF @PadIndex = 'Y' AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', PAD_INDEX = ON'
3987            IF @PadIndex = 'N' AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', PAD_INDEX = OFF'
3988            SET @CurrentCommand13 = @CurrentCommand13 + ')'
3989          END
3990
3991          IF @CurrentAction IN('INDEX_REORGANIZE')
3992          BEGIN
3993            SET @CurrentCommand13 = @CurrentCommand13 + ' REORGANIZE'
3994            IF @CurrentIsPartition = 1 SET @CurrentCommand13 = @CurrentCommand13 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)
3995            SET @CurrentCommand13 = @CurrentCommand13 + ' WITH ('
3996            IF @LOBCompaction = 'Y' SET @CurrentCommand13 = @CurrentCommand13 + 'LOB_COMPACTION = ON'
3997            IF @LOBCompaction = 'N' SET @CurrentCommand13 = @CurrentCommand13 + 'LOB_COMPACTION = OFF'
3998            SET @CurrentCommand13 = @CurrentCommand13 + ')'
3999          END
4000
4001          EXECUTE @CurrentCommandOutput13 = [dbo].[CommandExecute] @Command = @CurrentCommand13, @CommandType = @CurrentCommandType13, @Mode = 2, @Comment = @CurrentComment, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @PartitionNumber = @CurrentPartitionNumber, @ExtendedInfo = @CurrentExtendedInfo, @LogToTable = @LogToTable, @Execute = @Execute
4002          SET @Error = @@ERROR
4003          IF @Error <> 0 SET @CurrentCommandOutput13 = @Error
4004          IF @CurrentCommandOutput13 <> 0 SET @ReturnCode = @CurrentCommandOutput13
4005
4006          IF @Delay > 0
4007          BEGIN
4008            SET @CurrentDelay = DATEADD(ss,@Delay,'1900-01-01')
4009            WAITFOR DELAY @CurrentDelay
4010          END
4011        END
4012
4013        IF @CurrentStatisticsID IS NOT NULL AND @CurrentUpdateStatistics = 'Y' AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
4014        BEGIN
4015          SET @CurrentCommandType14 = 'UPDATE_STATISTICS'
4016
4017          SET @CurrentCommand14 = ''
4018          IF @LockTimeout IS NOT NULL SET @CurrentCommand14 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
4019          SET @CurrentCommand14 = @CurrentCommand14 + 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentStatisticsName)
4020          IF @StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y' OR @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ' WITH'
4021          IF @StatisticsSample = 100 SET @CurrentCommand14 = @CurrentCommand14 + ' FULLSCAN'
4022          IF @StatisticsSample IS NOT NULL AND @StatisticsSample <> 100 AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) SET @CurrentCommand14 = @CurrentCommand14 + ' SAMPLE ' + CAST(@StatisticsSample AS nvarchar) + ' PERCENT'
4023          IF @StatisticsResample = 'Y' OR (@CurrentIsMemoryOptimized = 1 AND (@StatisticsSample <> 100 OR @StatisticsSample IS NULL)) SET @CurrentCommand14 = @CurrentCommand14 + ' RESAMPLE'
4024          IF (@StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y' OR @CurrentIsMemoryOptimized = 1) AND @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ','
4025          IF @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ' NORECOMPUTE'
4026
4027          EXECUTE @CurrentCommandOutput14 = [dbo].[CommandExecute] @Command = @CurrentCommand14, @CommandType = @CurrentCommandType14, @Mode = 2, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @StatisticsName = @CurrentStatisticsName, @LogToTable = @LogToTable, @Execute = @Execute
4028          SET @Error = @@ERROR
4029          IF @Error <> 0 SET @CurrentCommandOutput14 = @Error
4030          IF @CurrentCommandOutput14 <> 0 SET @ReturnCode = @CurrentCommandOutput14
4031        END
4032
4033        NoAction:
4034
4035        -- Update that the index is completed
4036        UPDATE @tmpIndexesStatistics
4037        SET Completed = 1
4038        WHERE Selected = 1
4039        AND Completed = 0
4040        AND ID = @CurrentIxID
4041
4042        -- Clear variables
4043        SET @CurrentCommand02 = NULL
4044        SET @CurrentCommand03 = NULL
4045        SET @CurrentCommand04 = NULL
4046        SET @CurrentCommand05 = NULL
4047        SET @CurrentCommand06 = NULL
4048        SET @CurrentCommand07 = NULL
4049        SET @CurrentCommand08 = NULL
4050        SET @CurrentCommand09 = NULL
4051        SET @CurrentCommand10 = NULL
4052        SET @CurrentCommand11 = NULL
4053        SET @CurrentCommand12 = NULL
4054        SET @CurrentCommand13 = NULL
4055        SET @CurrentCommand14 = NULL
4056
4057        SET @CurrentCommandOutput13 = NULL
4058        SET @CurrentCommandOutput14 = NULL
4059
4060        SET @CurrentCommandType13 = NULL
4061        SET @CurrentCommandType14 = NULL
4062
4063        SET @CurrentIxID = NULL
4064        SET @CurrentSchemaID = NULL
4065        SET @CurrentSchemaName = NULL
4066        SET @CurrentObjectID = NULL
4067        SET @CurrentObjectName = NULL
4068        SET @CurrentObjectType = NULL
4069        SET @CurrentIsMemoryOptimized = NULL
4070        SET @CurrentIndexID = NULL
4071        SET @CurrentIndexName = NULL
4072        SET @CurrentIndexType = NULL
4073        SET @CurrentStatisticsID = NULL
4074        SET @CurrentStatisticsName = NULL
4075        SET @CurrentPartitionID = NULL
4076        SET @CurrentPartitionNumber = NULL
4077        SET @CurrentPartitionCount = NULL
4078        SET @CurrentIsPartition = NULL
4079        SET @CurrentIndexExists = NULL
4080        SET @CurrentStatisticsExists = NULL
4081        SET @CurrentIsImageText = NULL
4082        SET @CurrentIsNewLOB = NULL
4083        SET @CurrentIsFileStream = NULL
4084        SET @CurrentIsColumnStore = NULL
4085        SET @CurrentAllowPageLocks = NULL
4086        SET @CurrentNoRecompute = NULL
4087        SET @CurrentStatisticsModified = NULL
4088        SET @CurrentOnReadOnlyFileGroup = NULL
4089        SET @CurrentFragmentationLevel = NULL
4090        SET @CurrentPageCount = NULL
4091        SET @CurrentFragmentationGroup = NULL
4092        SET @CurrentAction = NULL
4093        SET @CurrentMaxDOP = NULL
4094        SET @CurrentUpdateStatistics = NULL
4095        SET @CurrentComment = NULL
4096        SET @CurrentExtendedInfo = NULL
4097
4098        DELETE FROM @CurrentActionsAllowed
4099
4100      END
4101
4102    END
4103
4104    -- Update that the database is completed
4105    UPDATE @tmpDatabases
4106    SET Completed = 1
4107    WHERE Selected = 1
4108    AND Completed = 0
4109    AND ID = @CurrentDBID
4110
4111    -- Clear variables
4112    SET @CurrentDBID = NULL
4113    SET @CurrentDatabaseID = NULL
4114    SET @CurrentDatabaseName = NULL
4115    SET @CurrentIsDatabaseAccessible = NULL
4116    SET @CurrentAvailabilityGroup = NULL
4117    SET @CurrentAvailabilityGroupRole = NULL
4118    SET @CurrentDatabaseMirroringRole = NULL
4119
4120    SET @CurrentCommand01 = NULL
4121
4122    DELETE FROM @tmpIndexesStatistics
4123
4124  END
4125
4126  ----------------------------------------------------------------------------------------------------
4127  --// Log completing information                                                                 //--
4128  ----------------------------------------------------------------------------------------------------
4129
4130  Logging:
4131  SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)
4132  SET @EndMessage = REPLACE(@EndMessage,'%','%%')
4133  RAISERROR(@EndMessage,10,1) WITH NOWAIT
4134
4135  IF @ReturnCode <> 0
4136  BEGIN
4137    RETURN @ReturnCode
4138  END
4139
4140  ----------------------------------------------------------------------------------------------------
4141
4142END
4143GO
4144
4145IF (SELECT CAST([Value] AS int) FROM #Config WHERE Name = 'Error') = 0
4146AND (SELECT [Value] FROM #Config WHERE Name = 'CreateJobs') = 'Y'
4147AND SERVERPROPERTY('EngineEdition') <> 4
4148BEGIN
4149
4150  DECLARE @BackupDirectory nvarchar(max)
4151  DECLARE @CleanupTime int
4152  DECLARE @OutputFileDirectory nvarchar(max)
4153  DECLARE @LogToTable nvarchar(max)
4154  DECLARE @DatabaseName nvarchar(max)
4155
4156  DECLARE @Version numeric(18,10)
4157
4158  DECLARE @TokenServer nvarchar(max)
4159  DECLARE @TokenJobID nvarchar(max)
4160  DECLARE @TokenStepID nvarchar(max)
4161  DECLARE @TokenDate nvarchar(max)
4162  DECLARE @TokenTime nvarchar(max)
4163  DECLARE @TokenLogDirectory nvarchar(max)
4164
4165  DECLARE @JobDescription nvarchar(max)
4166  DECLARE @JobCategory nvarchar(max)
4167  DECLARE @JobOwner nvarchar(max)
4168
4169  DECLARE @JobName01 nvarchar(max)
4170  DECLARE @JobName02 nvarchar(max)
4171  DECLARE @JobName03 nvarchar(max)
4172  DECLARE @JobName04 nvarchar(max)
4173  DECLARE @JobName05 nvarchar(max)
4174  DECLARE @JobName06 nvarchar(max)
4175  DECLARE @JobName07 nvarchar(max)
4176  DECLARE @JobName08 nvarchar(max)
4177  DECLARE @JobName09 nvarchar(max)
4178  DECLARE @JobName10 nvarchar(max)
4179  DECLARE @JobName11 nvarchar(max)
4180
4181  DECLARE @JobCommand01 nvarchar(max)
4182  DECLARE @JobCommand02 nvarchar(max)
4183  DECLARE @JobCommand03 nvarchar(max)
4184  DECLARE @JobCommand04 nvarchar(max)
4185  DECLARE @JobCommand05 nvarchar(max)
4186  DECLARE @JobCommand06 nvarchar(max)
4187  DECLARE @JobCommand07 nvarchar(max)
4188  DECLARE @JobCommand08 nvarchar(max)
4189  DECLARE @JobCommand09 nvarchar(max)
4190  DECLARE @JobCommand10 nvarchar(max)
4191  DECLARE @JobCommand11 nvarchar(max)
4192
4193  DECLARE @OutputFile01 nvarchar(max)
4194  DECLARE @OutputFile02 nvarchar(max)
4195  DECLARE @OutputFile03 nvarchar(max)
4196  DECLARE @OutputFile04 nvarchar(max)
4197  DECLARE @OutputFile05 nvarchar(max)
4198  DECLARE @OutputFile06 nvarchar(max)
4199  DECLARE @OutputFile07 nvarchar(max)
4200  DECLARE @OutputFile08 nvarchar(max)
4201  DECLARE @OutputFile09 nvarchar(max)
4202  DECLARE @OutputFile10 nvarchar(max)
4203  DECLARE @OutputFile11 nvarchar(max)
4204
4205  SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
4206
4207  IF @Version >= 9.002047
4208  BEGIN
4209    SET @TokenServer = '$' + '(ESCAPE_SQUOTE(SRVR))'
4210    SET @TokenJobID = '$' + '(ESCAPE_SQUOTE(JOBID))'
4211    SET @TokenStepID = '$' + '(ESCAPE_SQUOTE(STEPID))'
4212    SET @TokenDate = '$' + '(ESCAPE_SQUOTE(STRTDT))'
4213    SET @TokenTime = '$' + '(ESCAPE_SQUOTE(STRTTM))'
4214  END
4215  ELSE
4216  BEGIN
4217    SET @TokenServer = '$' + '(SRVR)'
4218    SET @TokenJobID = '$' + '(JOBID)'
4219    SET @TokenStepID = '$' + '(STEPID)'
4220    SET @TokenDate = '$' + '(STRTDT)'
4221    SET @TokenTime = '$' + '(STRTTM)'
4222  END
4223
4224  IF @Version >= 12
4225  BEGIN
4226    SET @TokenLogDirectory = '$' + '(ESCAPE_SQUOTE(SQLLOGDIR))'
4227  END
4228
4229  SELECT @BackupDirectory = Value
4230  FROM #Config
4231  WHERE [Name] = 'BackupDirectory'
4232
4233  SELECT @CleanupTime = Value
4234  FROM #Config
4235  WHERE [Name] = 'CleanupTime'
4236
4237  SELECT @OutputFileDirectory = Value
4238  FROM #Config
4239  WHERE [Name] = 'OutputFileDirectory'
4240
4241  SELECT @LogToTable = Value
4242  FROM #Config
4243  WHERE [Name] = 'LogToTable'
4244
4245  SELECT @DatabaseName = Value
4246  FROM #Config
4247  WHERE [Name] = 'DatabaseName'
4248
4249  SET @JobDescription = 'Source: https://ola.hallengren.com'
4250  SET @JobCategory = 'Database Maintenance'
4251  SET @JobOwner = SUSER_SNAME(0x01)
4252
4253  SET @JobName01 = 'DatabaseBackup - SYSTEM_DATABASES - FULL'
4254  SET @JobCommand01 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + @DatabaseName + ' -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''SYSTEM_DATABASES'', @Directory = ' + ISNULL('N''' + REPLACE(@BackupDirectory,'''','''''') + '''','NULL') + ', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL') + ', @CheckSum = ''Y''' + CASE WHEN @LogToTable = 'Y' THEN ', @LogToTable = ''Y''' ELSE '' END + '" -b'
4255  SET @OutputFile01 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'DatabaseBackup_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4256  IF LEN(@OutputFile01) > 200 SET @OutputFile01 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4257  IF LEN(@OutputFile01) > 200 SET @OutputFile01 = NULL
4258
4259  SET @JobName02 = 'DatabaseBackup - USER_DATABASES - DIFF'
4260  SET @JobCommand02 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + @DatabaseName + ' -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = ' + ISNULL('N''' + REPLACE(@BackupDirectory,'''','''''') + '''','NULL') + ', @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL') + ', @CheckSum = ''Y''' + CASE WHEN @LogToTable = 'Y' THEN ', @LogToTable = ''Y''' ELSE '' END + '" -b'
4261  SET @OutputFile02 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'DatabaseBackup_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4262  IF LEN(@OutputFile02) > 200 SET @OutputFile02 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4263  IF LEN(@OutputFile02) > 200 SET @OutputFile02 = NULL
4264
4265  SET @JobName03 = 'DatabaseBackup - USER_DATABASES - FULL'
4266  SET @JobCommand03 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + @DatabaseName + ' -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = ' + ISNULL('N''' + REPLACE(@BackupDirectory,'''','''''') + '''','NULL') + ', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL') + ', @CheckSum = ''Y''' + CASE WHEN @LogToTable = 'Y' THEN ', @LogToTable = ''Y''' ELSE '' END + '" -b'
4267  SET @OutputFile03 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'DatabaseBackup_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4268  IF LEN(@OutputFile03) > 200 SET @OutputFile03 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4269  IF LEN(@OutputFile03) > 200 SET @OutputFile03 = NULL
4270
4271  SET @JobName04 = 'DatabaseBackup - USER_DATABASES - LOG'
4272  SET @JobCommand04 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + @DatabaseName + ' -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = ' + ISNULL('N''' + REPLACE(@BackupDirectory,'''','''''') + '''','NULL') + ', @BackupType = ''LOG'', @Verify = ''Y'', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL') + ', @CheckSum = ''Y''' + CASE WHEN @LogToTable = 'Y' THEN ', @LogToTable = ''Y''' ELSE '' END + '" -b'
4273  SET @OutputFile04 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'DatabaseBackup_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4274  IF LEN(@OutputFile04) > 200 SET @OutputFile04 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4275  IF LEN(@OutputFile04) > 200 SET @OutputFile04 = NULL
4276
4277  SET @JobName05 = 'DatabaseIntegrityCheck - SYSTEM_DATABASES'
4278  SET @JobCommand05 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + @DatabaseName + ' -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ''SYSTEM_DATABASES''' + CASE WHEN @LogToTable = 'Y' THEN ', @LogToTable = ''Y''' ELSE '' END + '" -b'
4279  SET @OutputFile05 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'DatabaseIntegrityCheck_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4280  IF LEN(@OutputFile05) > 200 SET @OutputFile05 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4281  IF LEN(@OutputFile05) > 200 SET @OutputFile05 = NULL
4282
4283  SET @JobName06 = 'DatabaseIntegrityCheck - USER_DATABASES'
4284  SET @JobCommand06 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + @DatabaseName + ' -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ''USER_DATABASES''' + CASE WHEN @LogToTable = 'Y' THEN ', @LogToTable = ''Y''' ELSE '' END + '" -b'
4285  SET @OutputFile06 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'DatabaseIntegrityCheck_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4286  IF LEN(@OutputFile06) > 200 SET @OutputFile06 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4287  IF LEN(@OutputFile06) > 200 SET @OutputFile06 = NULL
4288
4289  SET @JobName07 = 'IndexOptimize - USER_DATABASES'
4290  SET @JobCommand07 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + @DatabaseName + ' -Q "EXECUTE [dbo].[IndexOptimize] @Databases = ''USER_DATABASES''' + CASE WHEN @LogToTable = 'Y' THEN ', @LogToTable = ''Y''' ELSE '' END + '" -b'
4291  SET @OutputFile07 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'IndexOptimize_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4292  IF LEN(@OutputFile07) > 200 SET @OutputFile07 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4293  IF LEN(@OutputFile07) > 200 SET @OutputFile07 = NULL
4294
4295  SET @JobName08 = 'sp_delete_backuphistory'
4296  SET @JobCommand08 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + 'msdb' + ' -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate" -b'
4297  SET @OutputFile08 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'sp_delete_backuphistory_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4298  IF LEN(@OutputFile08) > 200 SET @OutputFile08 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4299  IF LEN(@OutputFile08) > 200 SET @OutputFile08 = NULL
4300
4301  SET @JobName09 = 'sp_purge_jobhistory'
4302  SET @JobCommand09 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + 'msdb' + ' -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate" -b'
4303  SET @OutputFile09 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'sp_purge_jobhistory_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4304  IF LEN(@OutputFile09) > 200 SET @OutputFile09 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4305  IF LEN(@OutputFile09) > 200 SET @OutputFile09 = NULL
4306
4307  SET @JobName10 = 'Output File Cleanup'
4308  SET @JobCommand10 = 'cmd /q /c "For /F "tokens=1 delims=" %v In (''ForFiles /P "' + COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '" /m *_*_*_*.txt /d -30 2^>^&1'') do if EXIST "' + COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '"\%v echo del "' + COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '"\%v& del "' + COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '"\%v"'
4309  SET @OutputFile10 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'OutputFileCleanup_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4310  IF LEN(@OutputFile10) > 200 SET @OutputFile10 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4311  IF LEN(@OutputFile10) > 200 SET @OutputFile10 = NULL
4312
4313  SET @JobName11 = 'CommandLog Cleanup'
4314  SET @JobCommand11 = 'sqlcmd -E -S ' + @TokenServer + ' -d ' + @DatabaseName + ' -Q "DELETE FROM [dbo].[CommandLog] WHERE StartTime < DATEADD(dd,-30,GETDATE())" -b'
4315  SET @OutputFile11 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + 'CommandLogCleanup_' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4316  IF LEN(@OutputFile11) > 200 SET @OutputFile11 = COALESCE(@OutputFileDirectory,@TokenLogDirectory) + '\' + @TokenJobID + '_' + @TokenStepID + '_' + @TokenDate + '_' + @TokenTime + '.txt'
4317  IF LEN(@OutputFile11) > 200 SET @OutputFile11 = NULL
4318
4319  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName01)
4320  BEGIN
4321    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName01, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4322    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName01, @step_name = @JobName01, @subsystem = 'CMDEXEC', @command = @JobCommand01, @output_file_name = @OutputFile01
4323    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName01
4324  END
4325
4326  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName02)
4327  BEGIN
4328    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName02, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4329    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName02, @step_name = @JobName02, @subsystem = 'CMDEXEC', @command = @JobCommand02, @output_file_name = @OutputFile02
4330    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName02
4331  END
4332
4333  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName03)
4334  BEGIN
4335    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName03, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4336    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName03, @step_name = @JobName03, @subsystem = 'CMDEXEC', @command = @JobCommand03, @output_file_name = @OutputFile03
4337    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName03
4338  END
4339
4340  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName04)
4341  BEGIN
4342    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName04, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4343    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName04, @step_name = @JobName04, @subsystem = 'CMDEXEC', @command = @JobCommand04, @output_file_name = @OutputFile04
4344    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName04
4345  END
4346
4347  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName05)
4348  BEGIN
4349    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName05, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4350    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName05, @step_name = @JobName05, @subsystem = 'CMDEXEC', @command = @JobCommand05, @output_file_name = @OutputFile05
4351    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName05
4352  END
4353
4354  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName06)
4355  BEGIN
4356    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName06, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4357    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName06, @step_name = @JobName06, @subsystem = 'CMDEXEC', @command = @JobCommand06, @output_file_name = @OutputFile06
4358    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName06
4359  END
4360
4361  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName07)
4362  BEGIN
4363    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName07, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4364    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName07, @step_name = @JobName07, @subsystem = 'CMDEXEC', @command = @JobCommand07, @output_file_name = @OutputFile07
4365    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName07
4366  END
4367
4368  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName08)
4369  BEGIN
4370    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName08, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4371    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName08, @step_name = @JobName08, @subsystem = 'CMDEXEC', @command = @JobCommand08, @output_file_name = @OutputFile08
4372    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName08
4373  END
4374
4375  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName09)
4376  BEGIN
4377    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName09, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4378    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName09, @step_name = @JobName09, @subsystem = 'CMDEXEC', @command = @JobCommand09, @output_file_name = @OutputFile09
4379    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName09
4380  END
4381
4382  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName10)
4383  BEGIN
4384    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName10, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4385    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName10, @step_name = @JobName10, @subsystem = 'CMDEXEC', @command = @JobCommand10, @output_file_name = @OutputFile10
4386    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName10
4387  END
4388
4389  IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName11)
4390  BEGIN
4391    EXECUTE msdb.dbo.sp_add_job @job_name = @JobName11, @description = @JobDescription, @category_name = @JobCategory, @owner_login_name = @JobOwner
4392    EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName11, @step_name = @JobName11, @subsystem = 'CMDEXEC', @command = @JobCommand11, @output_file_name = @OutputFile11
4393    EXECUTE msdb.dbo.sp_add_jobserver @job_name = @JobName11
4394  END
4395
4396END
4397GO