Backup and Archive Sql Server Database

-- Set the name of the archive backup directory.

DECLARE @bakdir VARCHAR(300)

SET @bakdir = 'D:\radmade_projects\src\code\_db\'

-- Set the name of the database.

DECLARE @dbname NVARCHAR(1024)

SET @dbname = 'rmMedia'

-- Set the name of the database backup directory.

DECLARE @dbbakdir VARCHAR(300)

SET @dbbakdir = @bakdir 

-- Create the name of the backup file from the database name and the current date.

DECLARE @bakname VARCHAR(300)

SET @bakname = @dbname + '_backup_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 112) + CONVERT(VARCHAR(20), GETDATE(), 108),':','')

-- Set the name of the backup file.

DECLARE @filename VARCHAR(300)

SET @filename = @dbbakdir + '\' + @bakname+'.bak'

-- Create the directories if necessary.

EXECUTE master.dbo.xp_create_subdir @dbbakdir

EXECUTE master.dbo.xp_create_subdir @bakdir

-- Backup the database.

BACKUP DATABASE @dbname

TO  DISK = @filename

WITH NOFORMAT, NOINIT,  NAME = @bakname, SKIP, REWIND, NOUNLOAD,  STATS = 10

-- Turn on the 'xp_cmdshell' function.

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

-- Build the command line string to add the file to the ZIP archive.

DECLARE @cmd VARCHAR(300)

--set @cmd = '"C:\program files\7-zip\7z.exe" a -tzip "D:\radmade_projects\src\code\_db\rmMedia\db.zip"  "D:\radmade_projects\src\code\_db\rmMedia\db.bak"'

SET @cmd = 'cd.. && "C:\program files\7-zip\7z.exe" a -tzip "' + @bakdir + @bakname + '.zip" "' + @filename + '"'

-- Execute the command.

EXEC xp_cmdshell @cmd

SET @cmd = 'del "' + @filename + '"'

-- Execute the command.

EXEC xp_cmdshell @cmd

-- Turn off the 'xp_cmdshell' function.

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

GO