This script takes the last full backup includes diff and logs backups related until the next full backup in one file.
It requires that 7za.exe is on PATH, (ej. %windir%\system32).
To use it run it like:
EXECUTE [dbo].[DatabaseBackupCompression] @DatabaseName = 'DB_EXAMPLE' ,
@BackupDirectory = 'C:\DB_BACKUPS' ,
@FinalDirectory = 'C:\ARCHIVE_BACKUPS'
Replace @BackupDirectory with the path used in Ola Hallengren script, @DatabaseName with the database to archive and @FinalDirectory with the path where the archive will be stored.
It takes aditional arguments like:
@ServerName: If running from another server.
@DatabaseName: Database to compress
@BackupDirectory: Path of Backup Directory
@FinalDirectory: Path of Archived files
@FormatFolder: Folder structure created to store archives, vaild formats are: yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, yyyy\MM
@CompressionLevel: 0 No Compression (Store) - 9 Max compression, {5} - Default (Normal) Compression
@SplitFileMB: Split the file (volume) in every X MB.
I hope this is useful.
USE [master]
GO
CREATE PROCEDURE [dbo].[DatabaseBackupCompression]
@ServerName NVARCHAR(MAX) = NULL,
@DatabaseName NVARCHAR(MAX) = NULL,
@BackupDirectory NVARCHAR(MAX) = NULL,
@FinalDirectory NVARCHAR(MAX) = NULL,
@FormatFolder NVARCHAR(MAX) = 'yyyyMM', -- yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, yyyy\MM
@CompressionLevel INT = 5, -- 0 No Compression (Store) - 9 Max compression, {5} - Default (Normal) Compression
@SplitFileMB INT = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @CurrentRootDirectoryPath nvarchar(4000)
DECLARE @FullDirectoryPath nvarchar(512)
DECLARE @DirectoryCheck bit
DECLARE @Error int
SET @Error = 0
IF @ServerName IS NULL
SET @ServerName = @@SERVERNAME
DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @DatabaseMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,SYSDATETIME(),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)
RAISERROR(@StartMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Set Root Backup Directory //--
----------------------------------------------------------------------------------------------------
IF @BackupDirectory IS NULL
BEGIN
DECLARE @DefaultBakDirectory nvarchar(4000)
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBakDirectory OUTPUT
SET @BackupDirectory = @DefaultBakDirectory
END
----------------------------------------------------------------------------------------------------
--// Remove last slash from path //--
----------------------------------------------------------------------------------------------------
IF RIGHT(@BackupDirectory, 1) = '\'
SET @BackupDirectory = LEFT(@BackupDirectory, LEN(@BackupDirectory) - 1)
IF RIGHT(@FinalDirectory, 1) = '\'
SET @FinalDirectory = LEFT(@FinalDirectory, LEN(@FinalDirectory) - 1)
----------------------------------------------------------------------------------------------------
--// Set Full Path of Corresponding Backup //--
----------------------------------------------------------------------------------------------------
SET @FullDirectoryPath = @BackupDirectory + '\' + @ServerName + '\' + @DatabaseName
RAISERROR(@FullDirectoryPath,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Fill Table Variable with filenames of backups //--
----------------------------------------------------------------------------------------------------
DECLARE @DirectoryTree TABLE (
name nvarchar(512)
,depth int
,isfile bit
);
DELETE FROM @DirectoryTree;
INSERT @DirectoryTree(name,depth,isfile)
EXEC master.sys.xp_dirtree @FullDirectoryPath,0,1
----------------------------------------------------------------------------------------------------
--// Fill Table Variable with id and type of backups //--
----------------------------------------------------------------------------------------------------
DECLARE @Backups TABLE (
id int
,filepath nvarchar(512)
,typebak nvarchar(5)
,datebak datetime2(0)
);
DELETE FROM @Backups;
-- MAGIC!
INSERT INTO @Backups(id,filepath,typebak,datebak)
SELECT ROW_NUMBER() OVER(PARTITION BY replace(substring(name, len(name)-23, 4), '_', '')
ORDER BY convert(datetime2(0), substring(name, len(name)-18, 8) + ' ' +
substring(substring(name, len(name)-9, 6), 1, 2)+ ':' + substring(substring(name, len(name)-9, 6), 3, 2)+ ':' +
substring(substring(name, len(name)-9, 6), 5, 2), 112) ASC),
@FullDirectoryPath + '\'+replace(substring(name, len(name)-23, 4), '_', '')+'\' + name,
replace(substring(name, len(name)-23, 4), '_', ''),
convert(datetime2(0), substring(name, len(name)-18, 8) + ' ' +
substring(substring(name, len(name)-9, 6), 1, 2)+ ':' + substring(substring(name, len(name)-9, 6), 3, 2)+ ':' +
substring(substring(name, len(name)-9, 6), 5, 2), 112)
FROM @DirectoryTree
WHERE depth = 2 AND isfile = 1
----------------------------------------------------------------------------------------------------
--// Select range of last backup files //--
----------------------------------------------------------------------------------------------------
DECLARE
@id_work int,
@date_start datetime2(0),
@date_end datetime2(0),
@date_end_full datetime2(0),
@dateformat nvarchar(32),
@folderformat varchar(32)
SELECT @id_work = MIN(id) FROM @Backups WHERE typebak = 'FULL' HAVING COUNT(*) > 1
SELECT @date_start = datebak FROM @Backups WHERE typebak = 'FULL' AND id = @id_work
SELECT @date_end_full = datebak FROM @Backups WHERE typebak = 'FULL' AND id = @id_work + 1
SELECT @date_end = MAX(datebak) FROM @Backups WHERE typebak <> 'FULL' AND datebak BETWEEN @date_start AND @date_end_full
DELETE FROM @Backups WHERE typebak = 'FULL' AND id = @id_work + 1
IF @date_end IS NULL
SET @date_end = @date_start;
----------------------------------------------------------------------------------------------------
--// Select range of last backup files //--
----------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL
DROP TABLE ##FilesToCompress;
CREATE TABLE ##FilesToCompress (
id int identity(1,1),
filepath nvarchar(512)
);
INSERT INTO ##FilesToCompress(filepath)
SELECT filepath FROM @Backups WHERE datebak BETWEEN @date_start AND @date_end
ORDER BY datebak ASC
IF (@date_start <> @date_end)
SET @dateformat =
REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@date_start,120)),'-',''),' ','_'),':','')
+ '-' +
REPLACE(REPLACE(REPLACE((CONVERT(nvarchar, @date_end ,120)),'-',''),' ','_'),':','')
ELSE
SET @dateformat =
REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@date_start,120)),'-',''),' ','_'),':','')
-- Valid formats yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, , yyyy\MM
IF (@FormatFolder = 'yyyyMMdd')
SET @folderformat = CONVERT(VARCHAR(8), @date_start, 112)
ELSE IF (@FormatFolder = 'yyyyMM\dd')
SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112) + '\' + LEFT(CONVERT(VARCHAR(2), @date_start, 112), 2)
ELSE IF (@FormatFolder = 'yyyy\MM\dd')
SET @folderformat = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '\')
ELSE IF (@FormatFolder = 'yyyyMM')
SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112)
ELSE IF (@FormatFolder = 'yyyy\MM')
SET @folderformat = REPLACE(CONVERT(VARCHAR(7), GETDATE(), 111), '/', '\')
ELSE
SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112) -- Same as yyyyMM
DECLARE
@ExecString VARCHAR(8000),
@CompressFile NVARCHAR(200),
@FileList NVARCHAR(200)
SET @CompressFile = @DatabaseName + '_' + @dateformat +'.7z'
SET @FinalDirectory = @FinalDirectory + '\' + @ServerName + '\' + @DatabaseName + '\' + @folderformat
RAISERROR(@FinalDirectory,10,1) WITH NOWAIT
SET @ExecString = 'mkdir ' + @FinalDirectory
EXEC master..xp_cmdshell @ExecString, no_output;
SET @FileList = @FinalDirectory + '\' + @DatabaseName + '_' + @dateformat + '.txt'
SET @ExecString = 'bcp "select filepath from ##FilesToCompress" queryout ' + @FileList + ' -c -T -S' + @ServerName
EXEC master..xp_cmdshell @ExecString, no_output;
IF EXISTS(SELECT 1 FROM ##FilesToCompress)
BEGIN
IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL
DROP TABLE ##FilesToCompress;
DECLARE @SplitCommand VARCHAR(20) = ''
IF @SplitFileMB IS NOT NULL
SET @SplitCommand = '-v' + CAST(@SplitFileMB AS VARCHAR) + 'm '
SET @ExecString = '@7za.exe a -ssc -bd -mx'+ CAST(@CompressionLevel AS VARCHAR) +' -t7z -ms=on ' + @SplitCommand + '"' + @FinalDirectory + '\' + @CompressFile + '" @' + @FileList + ''
--PRINT @ExecString
EXEC master..xp_cmdshell @ExecString, no_output;
SET @ExecString = '@for /f "delims=" %i in (' + @FileList +') do del /f /q %i'
--PRINT @ExecString
EXEC master..xp_cmdshell @ExecString, no_output;
SET @ExecString = '@del ' + @FileList
--PRINT @ExecString
EXEC master..xp_cmdshell @ExecString, no_output;
END
----------------------------------------------------------------------------------------------------
--// Cleanup //--
----------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL
DROP TABLE ##FilesToCompress;
END
GO
It requires that 7za.exe is on PATH, (ej. %windir%\system32).
To use it run it like:
EXECUTE [dbo].[DatabaseBackupCompression] @DatabaseName = 'DB_EXAMPLE' ,
@BackupDirectory = 'C:\DB_BACKUPS' ,
@FinalDirectory = 'C:\ARCHIVE_BACKUPS'
Replace @BackupDirectory with the path used in Ola Hallengren script, @DatabaseName with the database to archive and @FinalDirectory with the path where the archive will be stored.
It takes aditional arguments like:
@ServerName: If running from another server.
@DatabaseName: Database to compress
@BackupDirectory: Path of Backup Directory
@FinalDirectory: Path of Archived files
@FormatFolder: Folder structure created to store archives, vaild formats are: yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, yyyy\MM
@CompressionLevel: 0 No Compression (Store) - 9 Max compression, {5} - Default (Normal) Compression
@SplitFileMB: Split the file (volume) in every X MB.
I hope this is useful.
USE [master]
GO
CREATE PROCEDURE [dbo].[DatabaseBackupCompression]
@ServerName NVARCHAR(MAX) = NULL,
@DatabaseName NVARCHAR(MAX) = NULL,
@BackupDirectory NVARCHAR(MAX) = NULL,
@FinalDirectory NVARCHAR(MAX) = NULL,
@FormatFolder NVARCHAR(MAX) = 'yyyyMM', -- yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, yyyy\MM
@CompressionLevel INT = 5, -- 0 No Compression (Store) - 9 Max compression, {5} - Default (Normal) Compression
@SplitFileMB INT = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @CurrentRootDirectoryPath nvarchar(4000)
DECLARE @FullDirectoryPath nvarchar(512)
DECLARE @DirectoryCheck bit
DECLARE @Error int
SET @Error = 0
IF @ServerName IS NULL
SET @ServerName = @@SERVERNAME
DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @DatabaseMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,SYSDATETIME(),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)
RAISERROR(@StartMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Set Root Backup Directory //--
----------------------------------------------------------------------------------------------------
IF @BackupDirectory IS NULL
BEGIN
DECLARE @DefaultBakDirectory nvarchar(4000)
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBakDirectory OUTPUT
SET @BackupDirectory = @DefaultBakDirectory
END
----------------------------------------------------------------------------------------------------
--// Remove last slash from path //--
----------------------------------------------------------------------------------------------------
IF RIGHT(@BackupDirectory, 1) = '\'
SET @BackupDirectory = LEFT(@BackupDirectory, LEN(@BackupDirectory) - 1)
IF RIGHT(@FinalDirectory, 1) = '\'
SET @FinalDirectory = LEFT(@FinalDirectory, LEN(@FinalDirectory) - 1)
----------------------------------------------------------------------------------------------------
--// Set Full Path of Corresponding Backup //--
----------------------------------------------------------------------------------------------------
SET @FullDirectoryPath = @BackupDirectory + '\' + @ServerName + '\' + @DatabaseName
RAISERROR(@FullDirectoryPath,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Fill Table Variable with filenames of backups //--
----------------------------------------------------------------------------------------------------
DECLARE @DirectoryTree TABLE (
name nvarchar(512)
,depth int
,isfile bit
);
DELETE FROM @DirectoryTree;
INSERT @DirectoryTree(name,depth,isfile)
EXEC master.sys.xp_dirtree @FullDirectoryPath,0,1
----------------------------------------------------------------------------------------------------
--// Fill Table Variable with id and type of backups //--
----------------------------------------------------------------------------------------------------
DECLARE @Backups TABLE (
id int
,filepath nvarchar(512)
,typebak nvarchar(5)
,datebak datetime2(0)
);
DELETE FROM @Backups;
-- MAGIC!
INSERT INTO @Backups(id,filepath,typebak,datebak)
SELECT ROW_NUMBER() OVER(PARTITION BY replace(substring(name, len(name)-23, 4), '_', '')
ORDER BY convert(datetime2(0), substring(name, len(name)-18, 8) + ' ' +
substring(substring(name, len(name)-9, 6), 1, 2)+ ':' + substring(substring(name, len(name)-9, 6), 3, 2)+ ':' +
substring(substring(name, len(name)-9, 6), 5, 2), 112) ASC),
@FullDirectoryPath + '\'+replace(substring(name, len(name)-23, 4), '_', '')+'\' + name,
replace(substring(name, len(name)-23, 4), '_', ''),
convert(datetime2(0), substring(name, len(name)-18, 8) + ' ' +
substring(substring(name, len(name)-9, 6), 1, 2)+ ':' + substring(substring(name, len(name)-9, 6), 3, 2)+ ':' +
substring(substring(name, len(name)-9, 6), 5, 2), 112)
FROM @DirectoryTree
WHERE depth = 2 AND isfile = 1
----------------------------------------------------------------------------------------------------
--// Select range of last backup files //--
----------------------------------------------------------------------------------------------------
DECLARE
@id_work int,
@date_start datetime2(0),
@date_end datetime2(0),
@date_end_full datetime2(0),
@dateformat nvarchar(32),
@folderformat varchar(32)
SELECT @id_work = MIN(id) FROM @Backups WHERE typebak = 'FULL' HAVING COUNT(*) > 1
SELECT @date_start = datebak FROM @Backups WHERE typebak = 'FULL' AND id = @id_work
SELECT @date_end_full = datebak FROM @Backups WHERE typebak = 'FULL' AND id = @id_work + 1
SELECT @date_end = MAX(datebak) FROM @Backups WHERE typebak <> 'FULL' AND datebak BETWEEN @date_start AND @date_end_full
DELETE FROM @Backups WHERE typebak = 'FULL' AND id = @id_work + 1
IF @date_end IS NULL
SET @date_end = @date_start;
----------------------------------------------------------------------------------------------------
--// Select range of last backup files //--
----------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL
DROP TABLE ##FilesToCompress;
CREATE TABLE ##FilesToCompress (
id int identity(1,1),
filepath nvarchar(512)
);
INSERT INTO ##FilesToCompress(filepath)
SELECT filepath FROM @Backups WHERE datebak BETWEEN @date_start AND @date_end
ORDER BY datebak ASC
IF (@date_start <> @date_end)
SET @dateformat =
REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@date_start,120)),'-',''),' ','_'),':','')
+ '-' +
REPLACE(REPLACE(REPLACE((CONVERT(nvarchar, @date_end ,120)),'-',''),' ','_'),':','')
ELSE
SET @dateformat =
REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@date_start,120)),'-',''),' ','_'),':','')
-- Valid formats yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, , yyyy\MM
IF (@FormatFolder = 'yyyyMMdd')
SET @folderformat = CONVERT(VARCHAR(8), @date_start, 112)
ELSE IF (@FormatFolder = 'yyyyMM\dd')
SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112) + '\' + LEFT(CONVERT(VARCHAR(2), @date_start, 112), 2)
ELSE IF (@FormatFolder = 'yyyy\MM\dd')
SET @folderformat = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '\')
ELSE IF (@FormatFolder = 'yyyyMM')
SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112)
ELSE IF (@FormatFolder = 'yyyy\MM')
SET @folderformat = REPLACE(CONVERT(VARCHAR(7), GETDATE(), 111), '/', '\')
ELSE
SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112) -- Same as yyyyMM
DECLARE
@ExecString VARCHAR(8000),
@CompressFile NVARCHAR(200),
@FileList NVARCHAR(200)
SET @CompressFile = @DatabaseName + '_' + @dateformat +'.7z'
SET @FinalDirectory = @FinalDirectory + '\' + @ServerName + '\' + @DatabaseName + '\' + @folderformat
RAISERROR(@FinalDirectory,10,1) WITH NOWAIT
SET @ExecString = 'mkdir ' + @FinalDirectory
EXEC master..xp_cmdshell @ExecString, no_output;
SET @FileList = @FinalDirectory + '\' + @DatabaseName + '_' + @dateformat + '.txt'
SET @ExecString = 'bcp "select filepath from ##FilesToCompress" queryout ' + @FileList + ' -c -T -S' + @ServerName
EXEC master..xp_cmdshell @ExecString, no_output;
IF EXISTS(SELECT 1 FROM ##FilesToCompress)
BEGIN
IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL
DROP TABLE ##FilesToCompress;
DECLARE @SplitCommand VARCHAR(20) = ''
IF @SplitFileMB IS NOT NULL
SET @SplitCommand = '-v' + CAST(@SplitFileMB AS VARCHAR) + 'm '
SET @ExecString = '@7za.exe a -ssc -bd -mx'+ CAST(@CompressionLevel AS VARCHAR) +' -t7z -ms=on ' + @SplitCommand + '"' + @FinalDirectory + '\' + @CompressFile + '" @' + @FileList + ''
--PRINT @ExecString
EXEC master..xp_cmdshell @ExecString, no_output;
SET @ExecString = '@for /f "delims=" %i in (' + @FileList +') do del /f /q %i'
--PRINT @ExecString
EXEC master..xp_cmdshell @ExecString, no_output;
SET @ExecString = '@del ' + @FileList
--PRINT @ExecString
EXEC master..xp_cmdshell @ExecString, no_output;
END
----------------------------------------------------------------------------------------------------
--// Cleanup //--
----------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL
DROP TABLE ##FilesToCompress;
END
GO
No comments:
Post a Comment