windows下批处理自动备份文件和SQLSERVER数据库

发布于 2018-11-21  224 次阅读


以下保存为backup.bat
@echo off
set savePath="保存路径"
set keepDays=7
set logPath="日志路径"
set host="数据库服务器地址"
set user="用户名"
set pwd="密码"
set webPath="备份站点目录"

date /t >> %logPath%
time /t >> %logPath%

echo 开始删除%keepDays%天前文件 >> %logPath%
forfiles /p "%savePath%" /s /m *.zip /d -%keepDays% /c "cmd /c del @file"
forfiles /p "%savePath%" /s /m *.bak /d -%keepDays% /c "cmd /c del @file"
echo 删除完成>> %logPath%

echo 开始备份数据库 >> %logPath%
sqlcmd -S %host% -U %user% -P %pwd% -i backup.sql
echo 数据库备份结束 >> %logPath%

echo 开始备份网站文件 >> %logPath%
E:\7-Zip\7z.exe a %savePath%\web%date:~0,4%%date:~5,2%%date:~8,2%.zip %webPath%
echo 网站文件备份完成 >> %logPath%

date /t >> %logPath%
time /t >> %logPath%
以下保存为backup.sql
 
DECLARE @name varchar(50)
DECLARE @datetime char(14)
DECLARE @path varchar(255)
DECLARE @bakfile varchar(255)
set @name='数据库名称'
set @datetime=CONVERT(char(8),getdate(),112) + REPLACE(CONVERT(char(8),getdate(),108),':','')
set @path='数据库备份文件保存位置'
set @bakfile=@path+'\'+'Bak_'+@datetime+'_'+ @name + '.bak'
backup database @name to disk=@bakfile with name=@name
go
 

然后在windows中添加计划任务即可,保留最新7天文件


LoneKing