Nisarg Upadhyay
The Backup of is compressed and split into multiple files

Compress and split SQL database backups using WinRar

August 4, 2020 by

Recently, we have received a strange request from our customer. They want us to set up a schedule a backup job that generates a backup of SQL database, compress the backup file in multiple compressed archive files (WinRAR files.) We tried to explain to the customer that the SQL Server native backups are capable of compressing the backup file, and it can split a large and compressed backup into multiple backup files. But they insisted us to use the WinRAR software to compress and split the backup.

The IT team of the company has set up the network drive to save the backup file. To accomplish the task, we had taken the following approach:

  1. To use the WinRAR command-line utility, we set the PATH variable on the database server
  2. Create a T-SQL script to generate a compressed and copy_only backup of the database
  3. Using WinRAR command-line utility, compress and divide the backup file in multiple WinRAR files and copy them to the network location

For the demonstration, I have installed WinRAR software from here, restored a SQL database named AdventureWorks2017 on my workstation.

Set the PATH system variable in windows server

To set the environment variable, Open Control Panel Click on System. See the following image:

Control Panel

A dialog box, Systems opens. (Screen 1). On the dialog box, click on Advance System properties. On System Properties dialog box (Screen 2), click on the Advanced tab. In the Advanced tab, click on Environment variables. See the following image:

Advance System Settings

A dialog box environment variable opens. From User Variable for <Hostname> list box, select PATH and click on Edit. See the following image:

Environmental Variables

A dialog box named Edit environment variable opens. On the dialog box, click on New and add the location of the Winrar.exe file. Click on OK to close the Environment Variables dialog box. See the following image:

Add path of Winrar.exe to compress the

Click OK to close the environment variable dialog box and click OK to close the System Properties dialog box.

Create a store procedure to generate the backup

We will use a SQL Server stored procedure to generate the backup. The logic of the stored procedure is as follows:

  1. When you execute the procedure, we must pass the name of the database as an input parameter. The procedure takes the backup of the database specified in the input parameter
  2. Generate the compressed backup on the local disk of the server. You can put the backup on the network location
  3. Enable xp_cmdshell on the server where the SQL Database is hosted. The xp_cmdshell command is used to executes the DOS command on the computer using a T-SQL Query
  4. Use xp_cmdshell to execute the rar.exe command to generate a backup file and split it into multiple WinRAR files

The stored procedure accepts the following input parameters:

  1. @DBName: This parameter holds the name of the SQL database. It is an input parameter of the stored procedure. The data type is varchar(500)
  2. @Backup_Location: This variable holds the value of the location of the native SQL backup of the SQL Database
  3. @SizeOfWinRARFile: This variable holds the size of the WinRAR file. The compressed backup will be split into the file size specified in this variable
  4. @CompressedBackupFileLocation: This parameter specifies the location of the drive where you want to save the WinRAR archive files

Below is the Create Procedure statement with input variables:

In the stored procedure, first section is variable declaration. The code is following:

Description of the variables:

  1. @Backup_Name: This parameter holds the name of the backup. It is the combination of input parameter @DBName and the current date time (getdate() function). The format of backup file is: @DBName+’_’ + replace(convert(date,getdate()),’-‘,’_’). The datatype of the parameter is nvarchar(1000)
  2. @BackupSQL: This parameter holds the dynamic T-SQL query which generates the backup of SQL database. The datatype of the command is nvarchar(max)
  3. @WinRarCommand: This parameter holds the dynamic query which executes a WinRAR command to compress and split the backup using xp_cmdshell. The datatype of the variable is nvarchar(max)

Another section is the code block. Its code generates the two dynamic T-SQL queries. The first query is used to generate the backup, and it is stored in the @BackupSQL variable. Following is the code of the first query:

The second query generates the dynamic query that is used to form a Winrar command, and it is stored in @WinrarCommand variable. Following is the code of the second query:

The entire code of the stored procedure is the following. Execute it in the DBA database.

Now, to test the stored procedure, execute the stored procedure with valid input parameters. Following is the sample code:

Based on the input variable, the stored procedure generated the backup of the SQL Database on the location specified in the SP. Following is the screenshot:

The Backup of  is compressed and split into multiple files

Summary

In this article, we learned how the backups of the SQL Database could be compressed and split by using WinRAR software.

Nisarg Upadhyay
Backup and restore, Maintenance

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

168 Views