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:

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:

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

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:

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

#backup and restore #maintenance #database