/*

DBA: J McCoy

Description: Script to setup DBA Database Maintenance

Based on script from: http://ola.hallengren.com

Date: 11/2/2011

*/

USE [master]

GO

/****** Object: Table [dbo].[CommandLog] Script Date: 11/03/2011 14:40:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[CommandLog](

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

[DatabaseName] [sysname] NULL,

[SchemaName] [sysname] NULL,

[ObjectName] [sysname] NULL,

[ObjectType] [char](2) NULL,

[IndexName] [sysname] NULL,

[IndexType] [tinyint] NULL,

[StatisticsName] [sysname] NULL,

[PartitionNumber] [int] NULL,

[ExtendedInfo] [xml] NULL,

[Command] [nvarchar](max) NOT NULL,

[CommandType] [nvarchar](60) NOT NULL,

[StartTime] [datetime] NOT NULL,

[EndTime] [datetime] NULL,

[ErrorNumber] [int] NULL,

[ErrorMessage] [nvarchar](max) NULL,

CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [master]

GO

/****** Object: StoredProcedure [dbo].[CommandExecute] Script Date: 11/03/2011 14:41:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[CommandExecute]

@Command nvarchar(max),

@CommandType nvarchar(max),

@Mode int,

@Comment nvarchar(max) = NULL,

@DatabaseName nvarchar(max) = NULL,

@SchemaName nvarchar(max) = NULL,

@ObjectName nvarchar(max) = NULL,

@ObjectType nvarchar(max) = NULL,

@IndexName nvarchar(max) = NULL,

@IndexType int = NULL,

@StatisticsName nvarchar(max) = NULL,

@PartitionNumber int = NULL,

@ExtendedInfo xml = NULL,

@LogToTable nvarchar(max),

@Execute nvarchar(max)

AS

BEGIN

----------------------------------------------------------------------------------------------------

--// Source: http://ola.hallengren.com //--

----------------------------------------------------------------------------------------------------

SET NOCOUNT ON

SET LOCK_TIMEOUT 3600000

DECLARE @StartMessage nvarchar(max)

DECLARE @EndMessage nvarchar(max)

DECLARE @ErrorMessage nvarchar(max)

DECLARE @ErrorMessageOriginal nvarchar(max)

DECLARE @StartTime datetime

DECLARE @EndTime datetime

DECLARE @StartTimeSec datetime

DECLARE @EndTimeSec datetime

DECLARE @ID int

DECLARE @Error int

DECLARE @ReturnCode int

SET @Error = 0

SET @ReturnCode = 0

----------------------------------------------------------------------------------------------------

--// Check core requirements //--

----------------------------------------------------------------------------------------------------

IF SERVERPROPERTY('EngineEdition') = 5

BEGIN

SET @ErrorMessage = 'SQL Azure is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO ReturnCode

END

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')

BEGIN

SET @ErrorMessage = 'The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO ReturnCode

END

----------------------------------------------------------------------------------------------------

--// Check input parameters //--

----------------------------------------------------------------------------------------------------

IF @Command IS NULL OR @Command = ''

BEGIN

SET @ErrorMessage = 'The value for parameter @Command is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60

BEGIN

SET @ErrorMessage = 'The value for parameter @CommandType is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Mode NOT IN(1,2) OR @Mode IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @Mode is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Execute NOT IN('Y','N') OR @Execute IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO ReturnCode

END

----------------------------------------------------------------------------------------------------

--// Log initial information //--

----------------------------------------------------------------------------------------------------

SET @StartTime = GETDATE()

SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)

SET @StartMessage = 'DateTime: ' + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Command: ' + @Command

IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + 'Comment: ' + @Comment

SET @StartMessage = REPLACE(@StartMessage,'%','%%')

RAISERROR(@StartMessage,10,1) WITH NOWAIT

IF @LogToTable = 'Y'

BEGIN

INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)

VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)

END

SET @ID = SCOPE_IDENTITY()

----------------------------------------------------------------------------------------------------

--// Execute command //--

----------------------------------------------------------------------------------------------------

IF @Mode = 1 AND @Execute = 'Y'

BEGIN

EXECUTE(@Command)

SET @Error = @@ERROR

SET @ReturnCode = @Error

END

IF @Mode = 2 AND @Execute = 'Y'

BEGIN

BEGIN TRY

EXECUTE(@Command)

END TRY

BEGIN CATCH

SET @Error = ERROR_NUMBER()

SET @ReturnCode = @Error

SET @ErrorMessageOriginal = ERROR_MESSAGE()

SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

END CATCH

END

----------------------------------------------------------------------------------------------------

--// Log completing information //--

----------------------------------------------------------------------------------------------------

SET @EndTime = GETDATE()

SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120)

SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END + CHAR(13) + CHAR(10)

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)

SET @EndMessage = @EndMessage + 'DateTime: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10) + ' '

SET @EndMessage = REPLACE(@EndMessage,'%','%%')

RAISERROR(@EndMessage,10,1) WITH NOWAIT

IF @LogToTable = 'Y'

BEGIN

UPDATE dbo.CommandLog

SET EndTime = @EndTime,

ErrorNumber = CASE WHEN @Execute = 'N' THEN NULL ELSE @Error END,

ErrorMessage = @ErrorMessageOriginal

WHERE ID = @ID

END

ReturnCode:

IF @ReturnCode <> 0

BEGIN

RETURN @ReturnCode

END

----------------------------------------------------------------------------------------------------

END

GO

USE [master]

GO

/****** Object: StoredProcedure [dbo].[DatabaseBackup] Script Date: 11/03/2011 14:41:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseBackup]

@Databases nvarchar(max),

@Directory nvarchar(max) = NULL,

@BackupType nvarchar(max),

@Verify nvarchar(max) = 'N',

@CleanupTime int = NULL,

@Compress nvarchar(max) = NULL,

@CopyOnly nvarchar(max) = 'N',

@ChangeBackupType nvarchar(max) = 'N',

@BackupSoftware nvarchar(max) = NULL,

@CheckSum nvarchar(max) = 'N',

@BlockSize int = NULL,

@BufferCount int = NULL,

@MaxTransferSize int = NULL,

@NumberOfFiles int = 1,

@CompressionLevel int = NULL,

@Description nvarchar(max) = NULL,

@Threads int = NULL,

@Throttle int = NULL,

@LogToTable nvarchar(max) = 'N',

@Execute nvarchar(max) = 'Y'

AS

BEGIN

----------------------------------------------------------------------------------------------------

--// Source: http://ola.hallengren.com //--

----------------------------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @StartMessage nvarchar(max)

DECLARE @EndMessage nvarchar(max)

DECLARE @DatabaseMessage nvarchar(max)

DECLARE @ErrorMessage nvarchar(max)

DECLARE @Version numeric(18,10)

DECLARE @DefaultDirectory nvarchar(4000)

DECLARE @CheckDirectory nvarchar(4000)

DECLARE @CurrentID int

DECLARE @CurrentDatabaseID int

DECLARE @CurrentDatabaseName nvarchar(max)

DECLARE @CurrentBackupType nvarchar(max)

DECLARE @CurrentFileExtension nvarchar(max)

DECLARE @CurrentFileNumber int

DECLARE @CurrentDifferentialLSN numeric(25,0)

DECLARE @CurrentLogLSN numeric(25,0)

DECLARE @CurrentLatestBackup datetime

DECLARE @CurrentDatabaseNameFS nvarchar(max)

DECLARE @CurrentDirectory nvarchar(max)

DECLARE @CurrentFilePath nvarchar(max)

DECLARE @CurrentDate datetime

DECLARE @CurrentCleanupDate datetime

DECLARE @CurrentIsDatabaseAccessible bit

DECLARE @CurrentMirroringRole nvarchar(max)

DECLARE @CurrentCommand01 nvarchar(max)

DECLARE @CurrentCommand02 nvarchar(max)

DECLARE @CurrentCommand03 nvarchar(max)

DECLARE @CurrentCommand04 nvarchar(max)

DECLARE @CurrentCommandOutput01 int

DECLARE @CurrentCommandOutput02 int

DECLARE @CurrentCommandOutput03 int

DECLARE @CurrentCommandOutput04 int

DECLARE @CurrentCommandType01 nvarchar(max)

DECLARE @CurrentCommandType02 nvarchar(max)

DECLARE @CurrentCommandType03 nvarchar(max)

DECLARE @CurrentCommandType04 nvarchar(max)

DECLARE @DirectoryInfo TABLE (FileExists bit,

FileIsADirectory bit,

ParentDirectoryExists bit)

DECLARE @tmpDatabases TABLE (ID int IDENTITY PRIMARY KEY,

DatabaseName nvarchar(max),

Completed bit)

DECLARE @CurrentFiles TABLE (CurrentFilePath nvarchar(max))

DECLARE @Error int

DECLARE @ReturnCode int

SET @Error = 0

SET @ReturnCode = 0

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

----------------------------------------------------------------------------------------------------

--// Log initial information //--

----------------------------------------------------------------------------------------------------

SET @StartMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)

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)

SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Directory = ' + ISNULL('''' + REPLACE(@Directory,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @BackupType = ' + ISNULL('''' + REPLACE(@BackupType,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Verify = ' + ISNULL('''' + REPLACE(@Verify,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Compress = ' + ISNULL('''' + REPLACE(@Compress,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @CopyOnly = ' + ISNULL('''' + REPLACE(@CopyOnly,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @ChangeBackupType = ' + ISNULL('''' + REPLACE(@ChangeBackupType,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @BackupSoftware = ' + ISNULL('''' + REPLACE(@BackupSoftware,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @CheckSum = ' + ISNULL('''' + REPLACE(@CheckSum,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @BlockSize = ' + ISNULL(CAST(@BlockSize AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @BufferCount = ' + ISNULL(CAST(@BufferCount AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @MaxTransferSize = ' + ISNULL(CAST(@MaxTransferSize AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @NumberOfFiles = ' + ISNULL(CAST(@NumberOfFiles AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @CompressionLevel = ' + ISNULL(CAST(@CompressionLevel AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Description = ' + ISNULL('''' + REPLACE(@Description,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Threads = ' + ISNULL(CAST(@Threads AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Throttle = ' + ISNULL(CAST(@Throttle AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Source: http://ola.hallengren.com' + CHAR(13) + CHAR(10) + ' '

SET @StartMessage = REPLACE(@StartMessage,'%','%%')

RAISERROR(@StartMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------

--// Check core requirements //--

----------------------------------------------------------------------------------------------------

IF SERVERPROPERTY('EngineEdition') = 5

BEGIN

SET @ErrorMessage = 'SQL Azure is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO Logging

END

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')

BEGIN

SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

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%')

BEGIN

SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'TF' AND schemas.[name] = 'dbo' AND objects.[name] = 'DatabaseSelect')

BEGIN

SET @ErrorMessage = 'The function DatabaseSelect is missing. Download http://ola.hallengren.com/scripts/DatabaseSelect.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

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')

BEGIN

SET @ErrorMessage = 'The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO Logging

END

----------------------------------------------------------------------------------------------------

--// Select databases //--

----------------------------------------------------------------------------------------------------

IF @Databases IS NULL OR @Databases = ''

BEGIN

SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

INSERT INTO @tmpDatabases (DatabaseName, Completed)

SELECT DatabaseName AS DatabaseName,

0 AS Completed

FROM dbo.DatabaseSelect (@Databases)

ORDER BY DatabaseName ASC

IF @@ERROR <> 0

BEGIN

SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

SET @ErrorMessage = ''

SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

FROM @tmpDatabases

WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DatabaseName,'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') = ''

ORDER BY DatabaseName ASC

IF @@ROWCOUNT > 0

BEGIN

SET @ErrorMessage = 'The names of the following databases are not supported; ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

SET @ErrorMessage = '';

WITH tmpDatabasesCTE

AS

(

SELECT name AS DatabaseName,

UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','')) AS DatabaseNameFS

FROM sys.databases

)

SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

FROM tmpDatabasesCTE

WHERE DatabaseNameFS IN(SELECT DatabaseNameFS FROM tmpDatabasesCTE GROUP BY DatabaseNameFS HAVING COUNT(*) > 1)

AND DatabaseNameFS IN(SELECT UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DatabaseName COLLATE DATABASE_DEFAULT,'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','')) FROM @tmpDatabases)

AND DatabaseNameFS <> ''

ORDER BY DatabaseNameFS ASC, DatabaseName ASC

IF @@ROWCOUNT > 0

BEGIN

SET @ErrorMessage = 'The names of the following databases are not unique in the file system; ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

----------------------------------------------------------------------------------------------------

--// Get default backup directory //--

----------------------------------------------------------------------------------------------------

IF @Directory IS NULL

BEGIN

EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultDirectory OUTPUT

SET @Directory = @DefaultDirectory

END

----------------------------------------------------------------------------------------------------

--// Get default compression //--

----------------------------------------------------------------------------------------------------

IF @Compress IS NULL

BEGIN

SELECT @Compress = CASE

WHEN @BackupSoftware IS NULL AND EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup compression default' AND value_in_use = 1) THEN 'Y'

WHEN @BackupSoftware IS NULL AND NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup compression default' AND value_in_use = 1) THEN 'N'

WHEN @BackupSoftware IS NOT NULL AND (@CompressionLevel IS NULL OR @CompressionLevel > 0) THEN 'Y'

WHEN @BackupSoftware IS NOT NULL AND @CompressionLevel = 0 THEN 'N'

END

END

----------------------------------------------------------------------------------------------------

--// Check directory //--

----------------------------------------------------------------------------------------------------

IF NOT (@Directory LIKE '_:' OR @Directory LIKE '_:\%' OR @Directory LIKE '\\%\%') OR @Directory IS NULL OR LEFT(@Directory,1) = ' ' OR RIGHT(@Directory,1) = ' '

BEGIN

SET @ErrorMessage = 'The value for parameter @Directory is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

SET @CheckDirectory = @Directory

INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)

EXECUTE [master].dbo.xp_fileexist @CheckDirectory

IF NOT EXISTS (SELECT * FROM @DirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1)

BEGIN

SET @ErrorMessage = 'The directory does not exist.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

----------------------------------------------------------------------------------------------------

--// Check input parameters //--

----------------------------------------------------------------------------------------------------

IF @BackupType NOT IN ('FULL','DIFF','LOG') OR @BackupType IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @BackupType is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Verify NOT IN ('Y','N') OR @Verify IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @Verify is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CleanupTime < 0

BEGIN

SET @ErrorMessage = 'The value for parameter @CleanupTime is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

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') = -1534726760)))) 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)

BEGIN

SET @ErrorMessage = 'The value for parameter @Compress is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @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') = -1534726760)))

BEGIN

SET @ErrorMessage = 'Backup compression is only supported in SQL Server 2008 Enterprise and Developer Edition and in SQL Server 2008 R2 Standard, Enterprise, Developer and Datacenter Edition.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CopyOnly NOT IN ('Y','N') OR @CopyOnly IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @CopyOnly is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @ChangeBackupType NOT IN ('Y','N') OR @ChangeBackupType IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @ChangeBackupType is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BackupSoftware NOT IN ('LITESPEED','SQLBACKUP','HYPERBAC','SQLSAFE')

BEGIN

SET @ErrorMessage = 'The value for parameter @BackupSoftware is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BackupSoftware = 'LITESPEED' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_backup_database')

BEGIN

SET @ErrorMessage = 'LiteSpeed is not installed. Download http://www.quest.com/litespeed-for-sql-server/.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BackupSoftware = 'SQLBACKUP' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'sqlbackup')

BEGIN

SET @ErrorMessage = 'SQLBackup is not installed. Download http://www.red-gate.com/products/dba/sql-backup/.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BackupSoftware = 'SQLSAFE' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_ss_backup')

BEGIN

SET @ErrorMessage = 'SQLsafe is not installed. Download http://www.idera.com/Products/SQL-Server/SQL-safe-backup/.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CheckSum NOT IN ('Y','N') OR @CheckSum IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @CheckSum is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

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')

BEGIN

SET @ErrorMessage = 'The value for parameter @BlockSize is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BufferCount <= 0 OR @BufferCount > 2147483647 OR (@BufferCount IS NOT NULL AND @BackupSoftware = 'SQLBACKUP') OR (@BufferCount IS NOT NULL AND @BackupSoftware = 'SQLSAFE')

BEGIN

SET @ErrorMessage = 'The value for parameter @BufferCount is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @MaxTransferSize < 65536 OR @MaxTransferSize > 4194304 OR @MaxTransferSize % 65536 > 0 OR (@MaxTransferSize IS NOT NULL AND @BackupSoftware = 'SQLBACKUP') OR (@MaxTransferSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE')

BEGIN

SET @ErrorMessage = 'The value for parameter @MaxTransferSize is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @NumberOfFiles < 1 OR @NumberOfFiles > 64 OR (@NumberOfFiles > 32 AND @BackupSoftware = 'SQLBACKUP') OR @NumberOfFiles IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @NumberOfFiles is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF (@BackupSoftware IS NULL AND @CompressionLevel IS NOT NULL) OR (@BackupSoftware = 'HYPERBAC' AND @CompressionLevel IS NOT NULL) OR (@BackupSoftware = 'LITESPEED' AND (@CompressionLevel < 0 OR @CompressionLevel > 10)) OR (@BackupSoftware = 'SQLBACKUP' AND (@CompressionLevel < 0 OR @CompressionLevel > 4)) OR (@BackupSoftware = 'SQLSAFE' AND (@CompressionLevel < 1 OR @CompressionLevel > 4))

BEGIN

SET @ErrorMessage = 'The value for parameter @CompressionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF LEN(@Description) > 255 OR (@BackupSoftware = 'LITESPEED' AND LEN(@Description) > 128)

BEGIN

SET @ErrorMessage = 'The value for parameter @Description is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Threads IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED','SQLBACKUP','SQLSAFE') OR @BackupSoftware IS NULL) OR @Threads < 2 OR @Threads > 32

BEGIN

SET @ErrorMessage = 'The value for parameter @Threads is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Throttle IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED') OR @BackupSoftware IS NULL) OR @Throttle < 1 OR @Throttle > 100

BEGIN

SET @ErrorMessage = 'The value for parameter @Throttle is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Execute NOT IN('Y','N') OR @Execute IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ErrorMessage = 'The documentation is available on http://ola.hallengren.com/Documentation.html.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @ReturnCode = @Error

GOTO Logging

END

----------------------------------------------------------------------------------------------------

--// Execute backup commands //--

----------------------------------------------------------------------------------------------------

WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)

BEGIN

SELECT TOP 1 @CurrentID = ID,

@CurrentDatabaseName = DatabaseName

FROM @tmpDatabases

WHERE Completed = 0

ORDER BY ID ASC

SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)

IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)

BEGIN

SET @CurrentIsDatabaseAccessible = 1

END

ELSE

BEGIN

SET @CurrentIsDatabaseAccessible = 0

END

SELECT @CurrentMirroringRole = mirroring_role_desc

FROM sys.database_mirroring

WHERE database_id = @CurrentDatabaseID

SELECT @CurrentDifferentialLSN = differential_base_lsn

FROM sys.master_files

WHERE database_id = @CurrentDatabaseID

AND [type] = 0

AND [file_id] = 1

-- Workaround for a bug in SQL Server 2005

IF @Version >= 9 AND @Version < 10

AND (SELECT differential_base_lsn FROM sys.master_files WHERE database_id = @CurrentDatabaseID AND [type] = 0 AND [file_id] = 1) = (SELECT differential_base_lsn FROM sys.master_files WHERE database_id = DB_ID('model') AND [type] = 0 AND [file_id] = 1)

AND (SELECT differential_base_guid FROM sys.master_files WHERE database_id = @CurrentDatabaseID AND [type] = 0 AND [file_id] = 1) = (SELECT differential_base_guid FROM sys.master_files WHERE database_id = DB_ID('model') AND [type] = 0 AND [file_id] = 1)

AND (SELECT differential_base_time FROM sys.master_files WHERE database_id = @CurrentDatabaseID AND [type] = 0 AND [file_id] = 1) IS NULL

BEGIN

SET @CurrentDifferentialLSN = NULL

END

-- If a VSS snapshot has been taken since the last full backup, a differential backup cannot be performed

IF EXISTS (SELECT * FROM msdb.dbo.backupset WHERE database_name = @CurrentDatabaseName AND [type] = 'D' AND is_snapshot = 1 AND checkpoint_lsn = @CurrentDifferentialLSN)

BEGIN

SET @CurrentDifferentialLSN = NULL

END

SELECT @CurrentLogLSN = last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = @CurrentDatabaseID

SET @CurrentBackupType = @BackupType

IF @ChangeBackupType = 'Y'

BEGIN

IF @CurrentBackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') <> 'SIMPLE' AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'

BEGIN

SET @CurrentBackupType = 'DIFF'

END

IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL AND @CurrentDatabaseName <> 'master'

BEGIN

SET @CurrentBackupType = 'FULL'

END

END

IF @CurrentBackupType = 'LOG'

BEGIN

SELECT @CurrentLatestBackup = MAX(backup_finish_date)

FROM msdb.dbo.backupset

WHERE [type] IN('D','I')

AND is_copy_only = 0

AND is_snapshot = 0

AND is_damaged = 0

AND database_name = @CurrentDatabaseName

END

-- Set database message

SET @DatabaseMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Mirroring role: ' + ISNULL(@CurrentMirroringRole,'N/A') + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Differential base LSN: ' + ISNULL(CAST(@CurrentDifferentialLSN AS nvarchar),'N/A') + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Last log backup LSN: ' + ISNULL(CAST(@CurrentLogLSN AS nvarchar),'N/A') + CHAR(13) + CHAR(10) + ' '

SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%')

RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT

IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'

AND NOT (DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)

AND DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 0

AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') = 'SIMPLE' OR @CurrentLogLSN IS NULL))

AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL)

AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabaseName = 'master')

BEGIN

-- Set variables

SET @CurrentDate = GETDATE()

IF @CleanupTime IS NULL OR (@CurrentBackupType = 'LOG' AND @CurrentLatestBackup IS NULL)

BEGIN

SET @CurrentCleanupDate = NULL

END

ELSE

IF @CurrentBackupType = 'LOG'

BEGIN

SET @CurrentCleanupDate = (SELECT MIN([Date]) FROM(SELECT DATEADD(hh,-(@CleanupTime),@CurrentDate) AS [Date] UNION SELECT @CurrentLatestBackup AS [Date]) Dates)

END

ELSE

BEGIN

SET @CurrentCleanupDate = DATEADD(hh,-(@CleanupTime),@CurrentDate)

END

SET @CurrentDatabaseNameFS = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CurrentDatabaseName,'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','')

SELECT @CurrentFileExtension = CASE

WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'FULL' THEN 'bak'

WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'DIFF' THEN 'bak'

WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'LOG' THEN 'trn'

WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'FULL' THEN 'bak'

WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'DIFF' THEN 'bak'

WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'LOG' THEN 'trn'

WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'FULL' THEN 'sqb'

WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'DIFF' THEN 'sqb'

WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'LOG' THEN 'sqb'

WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'FULL' THEN 'hbc'

WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'DIFF' THEN 'hbc'

WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'LOG' THEN 'hbc'

WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'FULL' THEN 'safe'

WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'DIFF' THEN 'safe'

WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'LOG' THEN 'safe'

END

--SET @CurrentDirectory = @Directory + CASE WHEN RIGHT(@Directory,1) = '\' THEN '' ELSE '\' END + REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') + '\' + @CurrentDatabaseNameFS + '\' + UPPER(@CurrentBackupType) + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END

SET @CurrentDirectory = @Directory + CASE WHEN RIGHT(@Directory,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END

SET @CurrentFileNumber = 0

WHILE @CurrentFileNumber < @NumberOfFiles

BEGIN

SET @CurrentFileNumber = @CurrentFileNumber + 1

SET @CurrentFilePath = @CurrentDirectory + '\' + REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') + '_' + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + 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

IF LEN(@CurrentFilePath) > 259

BEGIN

SET @CurrentFilePath = @CurrentDirectory + '\' + REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') + '_' + 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 @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

END

INSERT INTO @CurrentFiles (CurrentFilePath)

SELECT @CurrentFilePath

END

-- Create directory

SET @CurrentCommandType01 = 'xp_create_subdir'

SET @CurrentCommand01 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N''' + REPLACE(@CurrentDirectory,'''','''''') + ''' IF @ReturnCode <> 0 RAISERROR(''Error creating directory.'', 16, 1)'

EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01, @CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute

SET @Error = @@ERROR

IF @Error <> 0 SET @CurrentCommandOutput01 = @Error

IF @CurrentCommandOutput01 <> 0 SET @ReturnCode = @CurrentCommandOutput01

-- Perform a backup

IF @CurrentCommandOutput01 = 0

BEGIN

IF @BackupSoftware IS NULL

BEGIN

SELECT @CurrentCommandType02 = CASE

WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP_DATABASE'

WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP_LOG'

END

SELECT @CurrentCommand02 = CASE

WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName) + ' TO'

WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName) + ' TO'

END

SELECT @CurrentCommand02 = @CurrentCommand02 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END

FROM @CurrentFiles

ORDER BY CurrentFilePath ASC

SET @CurrentCommand02 = @CurrentCommand02 + ' WITH '

IF @CheckSum = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + 'CHECKSUM'

IF @CheckSum = 'N' SET @CurrentCommand02 = @CurrentCommand02 + 'NO_CHECKSUM'

IF @Compress = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COMPRESSION'

IF @Compress = 'N' AND @Version >= 10 SET @CurrentCommand02 = @CurrentCommand02 + ', NO_COMPRESSION'

IF @CurrentBackupType = 'DIFF' SET @CurrentCommand02 = @CurrentCommand02 + ', DIFFERENTIAL'

IF @CopyOnly = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COPY_ONLY'

IF @BlockSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)

IF @BufferCount IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BUFFERCOUNT = ' + CAST(@BufferCount AS nvarchar)

IF @MaxTransferSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', MAXTRANSFERSIZE = ' + CAST(@MaxTransferSize AS nvarchar)

IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''

END

IF @BackupSoftware = 'LITESPEED'

BEGIN

SELECT @CurrentCommandType02 = CASE

WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'xp_backup_database'

WHEN @CurrentBackupType = 'LOG' THEN 'xp_backup_log'

END

SELECT @CurrentCommand02 = CASE

WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_backup_database @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''

WHEN @CurrentBackupType = 'LOG' THEN 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_backup_log @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''

END

SELECT @CurrentCommand02 = @CurrentCommand02 + ', @filename = N''' + REPLACE(CurrentFilePath,'''','''''') + ''''

FROM @CurrentFiles

ORDER BY CurrentFilePath ASC

SET @CurrentCommand02 = @CurrentCommand02 + ', @with = '''

IF @CheckSum = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + 'CHECKSUM'

IF @CheckSum = 'N' SET @CurrentCommand02 = @CurrentCommand02 + 'NO_CHECKSUM'

IF @CurrentBackupType = 'DIFF' SET @CurrentCommand02 = @CurrentCommand02 + ', DIFFERENTIAL'

IF @CopyOnly = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COPY_ONLY'

IF @BlockSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)

IF @BufferCount IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BUFFERCOUNT = ' + CAST(@BufferCount AS nvarchar)

IF @MaxTransferSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', MAXTRANSFERSIZE = ' + CAST(@MaxTransferSize AS nvarchar)

SET @CurrentCommand02 = @CurrentCommand02 + ''''

IF @CompressionLevel IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @compressionlevel = ' + CAST(@CompressionLevel AS nvarchar)

IF @Threads IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @threads = ' + CAST(@Threads AS nvarchar)

IF @Throttle IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @throttle = ' + CAST(@Throttle AS nvarchar)

IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @desc = N''' + REPLACE(@Description,'''','''''') + ''''

SET @CurrentCommand02 = @CurrentCommand02 + ' IF @ReturnCode <> 0 RAISERROR(''Error performing LiteSpeed backup.'', 16, 1)'

END

IF @BackupSoftware = 'SQLBACKUP'

BEGIN

SET @CurrentCommandType02 = 'sqlbackup'

SELECT @CurrentCommand02 = CASE

WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName) + ' TO'

WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName) + ' TO'

END

SELECT @CurrentCommand02 = @CurrentCommand02 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END

FROM @CurrentFiles

ORDER BY CurrentFilePath ASC

SET @CurrentCommand02 = @CurrentCommand02 + ' WITH '

IF @CheckSum = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + 'CHECKSUM'

IF @CheckSum = 'N' SET @CurrentCommand02 = @CurrentCommand02 + 'NO_CHECKSUM'

IF @CurrentBackupType = 'DIFF' SET @CurrentCommand02 = @CurrentCommand02 + ', DIFFERENTIAL'

IF @CopyOnly = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COPY_ONLY'

IF @CompressionLevel IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', COMPRESSION = ' + CAST(@CompressionLevel AS nvarchar)

IF @Threads IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', THREADCOUNT = ' + CAST(@Threads AS nvarchar)

IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''

SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqlbackup N''-SQL "' + REPLACE(@CurrentCommand02,'''','''''') + '"''' + ' IF @ReturnCode <> 0 RAISERROR(''Error performing SQLBackup backup.'', 16, 1)'

END

IF @BackupSoftware = 'HYPERBAC'

BEGIN

SET @CurrentCommandType02 = 'BACKUP_DATABASE'

SELECT @CurrentCommand02 = CASE

WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName) + ' TO'

WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName) + ' TO'

END

SELECT @CurrentCommand02 = @CurrentCommand02 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END

FROM @CurrentFiles

ORDER BY CurrentFilePath ASC

SET @CurrentCommand02 = @CurrentCommand02 + ' WITH '

IF @CheckSum = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + 'CHECKSUM'

IF @CheckSum = 'N' SET @CurrentCommand02 = @CurrentCommand02 + 'NO_CHECKSUM'

IF @CurrentBackupType = 'DIFF' SET @CurrentCommand02 = @CurrentCommand02 + ', DIFFERENTIAL'

IF @CopyOnly = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COPY_ONLY'

IF @BlockSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)

IF @BufferCount IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BUFFERCOUNT = ' + CAST(@BufferCount AS nvarchar)

IF @MaxTransferSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', MAXTRANSFERSIZE = ' + CAST(@MaxTransferSize AS nvarchar)

IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''

END

IF @BackupSoftware = 'SQLSAFE'