December 5, 2013 at 12:33 pm
I will preface this by stating we are using MS SQL Server 2008 R2.
We're having issues when our database backups are running SQL Server takes all of the available memory and never releases. Our current high watermark of memory usage is about 60%. When the backup job runs it goes to 99% and never releases unless we reset the SQL service. This leads me to 2 questions:
1. Dealing with memory allocation, Is there a way to accurately limit memory usage of SQL Server? We are limiting the "Maximum server memory" value to 85% but in consistently exceeds that value.
2. What is the best method of backing up the database? We are currently relying on our provider to maintain the database backups and it seems like the "home grown" method they use through a stored proc and commands is the cause of the memory issues but it is working for other customers of theirs. Should we look at using Maintenance Plans as a replacement?
Any help with this would be great.
December 6, 2013 at 3:27 am
D.Schrenker (12/5/2013)
I will preface this by stating we are using MS SQL Server 2008 R2.We're having issues when our database backups are running SQL Server takes all of the available memory and never releases. Our current high watermark of memory usage is about 60%. When the backup job runs it goes to 99% and never releases unless we reset the SQL service. This leads me to 2 questions:
1. Dealing with memory allocation, Is there a way to accurately limit memory usage of SQL Server? We are limiting the "Maximum server memory" value to 85% but in consistently exceeds that value.
2. What is the best method of backing up the database? We are currently relying on our provider to maintain the database backups and it seems like the "home grown" method they use through a stored proc and commands is the cause of the memory issues but it is working for other customers of theirs. Should we look at using Maintenance Plans as a replacement?
Any help with this would be great.
A couple of questions:
1. How much memory is in the server? (85% doesn't tell us how much is physically installed)
2. Is there a reason that you believe Maintenance Plans are a better alternative? In essence a backup is being performed and that is consuming your availabe memory so whether a maintenance plan is used or a TSQL Script, the results will be comparable.
3. Can you post the script your provider uses? If it is running on your servers you should be allowed to see what is going on.
4. Have you tried running a very simple COPY_ONLY backup on the machine(s) experiencing the problem? What was the result? COPY_ONLY is important because you don't want to risk breaking the LSN chain.
From my perspective and as a first impression, this sounds a lot like a memory problem and maybe more memory is required.
December 6, 2013 at 11:56 am
Thanks for the response and sorry about the long answer.
Here are the answers:
1. The server has 16GB of memory
2. I'm not completely convinced that their home grown method is the most efficient and want to look into alternatives.
3. The SQL Job has 3 steps: FYI, there is a lot of bloat from logging.
Step#1:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @databases = 'ALL_DATABASES'" -b
Step#2:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'ALL_DATABASES', @Directory = N'O:\SQL Backups', @BackupType = 'FULL', @verify = 'Y', @CleanupTime =360, @checksum = 'Y',@Compress = 'Y'" -b
Step#3:
EXEC msdb.dbo.sp_start_job @job_name=N'Backup Monitoring', @step_name=N'Full Backup Monitor'
The SP's that are called in the steps are:
USE [master]
GO
CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]
@databases nvarchar(max),
@PhysicalOnly nvarchar(max) = 'N',
@NoIndex nvarchar(max) = 'N',
@ExtendedLogicalChecks nvarchar(max) = 'N',
@TabLock nvarchar(max) = 'N',
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'
AS
BEGIN
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
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 @CurrentID int
DECLARE @CurrentDatabaseID int
DECLARE @CurrentDatabaseName nvarchar(max)
DECLARE @CurrentIsDatabaseAccessible bit
DECLARE @CurrentMirroringRole nvarchar(max)
DECLARE @CurrentCommand01 nvarchar(max)
DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandType01 nvarchar(max)
DECLARE @tmpDatabases TABLE (ID int IDENTITY PRIMARY KEY,
DatabaseName nvarchar(max),
Completed bit)
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 + ', @PhysicalOnly = ' + ISNULL('''' + REPLACE(@PhysicalOnly,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @NoIndex = ' + ISNULL('''' + REPLACE(@NoIndex,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @ExtendedLogicalChecks = ' + ISNULL('''' + REPLACE(@ExtendedLogicalChecks,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @TabLock = ' + ISNULL('''' + REPLACE(@TabLock,'''','''''') + '''','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: provider' + 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.' + 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.' + 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.' + 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.' + 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 OR (@@ROWCOUNT = 0 AND @databases <> 'USER_DATABASES')
BEGIN
SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------
IF @PhysicalOnly NOT IN ('Y','N') OR @PhysicalOnly IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @PhysicalOnly is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @NoIndex NOT IN ('Y','N') OR @NoIndex IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @NoIndex is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @ExtendedLogicalChecks NOT IN ('Y','N') OR @ExtendedLogicalChecks IS NULL OR (@ExtendedLogicalChecks = 'Y' AND NOT @Version >= 10)
BEGIN
SET @ErrorMessage = 'The value for parameter @ExtendedLogicalChecks is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @PhysicalOnly = 'Y' AND @ExtendedLogicalChecks = 'Y'
BEGIN
SET @ErrorMessage = 'Extended Logical Checks and Physical Only cannot be used together.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF (@ExtendedLogicalChecks = 'Y' AND NOT (@Version >= 10))
BEGIN
SET @ErrorMessage = 'Extended Logical Checks are only supported in SQL Server 2008 and SQL Server 2008 R2.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @TabLock NOT IN ('Y','N') OR @TabLock IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @TabLock 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' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @ReturnCode = @Error
GOTO Logging
END
----------------------------------------------------------------------------------------------------
--// Execute 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
-- 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 = REPLACE(@DatabaseMessage,'%','%%')
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
AND NOT (DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
BEGIN
SET @CurrentCommandType01 = 'DBCC_CHECKDB'
SET @CurrentCommand01 = 'DBCC CHECKDB (' + QUOTENAME(@CurrentDatabaseName)
IF @NoIndex = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', NOINDEX'
SET @CurrentCommand01 = @CurrentCommand01 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @PhysicalOnly = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', DATA_PURITY'
IF @PhysicalOnly = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PHYSICAL_ONLY'
IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', EXTENDED_LOGICAL_CHECKS'
IF @TabLock = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', TABLOCK'
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
END
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID
-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabaseID = NULL
SET @CurrentDatabaseName = NULL
SET @CurrentIsDatabaseAccessible = NULL
SET @CurrentMirroringRole = NULL
SET @CurrentCommand01 = NULL
SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandType01 = NULL
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
Logging:
SET @EndMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120)
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAIT
IF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode
END
----------------------------------------------------------------------------------------------------
END
USE [master]
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
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
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: providet' + 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.' + 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.' + 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.' + 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. ' + 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 OR (@@ROWCOUNT = 0 AND @databases <> 'USER_DATABASES')
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 OR (@CleanupTime IS NOT NULL AND @CopyOnly = 'Y')
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.' + 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.' + 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.' + 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 @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 with provider.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @ReturnCode = @Error
GOTO Logging
END
--- Added by provider to set default backup compression
IF(((@Version >= 10 AND @Version < 10.5 AND SERVERPROPERTY('EngineEdition') = 3) OR (@Version >= 10.5 AND (SERVERPROPERTY('EngineEdition') = 3 OR SERVERPROPERTY('EditionID') = -1534726760)))AND NOT (@Compress='N'))
BEGIN
SET @Compress = 'Y'
END
------------------------------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)
SET @CurrentFileNumber = 0
WHILE @CurrentFileNumber < @NumberOfFiles
BEGIN
SET @CurrentFileNumber = @CurrentFileNumber + 1
SET @CurrentFilePath = @CurrentDirectory + '\' + REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') + '_' + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + '_' + 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) + '_' + 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
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 @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
SET @CurrentCommandType02 = 'xp_backup_database'
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'
BEGIN
SET @CurrentCommandType02 = 'xp_ss_backup'
SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_backup @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
SELECT @CurrentCommand02 = @CurrentCommand02 + ', ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) = 1 THEN '@filename' ELSE '@backupfile' END + ' = N''' + REPLACE(CurrentFilePath,'''','''''') + ''''
FROM @CurrentFiles
ORDER BY CurrentFilePath ASC
SET @CurrentCommand02 = @CurrentCommand02 + ', @backuptype = ' + CASE WHEN @CurrentBackupType = 'FULL' THEN '''Full''' WHEN @CurrentBackupType = 'DIFF' THEN '''Differential''' WHEN @CurrentBackupType = 'LOG' THEN '''Log''' END
SET @CurrentCommand02 = @CurrentCommand02 + ', @checksum = ' + CASE WHEN @checksum = 'Y' THEN '1' WHEN @checksum = 'N' THEN '0' END
SET @CurrentCommand02 = @CurrentCommand02 + ', @copyonly = ' + CASE WHEN @CopyOnly = 'Y' THEN '1' WHEN @CopyOnly = 'N' THEN '0' END
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 @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @desc = N''' + REPLACE(@Description,'''','''''') + ''''
SET @CurrentCommand02 = @CurrentCommand02 + ' IF @ReturnCode <> 0 RAISERROR(''Error performing SQLsafe backup.'', 16, 1)'
END
EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @Command = @CurrentCommand02, @CommandType = @CurrentCommandType02, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput02 = @Error
IF @CurrentCommandOutput02 <> 0 SET @ReturnCode = @CurrentCommandOutput02
END
-- Verify the backup
IF @CurrentCommandOutput02 = 0 AND @verify = 'Y'
BEGIN
IF @BackupSoftware IS NULL
BEGIN
SET @CurrentCommandType03 = 'RESTORE_VERIFYONLY'
SET @CurrentCommand03 = 'RESTORE VERIFYONLY FROM'
SELECT @CurrentCommand03 = @CurrentCommand03 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
ORDER BY CurrentFilePath ASC
END
IF @BackupSoftware = 'LITESPEED'
BEGIN
SET @CurrentCommandType03 = 'xp_restore_verifyonly'
SET @CurrentCommand03 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_restore_verifyonly'
SELECT @CurrentCommand03 = @CurrentCommand03 + ' @filename = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
ORDER BY CurrentFilePath ASC
SET @CurrentCommand03 = @CurrentCommand03 + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying LiteSpeed backup.'', 16, 1)'
END
IF @BackupSoftware = 'SQLBACKUP'
BEGIN
SET @CurrentCommandType03 = 'sqlbackup'
SET @CurrentCommand03 = 'RESTORE VERIFYONLY FROM'
SELECT @CurrentCommand03 = @CurrentCommand03 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
ORDER BY CurrentFilePath ASC
SET @CurrentCommand03 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqlbackup N''-SQL "' + REPLACE(@CurrentCommand03,'''','''''') + '"''' + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying SQLBackup backup.'', 16, 1)'
END
IF @BackupSoftware = 'HYPERBAC'
BEGIN
SET @CurrentCommandType03 = 'RESTORE_VERIFYONLY'
SET @CurrentCommand03 = 'RESTORE VERIFYONLY FROM'
SELECT @CurrentCommand03 = @CurrentCommand03 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
ORDER BY CurrentFilePath ASC
END
IF @BackupSoftware = 'SQLSAFE'
BEGIN
SET @CurrentCommandType03 = 'xp_ss_verify'
SET @CurrentCommand03 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_verify @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
SELECT @CurrentCommand03 = @CurrentCommand03 + ', ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) = 1 THEN '@filename' ELSE '@backupfile' END + ' = N''' + REPLACE(CurrentFilePath,'''','''''') + ''''
FROM @CurrentFiles
ORDER BY CurrentFilePath ASC
SET @CurrentCommand03 = @CurrentCommand03 + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying SQLsafe backup.'', 16, 1)'
END
EXECUTE @CurrentCommandOutput03 = [dbo].[CommandExecute] @Command = @CurrentCommand03, @CommandType = @CurrentCommandType03, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput03 = @Error
IF @CurrentCommandOutput03 <> 0 SET @ReturnCode = @CurrentCommandOutput03
END
-- Delete old backup files
IF (@CurrentCommandOutput02 = 0 AND @verify = 'N' AND @CurrentCleanupDate IS NOT NULL)
OR (@CurrentCommandOutput02 = 0 AND @verify = 'Y' AND @CurrentCommandOutput03 = 0 AND @CurrentCleanupDate IS NOT NULL)
BEGIN
IF @BackupSoftware IS NULL
BEGIN
SET @CurrentCommandType04 = 'xp_delete_file'
SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N''' + REPLACE(@CurrentDirectory,'''','''''') + ''', ''' + @CurrentFileExtension + ''', ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' IF @ReturnCode <> 0 RAISERROR(''Error deleting files.'', 16, 1)'
END
IF @BackupSoftware = 'LITESPEED'
BEGIN
SET @CurrentCommandType04 = 'xp_slssqlmaint'
SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_slssqlmaint N''-MAINTDEL -DELFOLDER "' + REPLACE(@CurrentDirectory,'''','''''') + '" -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)'
END
IF @BackupSoftware = 'SQLBACKUP'
BEGIN
SET @CurrentCommandType04 = 'sqbutility'
SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqbutility 1032, N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''', N''' + REPLACE(@CurrentDirectory,'''','''''') + ''', ''' + 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'' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLBackup backup files.'', 16, 1)'
END
IF @BackupSoftware = 'HYPERBAC'
BEGIN
SET @CurrentCommandType04 = 'xp_delete_file'
SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N''' + REPLACE(@CurrentDirectory,'''','''''') + ''', ''' + @CurrentFileExtension + ''', ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' IF @ReturnCode <> 0 RAISERROR(''Error deleting files.'', 16, 1)'
END
IF @BackupSoftware = 'SQLSAFE'
BEGIN
SET @CurrentCommandType04 = 'xp_ss_delete'
SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_delete @filename = N''' + REPLACE(@CurrentDirectory,'''','''''') + '\*.' + @CurrentFileExtension + ''', @age = ''' + CAST(DATEDIFF(mi,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + 'Minutes'' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLsafe backup files.'', 16, 1)'
END
EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @Command = @CurrentCommand04, @CommandType = @CurrentCommandType04, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput04 = @Error
IF @CurrentCommandOutput04 <> 0 SET @ReturnCode = @CurrentCommandOutput04
END
END
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID
-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabaseID = NULL
SET @CurrentDatabaseName = NULL
SET @CurrentBackupType = NULL
SET @CurrentFileExtension = NULL
SET @CurrentFileNumber = NULL
SET @CurrentDifferentialLSN = NULL
SET @CurrentLogLSN = NULL
SET @CurrentLatestBackup = NULL
SET @CurrentDatabaseNameFS = NULL
SET @CurrentDirectory = NULL
SET @CurrentFilePath = NULL
SET @CurrentDate = NULL
SET @CurrentCleanupDate = NULL
SET @CurrentIsDatabaseAccessible = NULL
SET @CurrentMirroringRole = NULL
SET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL
SET @CurrentCommand03 = NULL
SET @CurrentCommand04 = NULL
SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput02 = NULL
SET @CurrentCommandOutput03 = NULL
SET @CurrentCommandOutput04 = NULL
SET @CurrentCommandType01 = NULL
SET @CurrentCommandType02 = NULL
SET @CurrentCommandType03 = NULL
SET @CurrentCommandType04 = NULL
DELETE FROM @CurrentFiles
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
Logging:
SET @EndMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120)
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAIT
IF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode
END
----------------------------------------------------------------------------------------------------
END
4. I have and I don't have the same memory issues.
And to add a little more information: There are 8 databases on the server the largest is 3.5GB and the others average around 50MB each.
December 6, 2013 at 1:02 pm
My first thoughts are:
1. What is your max memory set at? I'm guessing it is the default, change it to at most 14336MB (14GB) You need to leave space for the OS and such.
2. It doesn't matter if it releases if you do #1
3. Roll your own backup is not advised when you don't NEED it. If you backup to disk either locally or remotely use a maintenance plan. If you have to use a home grown solution call it from a maintenance plan. In short, use a maintenance plan.
We used to backup to a server on the network, we moved to Azure Blob Store backups which are supported by SQL 2012 but not by the tools, so I had to write a sproc that handles that and an EXE to handle the cleanup..
CEWII
December 6, 2013 at 2:58 pm
D.Schrenker (12/5/2013)
I will preface this by stating we are using MS SQL Server 2008 R2.We're having issues when our database backups are running SQL Server takes all of the available memory and never releases. Our current high watermark of memory usage is about 60%. When the backup job runs it goes to 99% and never releases unless we reset the SQL service. This leads me to 2 questions:
1. Dealing with memory allocation, Is there a way to accurately limit memory usage of SQL Server? We are limiting the "Maximum server memory" value to 85% but in consistently exceeds that value.
Any help with this would be great.
There was a similar discussion topic recently - http://www.sqlservercentral.com/Forums/Topic1517522-2799-1.aspx#bm1517593.
As a general base configuration, for a dedicated SQL Server machine, reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. This means that, for a server with 64 GB RAM, the starting point for max server memory should be in the 54 GB range, and then tuned higher based on monitoring the Memory\Available Mbytes performance counter. -> https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ - Chapter 4
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply