ZIP backup and transfer to SQL Server Server database backup every hour

Question

I need to backup the SQL-Server Express database, grab it and transfer it to the FTP server every hour.

While I can create a backup, but I can’t zip it (I tried with 7z) and passed it, but at the command line:

Script.sql file:

DECLARE @pathName NVARCHAR(512) SET @pathName = 'C:\prd-db-backup-' + replace(replace(replace(convert(varchar(19), getdate(), 126),'-',''),'T',''),':','') + '.bak' BACKUP DATABASE [Database] TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N'Database-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 

Bat file:

 sqlcmd -S SQLSERVER -U user -P password -i script.sql PROBLEM1: 7z a -tzip C:\prd-db-backup-%date%%time%.zip -i! C:\prd-db-backup-%DATE%%TIME%*.bak FTP transfer: ? 

Thanks.

Answer 1

depending on your locale you may need to tweak this for date / time analysis

 rem get the last 10 chars in either dd-mm-yyyy or mm-dd-yyyy set dtm=%DATE:~-10% rem last 4 is the year set yyyy=%dtm:~-4% set mm=%dtm:~4,2% set dd=%dtm:~1,2% set tim1=%TIME:.=% set tim2=%tim1:,=% rem lets just copy everyting from this date sqlcmd -S SQLSERVER -U user -P password -i script.sql 7z a -tzip C:\prd-db-backup-%yyyy%%mm%%dd%%tim2%.zip -i! C:\prd-db-backup-%yyyy%%mm%%dd%*.bak echo user your-ftp-username > ftpcmd.txt echo pwd your-ftp-password >> ftpcmd.txt echo cd /ftp/dir/to/store >> ftmcmd.txt echo put C:\prd-db-backup-%yyyy%%mm%%dd%%tim2%.zip >> ftpcmd.txt echo bye >> ftpcmd.txt ftp -i -s:ftpcmd.txt your-ftp-server