By: Nisarg Upadhyay | 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:
- @DatabaseName: The value of this parameter is the name of the database.
- @BackupPath: The @BackupPath parameter is the location where you want to save the backup file.
- @FileSize: The value of this parameter is the size of the RAR file in MB.
- @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:
- @BackupName:This variable holds the name of the backup. Filename format is <DatabaseName>_<Date of backup in DD_MM_YYYY format>.bak
- @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.
- @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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips