SQL Server Backup Compression using WinRAR

By: Nisarg Upadhyay   |   Updated: 2019-12-31   |   Comments (3)   |   Related: > 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:

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

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:

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 @BackupName=@DatabaseName+'_' + 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 @CompressedBackupPath=@BackupPath
set @SQLText= 'Backup database [' + @DatabaseName + '] to disk ='''+@BackupPath + '\'+@BackupName + '.bak'' with compression,copy_only'
set @CompressionCommand='"C:\Program Files\WinRAR\rar.exe" a -v'+Convert(varchar,@FileSize)+'M ' + @CompressedBackupPath + '\' + @BackupName +'.rar '+@BackupPath + '\'+@BackupName + '.bak'
End
else
/*Backup and compressed file will be generated based on the values on parameters*/
begin
set @SQLText= 'Backup database [' + @DatabaseName + '] to disk ='''+@BackupPath + '\'+@BackupName + '.bak'' with compression,copy_only'
set @CompressionCommand='"C:\Program Files\WinRAR\rar.exe" a -v' +Convert(varchar,@FileSize)+'M ' + @CompressedBackupPath + '\' + @BackupName +'.rar '+@BackupPath + '\'+@BackupName + '.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 @BackupName=@DatabaseName+'_' + replace(convert(date,getdate()),'-','_') 
   if (@CompressedBackupPath is null OR @CompressedBackupPath='')
   begin
      set @CompressedBackupPath=@BackupPath
      print @CompressedBackupPath
      set @SQLText= 'Backup database [' + @DatabaseName + '] to disk ='''+@BackupPath + '\'+@BackupName + '.bak'' with compression,copy_only'
      set @CompressionCommand='"C:\Program Files\WinRAR\rar.exe" a -v'+Convert(varchar,@FileSize)+'M ' + @CompressedBackupPath + '\' + @BackupName +'.rar '+@BackupPath + '\'+@BackupName + '.bak'
   end
   else
   begin
      set @SQLText= 'Backup database [' + @DatabaseName + '] to disk ='''+@BackupPath + '\'+@BackupName + '.bak'' with compression,copy_only'
      set @CompressionCommand='"C:\Program Files\WinRAR\rar.exe" a -v' +Convert(varchar,@FileSize)+'M ' + @CompressedBackupPath + '\' + @BackupName +'.rar '+@BackupPath + '\'+@BackupName + '.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:

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.”

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:

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:

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

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 Steps

Check out these tips:

• SQL Server Backup tips
• SQL Server Restore tips
• Get Started with SQL Server xp_cmdshell
• SQL Server Backup Tutorial
• SQL Server Restore Tutorial

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips