批处理备份SQL SERVER并自动清理30以前的备份文件

分享一个正用的SQL SERVER数据库备份脚本

功能介绍：将文件备份到地本盘的一个临时文件夹中并压缩，经过测试22G的文件压缩后仅1.5G，压缩后将文件复制到网盘并删除源文件，最后删除网盘中30天以前的备份文件。

能用得上的朋友可以自取，看得起也可以在下方评论。

@echo off
set path=%path%;E:\Program Files\Microsoft SQL Server\100\Tools\Binn;C:\Program Files\7-Zip
set BAKPATH=E:\DATA_BACK_TEMP\
set DBNAME=DB2
set DATES=%date:~0,4%%date:~5,2%%date:~8,2%
set BACK_DB_NAME=%BAKPATH%%DBNAME%_%DATES%
set LOGFILE=%BAKPATH%logs\%DBNAME%_%DATES%.log.txt
set BACK_DISK=Z:\DATABACK\

echo %date% %time:~,-3% 开始备份%DBNAME%数据库 >> %LOGFILE%
OSQL.exe -U sa -P abc@1234567 -d master -Q "BACKUP DATABASE %DBNAME% to disk='%BACK_DB_NAME%.bak'"

echo %date% %time:~,-3% 开始使用7Z压缩%DBNAME%数据库，压缩包完后删除%DBNAME%_%DATES%.bak备份文件 >> %LOGFILE%
7z.exe a -t7z %BACK_DB_NAME%.7z "%BACK_DB_NAME%.bak" -r -mx=9 -m0=LZMA2 -ms=10m -mf=on -mhc=on -mmt=on -sdel

echo %date% %time:~,-3% 将压缩包%DBNAME%_%DATES%.7z复制到%BACK_DISK% >> %LOGFILE%

copy %BACK_DB_NAME%.7z %BACK_DISK%%DBNAME%_%DATES%.7z

echo %date% %time:~,-3% 删除临时压缩包%BACK_DB_NAME%.7z >> %LOGFILE%

del /q  %BACK_DB_NAME%.7z

echo %date% %time:~,-3% %DBNAME%数据库成功备份到%BACK_DISK%%DBNAME%_%DATES%.7z >> %LOGFILE%

echo %date% %time:~,-3% 开始清理%BACK_DISK% 30天以前的备份文件 >> %LOGFILE%

echo %date% %time:~,-3% 以下文件将被清理： >> %LOGFILE%

forfiles /p "Z:\DATABACK" /s /m *.* /d -30 >> %LOGFILE%

forfiles /p "Z:\DATABACK" /s /m *.* /d -30 /c "cmd /c del @path"

echo %date% %time:~,-3% 备份任务已完成 >> %LOGFILE%

稍作改进，支持传入参数，备分不同的数据库，取消复制到其他异地

@echo off
set path=%path%;E:\Program Files\Microsoft SQL Server\100\Tools\Binn;C:\Program Files\7-Zip
set BAKPATH=d:\DATA_BACK_TEMP\
set DBNAME=%1
set DATES=%date:~0,4%%date:~5,2%%date:~8,2%
set BACK_DB_NAME=%BAKPATH%%DBNAME%_%DATES%
set LOGFILE=d:\logs\%DBNAME%_%DATES%.log.txt

echo %date% %time:~,-3% 开始备份%DBNAME%数据库 >> %LOGFILE%
OSQL.exe -U sa -P Abc@369369 -d master -Q "BACKUP DATABASE %DBNAME% to disk='%BACK_DB_NAME%.bak'"

echo %date% %time:~,-3% 开始使用7Z压缩%DBNAME%数据库，压缩包完后删除%DBNAME%_%DATES%.bak备份文件 >> %LOGFILE%
7z.exe a -t7z %BACK_DB_NAME%.7z "%BACK_DB_NAME%.bak" -r -mx=9 -m0=LZMA2 -ms=10m -mf=on -mhc=on -mmt=on -sdel

echo %date% %time:~,-3% %DBNAME%数据库成功备份到%BAKPATH%%DBNAME%_%DATES%.7z >> %LOGFILE%

echo %date% %time:~,-3% 开始清理%BAKPATH% 30天以前的备份文件 >> %LOGFILE%

echo %date% %time:~,-3% 以下文件将被清理： >> %LOGFILE%

forfiles /p %BAKPATH% /s /m *.* /d -30 >> %LOGFILE% 2>>&1

forfiles /p %BAKPATH% /s /m *.* /d -30 /c "cmd /c del @path" 2>>null

echo %date% %time:~,-3% 备份任务已完成 >> %LOGFILE%