Twitter
Facebook
RSS
-- 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
