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:
- To use the WinRAR command-line utility, we set the PATH variable on the database server
- Create a T-SQL script to generate a compressed and copy_only backup of the database
- 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 <Hostname> 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:
- 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
- Generate the compressed backup on the local disk of the server. You can put the backup on the network location
- 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
- 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:
- @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)
- @Backup_Location: This variable holds the value of the location of the native SQL backup of the SQL Database
- @SizeOfWinRARFile: This variable holds the size of the WinRAR file. The compressed backup will be split into the file size specified in this variable
- @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:
|
1 2 3 4 5 6 7 8 9 10 11 |
Use DBA Go Create procedure sp_generate_compressed_backup @DBName varchar(500), @Backup_Location varchar(max), @SizeOfWinRARFile int, @CompressedBackupFileLocation varchar(max) as Begin End |
In the stored procedure, first section is variable declaration. The code is following:
|
1 2 3 4 |
declare @BackupSQL nvarchar(max) declare @Backup_Name varchar(max) declare @WinRarCommand varchar(5000) set @Backup_Name=@DBName+'_' + replace(convert(date,getdate()),'-','_') |
Description of the variables:
- @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)
- @BackupSQL: This parameter holds the dynamic T-SQL query which generates the backup of SQL database. The datatype of the command is nvarchar(max)
- @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:
|
1 |
set @BackupSQL= 'Backup database [' + @DBName + '] to disk ='''+@Backup_Location+ '\'+@BackupName + '.bak'' with compression,copy_only' |
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:
|
1 |
set @WinRarCommand='" rar.exe" a -v' +Convert(varchar,@SizeOfWinRARFile)+'M ' + @CompressedBackupFileLocation + '\' + @Backup_Name +'.rar '+@Backup_Location + '\'+@Backup_Name + '.bak' |
The entire code of the stored procedure is the following. Execute it in the DBA database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Use DBA Go Alter procedure sp_generate_compressed_backup @DBName varchar(500), @Backup_Location varchar(max), @SizeOfWinRARFile int, @CompressedBackupFileLocation varchar(max) as Begin declare @BackupSQL nvarchar(max) declare @Backup_Name varchar(max) declare @WinRarCommand varchar(5000) set @Backup_Name=@DBName+'_' + replace(convert(date,getdate()),'-','_') set @BackupSQL= 'Backup database [' + @DBName + '] to disk ='''+@Backup_Location+ '\'+@Backup_Name + '.bak'' with compression,copy_only' set @WinRarCommand='"rar.exe" a -v' +Convert(varchar,@SizeOfWinRARFile)+'M ' + @CompressedBackupFileLocation + '\' + @Backup_Name +'.rar '+@Backup_Location + '\'+@Backup_Name + '.bak' exec sp_executesql @BackupSQL exec xp_cmdshell @Winrarcommand End |
Now, to test the stored procedure, execute the stored procedure with valid input parameters. Following is the sample code:
|
1 |
exec sp_generate_compressed_backup 'AdventureWorks2017','C:\Backups',5,'D:\Compressed_Backups' |
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:
Summary
In this article, we learned how the backups of the SQL Database could be compressed and split by using WinRAR software.
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022