备份zip并每小时向FTP备份服务器传输一个SQL Server Express数据库

Question

我需要备份SQL Server Express数据库，压缩它，并每小时将其传输到FTP服务器。

到目前为止，我可以创建备份，但我无法压缩它（我尝试了7z）并传输它，但命令行：

script.sql文件：

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

蝙蝠文件：

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

谢谢。

Answer 1

根据您的语言环境设置，您可能需要针对日期/时间解析进行调整

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