SQL Server Backup Compression using WinRAR


By:   |   Updated: 2019-12-31   |   Comments (1)   |   Related: More > Compression

Problem

How do you compress a SQL Server backup file and split it into multiple *.rar files using the WinRAR utility?  Also, once the database backup file is split, how can you reconstruct the file to restore it?

Solution

In this article, I will explain how to compress a SQL Server database backup and split it into multiple files using the WinRAR utility.

WinRAR software has a set of its own DOS commands. Using those commands, we can compress files, split files and many more tasks. Using the xp_cmdshell extended stored procedure, we can run a DOS command from a SQL Server stored procedure.

To run the WinRAR commands using xp_cmdshell, execute the following steps:

Add the location of WinRAR executables in the PATH variable. To do that, right-click on My Computer (This PC for Windows 10) > click on Advanced System Properties > click on Advanced tab > Click on the “Environment variables” button > select “PATH” from system variable text box and click on Edit.

See the following image:

add windows environment variable

In the Edit environment variable dialog box, click on New and provide the location of the WinRAR executable file. See the following image:

add windows environment variable

Now, let us verify that the environment variable is set correctly, to do that, open command prompt and run following command.

C:\Users\Nisarg> rar

The following should be the output:

get help about rar

The syntax of the WinRAR command is as follows:

Rar a -v<Size of compressed files in MB>  <Location of compressed file>  <Fully qualified name of the source file> 

For example:

Rar a -v50M “C:\Temp\DemoFile.rar” ”C:\Temp\DemoFile.docx.”

In the command, the -v argument represents the size of the compressed file. For example, if you have specified -v50M in the rar command and you are compressing a 100MB file, then it will create two compressed files. The size of each compressed file will be 50MB.

To execute the stored procedure, we must enable xp_cmdshell. To do that, execute the following command:

use master
GO
exec sp_configure 'show advanced options',1
reconfigure with override
exec sp_configure 'xp_cmdshell',1
reconfigure with override

Now, let me explain the stored procedure, which is used to create the backup. The script performs the following tasks:

Stored Procedure Input Parameters

The stored procedure accepts four parameters:

  1. @DatabaseName: The value of this parameter is the name of the database.
  2. @BackupPath: The @BackupPath parameter is the location where you want to save the backup file.
  3. @FileSize: The value of this parameter is the size of the RAR file in MB.
  4. @CompressedBackupPath: This parameter is the location where you want to save the compressed backup files. If the value of this parameter is NULL or empty string, then the procedure will save the backup on the location value provided in the @BackupPath parameter.

Stored Procedure Variables

The stored procedure will use the following variables:

  1. @BackupName:This variable holds the name of the backup. Filename format is <DatabaseName>_<Date of backup in DD_MM_YYYY format>.bak
  2. @SQLText: This parameter holds the backup command. The destination of backup files changes based on the input parameter; hence, I have created dynamic SQL Command to generate the database backup.
  3. @CompressionCommand: This parameter holds the DOS command to generate the rar files. The destination path of rar files varies based on the input parameters; hence, I have created a dynamic command to compress the backup file.

Stored Procedure Variable Declaration

declare @SQLText nvarchar(max)  
declare @BackupName varchar(max)  
declare @CompressionCommand varchar(5000)  
set @[email protected]+'_' + replace(convert(date,getdate()),'-','_')   

Dynamically Creating SQL Server Backup Command

First, the procedure will check the value of the @CompressedBackupPath variable to determine the destination of the rar files. To do that, I have used an if condition. Set the values of @CompressionCommand and @SQLText accordingly. Following is the code:

if (@CompressedBackupPath is null OR @CompressedBackupPath='')
begin
set @[email protected]
set @SQLText= 'Backup database [' + @DatabaseName + '] to disk ='''[email protected] + '\'[email protected] + '.bak'' with compression,copy_only'
set @CompressionCommand='"C:\Program Files\WinRAR\rar.exe" a -v'+Convert(varchar,@FileSize)+'M ' + @CompressedBackupPath + '\' + @BackupName +'.rar '[email protected] + '\'[email protected] + '.bak'
End
else
/*Backup and compressed file will be generated based on the values on parameters*/
begin
set @SQLText= 'Backup database [' + @DatabaseName + '] to disk ='''[email protected] + '\'[email protected] + '.bak'' with compression,copy_only'
set @CompressionCommand='"C:\Program Files\WinRAR\rar.exe" a -v' +Convert(varchar,@FileSize)+'M ' + @CompressedBackupPath + '\' + @BackupName +'.rar '[email protected] + '\'[email protected] + '.bak'
End

Complete GenerateBackup Stored Procedure

The entire T-SQL code for the stored procedure is as follows:

Use DBA
GO
create proc GenerateBackup
   @DatabaseName varchar(max),
   @BackupPath varchar(max),
   @FileSize int,
   @CompressedBackupPath varchar(max) 
as
begin
   declare @SQLText nvarchar(max)
   declare @BackupName varchar(max)
   declare @CompressionCommand varchar(5000)
   set @[email protected]+'_' + replace(convert(date,getdate()),'-','_') 
   if (@CompressedBackupPath is null OR @CompressedBackupPath='')
   begin
      set @[email protected]
      print @CompressedBackupPath
      set @SQLText= 'Backup database [' + @DatabaseName + '] to disk ='''[email protected] + '\'[email protected] + '.bak'' with compression,copy_only'
      set @CompressionCommand='"C:\Program Files\WinRAR\rar.exe" a -v'+Convert(varchar,@FileSize)+'M ' + @CompressedBackupPath + '\' + @BackupName +'.rar '[email protected] + '\'[email protected] + '.bak'
   end
   else
   begin
      set @SQLText= 'Backup database [' + @DatabaseName + '] to disk ='''[email protected] + '\'[email protected] + '.bak'' with compression,copy_only'
      set @CompressionCommand='"C:\Program Files\WinRAR\rar.exe" a -v' +Convert(varchar,@FileSize)+'M ' + @CompressedBackupPath + '\' + @BackupName +'.rar '[email protected] + '\'[email protected] + '.bak'
   end
   exec sp_executesql @SQLText
   exec xp_cmdshell @CompressionCommand
end

Case 1: SQL Server Backup File and Archive File in the Same Location

To save the backup file and RAR file to the same location, execute the stored procedure as shown below.

Use DBA
GO
exec GenerateBackup 'AdventureWorks2017','C:\Backups',5,''

The above code saves the backup and RAR file in the “C:\Backups” folder. See the following output:

database backup files

Case 2: SQL Server Backup File and Archive File in a Different Location

To save the backup file and the RAR file in a different location, execute the procedure, as shown below:

Use DBA
GO
exec GenerateBackup 'AdventureWorks2017','C:\Backups',5,'C:\WinRar\Backups'

The above code saves the backup on the “C:\Backups” folder and saves the archive files on “C:\WinRar\Backups.

database backup

Extract the SQL Server Backup File from WinRAR files to Restore the Database

To extract the .bak (SQL backup file), we do not have to extract individual rar files.  You need to extract the first file. The file format will be <FileName>.01.rar. To do that, right-click on the first WinRAR file and click on “Extract File.” See the following image:

backup files

In the “Extraction path and options” dialog box, provide the destination path in the “Destination Path” text box and click OK. See the following image:

extract compressed file

Once the backup is extracted successfully, you can restore it on the desired server / instance. See the following image:

database backup

Summary

In this article, we learned how to compress and split a SQL backup file into multiple RAR files using the WinRAR utility to save disk space and permit easier copying of files.  We also learned how to reconstruct the original backup file in order to perform a database restore.

Next Step

Check out these tips:



Last Updated: 2019-12-31


get scripts

next tip button



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips
Related Resources





Comments For This Article




Tuesday, December 31, 2019 - 12:11:17 AM - Aveek Back To Top (83565)

Great article Nisarg. Thanks!



download


Recommended Reading

SQL Server 2008 Backup Compression

Using COMPRESS and DECOMPRESS in SQL Server to Save Disk Space

SQL Server 2016 COMPRESS and DECOMPRESS functions

SQL Server Stored Procedure to Calculate Database Backup Compression Ratio

Demonstrating the Effects of Using Data Compression in SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools