What's up!

Pyaarey Allah!

Wednesday, April 1, 2015

Backup and Archive Sql Server Database

0 comments

-- 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