由于工作需要,需要将安装在 Win Server 的 SQL SERVER 进行备份还原,写了个脚本满足所需。

原理:通过 batch 调用 osql.exe 执行T-SQL语句进行备份和还原操作,同时将执行日志重定向到文件,也使用mailsend-go 发送 SMTP 邮件进行消息通知,由于消息可以能会乱码,还需要 iconv 进行 GB2312 到 UTF8 转换。如果需要拷贝备份到其他主机,通过SMB挂载网络驱动器形式进行拷贝。

sql 备份主批处理 backupsql.bat

@ECHO OFF&&PUSHD %~DP0
@REM 源码 UTF8 编码,echo 无法输出中文,所以全部写成英语
setlocal EnableDelayedExpansion&&color 3e && cd /d "%~dp0"
TITLE SQL Backup Script
echo.------------------------------------
echo.
echo. SQL Backup Script
echo. Author: chn-student 
echo. E-Mail: chn-student@outlook.com
echo. Version: V0.3 2020.11.17
echo.
echo.------------------------------------
echo.
@REM 数据库连接变量设置
set SERVER="127.0.0.1"
set USERNAME="sa"
set PASSWORD="xxxxxx"
@REM 备份 T-SQL 文件名,和本批处理相同位置
set "SQLNAME=%~dp0\backup.sql"
@REM 备份目录和日志目录设置
set BACKUPSQLPATH="D:\backup\databases" 
set REMOTEBACKUPSQLPATH="\\172.16.172.1\backup\db"
set BACKUPLOGSPATH="D:\backup\logs" 
@REM 日志输出时间,文件名设置
set NEWTIME=%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%
@REM %LOGNAME% 记录控制台输出日志 %OSQLLOGNAME% 记录执行备份 backup.sql 输出的日志 
set LOGNAME="D:\backup\logs\%NEWTIME%-sql-backup-log.txt"
set OSQLLOGNAME="D:\backup\logs\%NEWTIME%-sql-backup-osql-log.txt"
@REM 自动删除文件过期备份文件和日志设置,设置为0则不删除
set DAYS=30
@REM 发送邮件提醒消息设置
set title="%date%-%time% Database Backup Log"
set smtpserver="smtp.163.com"
set smtpport=25
set user="user@163.com"
set token="password"
set sendto="user@163.com"
@REM 请勿更改下面的代码 
del sendlog.txt /f /q /a
del send.old /f /q /a
del send.txt /f /q /a
echo. %date% - %time% Create log file %LOGNAME%.
echo. %date% - %time% Create osql log file %OSQLLOGNAME%. >> %LOGNAME%
echo. > %OSQLLOGNAME%
for /f "tokens=2 delims=:" %%i in ('ipconfig^|findstr "Address"') do set ip=%%i
echo. %date% - %time% Server IP is !ip!. >> %LOGNAME%
echo. %date% - %time% Try to Found Backup SQL Script in %SQLNAME%.
echo. %date% - %time% Try to Found Backup SQL Script in %SQLNAME%. >> %LOGNAME%
if exist %SQLNAME% (
    echo. %date% - %time% Now start to backup.
    echo. %date% - %time% Now start to backup. >> %LOGNAME%
    @REM 调用 osql 进行备份,传入备份 t-sql 脚本,日志输出到 %logname% 
    osql.exe -S %SERVER% -U %USERNAME% -P %PASSWORD% -i %SQLNAME% -o %OSQLLOGNAME%
    @REM 进入工作目录,找到最新的备份文件,并复制为 latest.bak 为还原使用
    cd /d %BACKUPSQLPATH%
    for /f "tokens=*" %%f in ('dir /b /od /a-d') do (set f=%%f)
    echo. %date% - %time% The latest backup file is !f!, copy it as latest.bak
    echo. %date% - %time% The latest backup file is !f!, copy it as latest.bak >> %LOGNAME%
    @REM 调用路径处理函数,防止路径拼贴错误
    call :PathHandler !BACKUPSQLPATH! !f! "latest.bak"
    copy "!filepath!" "!copypath!" /Y >> %LOGNAME%
    echo. %date% - %time% Backup end. 
    echo. %date% - %time% Backup end. >> %LOGNAME%
    @REM 调用远程主机复制函数
    call :CopyToRemote !f! "latest.bak"
    if %DAYS% neq 0 (
        echo. %date% - %time% Now start to clean old backup files.
        echo. %date% - %time% Now start to clean old backup files. >> %LOGNAME%
        @REM 根据时间自动删除 %DAYS% 以前的备份文件和日志,若 %DAYS% = 0 则不删除
        forfiles /p %BACKUPSQLPATH% /s /m *.* /d -%DAYS% /c "cmd /c echo del /f /q /a @path" >> %LOGNAME%
        forfiles /p %BACKUPLOGSPATH% /s /m *.* /d -%DAYS% /c "cmd /c echo del /f /q /a @path" >> %LOGNAME%
        echo. %date% - %time% Clean old backup files end. >> %LOGNAME%
        @REM 调用远程主机删除函数,没法用
        @REM call :DeleteFromRemote
    ) 
) else (
    echo. %date% - %time% Can not Found Backup SQL Script,Backup failed.    
    echo. %date% - %time% Can not Found Backup SQL Script,Backup failed. >> %LOGNAME%
)


@REM 文件编码转换 & 两日志合一
cd /d "%~dp0"
echo. %date% - %time% Convert file encoding.
echo. %date% - %time% Convert file encoding. >> %LOGNAME%
copy %LOGNAME% sendlog.txt /Y 
echo ----------------------------------- >> sendlog.txt
rem echo "备份SQL执行结果:" >> sendlog.txt
type sendlog.txt > send.txt
more +1 %OSQLLOGNAME% >> send.txt
ren send.txt send.old
iconv -f GB2312 -t UTF-8 < send.old > send.txt

@REM 发送日志
cd /d "%~dp0"
echo. %date% - %time% Send log file to mail.
echo. %date% - %time% Send log file to mail. >> %LOGNAME%
mailsend-go -sub %title% -smtp %smtpserver% -port %smtpport% auth  -user  %user% -pass %token% -to %sendto% -from %user% -subject %title% -cs "utf8" body -file send.txt

@REM 删除产生文件
echo. %date% - %time% Delete all send temp files.
echo. %date% - %time% Delete all send temp files. >> %LOGNAME%
del sendlog.txt /f /q /a
del send.old /f /q /a
del send.txt /f /q /a

echo. %date% - %time% Thanks for your use. Press any key to exit.
echo. %date% - %time% Thanks for your use. Press any key to exit. >> %LOGNAME%
pause > nul
exit

:PathHandler
@REM 由于直接进行拼贴路径变量会产生引号问题,使用该方法去掉引号。
@REM ref:http://www.bathome.net/viewthread.php?tid=2397
set "filepath=%~1\%~2"
set "copypath=%~1\%~3"
goto:eof


:CopyToRemote
@REM 将最新版本备份文件拷贝到远程主机,并删除远程主机的过期备份文件。运行日志不拷贝
echo. %date% - %time% Now copy the latest backup sql file to remote.
echo. %date% - %time% Now copy the latest backup sql file to remote. >> %LOGNAME%
copy "!filepath!" "%REMOTEBACKUPSQLPATH%\%~1" /Y >> %LOGNAME%
copy "!copypath!" "%REMOTEBACKUPSQLPATH%\%~2" /Y >> %LOGNAME%
echo. %date% - %time% Copy to remote end.
echo. %date% - %time% Copy to remote end. >> %LOGNAME%
goto:eof


@REM :DeleteFromRemote 不支持
@REM @REM 将最新版本备份文件拷贝到远程主机,并删除远程主机的过期备份文件。运行日志不拷贝
@REM echo. %date% - %time% Now start to clean remote old backup files.
@REM echo. %date% - %time% Now start to clean remote old backup files. >> %LOGNAME%
@REM forfiles /p %REMOTEBACKUPSQLPATH% /s /m *.* /d -%DAYS% /c "cmd /c echo del /f /q /a @path" >> %LOGNAME%
@REM echo. %date% - %time% Clean remote old backup files end.
@REM echo. %date% - %time% Clean remote old backup files end. >> %LOGNAME%
@REM goto:eof

T-SQL 备份脚本backup.sql

use db; -- 数据库名
go
print CONVERT(char(20),getdate(),20) + ' ' + '备份开始。'
DECLARE @name varchar(50)
DECLARE @datetime char(15)
DECLARE @path varchar(255)
DECLARE @bakfile varchar(255)
DECLARE @msg varchar(50)
set @name='db'
set @datetime=REPLACE(CONVERT(char(12),getdate(),112),' ','') +'_'+REPLACE(CONVERT(char(12),getdate(),108),':','')
set @path='D:\backup\databases\'
set @bakfile=@path+''+@datetime+'_'+@name+''+'.bak'
print CONVERT(char(20),getdate(),20) + ' ' + '备份文件到 ' + @bakfile
backup database @name to disk=@bakfile with name=@name
if (@@ERROR <> 0 )
begin
   select @msg=convert(char(26),getdate(),20)+ ' ' + '备份失败!'
   print @msg
end
else
begin
   select @msg=convert(char(26),getdate(),20)+ ' ' + '备份成功!'
   print @msg
end
print CONVERT(char(20),getdate(),20) + ' ' + '备份结束。'
go

文件拷贝备份脚本 backupfile.bat

@ECHO OFF&PUSHD %~DP0
setlocal EnableDelayedExpansion&color 3e & cd /d "%~dp0"
TITLE File Backup Script
echo.------------------------------------
echo.
echo. File Backup Script 
echo. Author: chn-student
echo. E-Mail: chn-student@outlook.com
echo. Version: V0.1 2020.11.6
echo.
echo.------------------------------------
set FILEPATH="D:\appdata"
set BACKUPPATH="D:\backup\files"
set REMOTEPATH="\\172.16.172.1\backup\files"
set LOGNAME="D:\backup\logs\%NEWTIME%-file-backup-log.txt"
@REM 自动删除文件过期备份文件和日志设置,设置为0则不删除
set DAYS=30
@REM 发送邮件提醒消息设置
set title="%date%-%time% Files Backup Log"
set smtpserver="smtp.163.com"
set smtpport=25
set user="user@163.com"
set token="password"
set sendto="user@163.com"
@REM 请勿更改下面的代码 
del sendlog.txt /f /q /a
del send.old /f /q /a
del send.txt /f /q /a
if exist  %FILEPATH% (
    echo. %date% - %time% Now start to backup.  >>  %LOGNAME%
    XCOPY %FILEPATH% %BACKUPPATH% /S /E /Y  >>  %LOGNAME%
    XCOPY %FILEPATH% %REMOTEPATH% /S /E /Y  >>  %LOGNAME%
    echo. %date% - %time% Backup end.>>  %LOGNAME%
) else (
    echo. %date% - %time% Can not Found Backup File Path,Backup failed.    >>  %LOGNAME%
)


@REM 文件编码转换
cd /d "%~dp0"
echo. %date% - %time% Convert file encoding.
echo. %date% - %time% Convert file encoding. >> %LOGNAME%
copy %LOGNAME% sendlog.txt /Y 
echo ----------------------------------- >> sendlog.txt
rem echo "备份SQL执行结果:" >> sendlog.txt
type sendlog.txt > send.txt
ren send.txt send.old
iconv -f GB2312 -t UTF-8 < send.old > send.txt

@REM 发送日志
cd /d "%~dp0"
echo. %date% - %time% Send log file to mail.
echo. %date% - %time% Send log file to mail. >> %LOGNAME%
mailsend-go -sub %title% -smtp %smtpserver% -port %smtpport% auth  -user  %user% -pass %token% -to %sendto% -from %user% -subject %title% -cs "utf8" body -file send.txt

@REM 删除产生文件
echo. %date% - %time% Delete all send temp files.
echo. %date% - %time% Delete all send temp files. >> %LOGNAME%
del sendlog.txt /f /q /a
del send.old /f /q /a
del send.txt /f /q /a
echo. %date% - %time% Thanks for your use. Press any key to exit.>>  %LOGNAME%
rem pause > nul
exit

一键还原脚本 restore.sql由于有很强危险性,为了避免在生产环境误操作,所以加了很多安全提示。

@ECHO OFF&&PUSHD %~DP0
@REM 源码 UTF8 编码,echo 无法输出中文,所以全部写成英语
setlocal EnableDelayedExpansion&&color 4f && cd /d "%~dp0"
TITLE SQL Restore Script
echo.------------------------------------
echo.
echo. SQL Restore Script 
echo. Author: chn-studentg 
echo. E-Mail: chn-student@outlook.com
echo. Version: V0.3 2020.11.18
echo.
echo.------------------------------------
echo.
@REM 变量设置开始,如有需要编辑以下部分
set SERVER="127.0.0.1"
set USERNAME="sa"
set PASSWORD="xxxxxxx"
@REM 还原 T-SQL 文件名,和本批处理相同位置
set SQLNAME=%~dp0\restore.sql
@REM 备份目录和日志目录设置
set BACKUPPATH="D:\backup\db"
set NEWTIME=%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%
@REM %LOGNAME% 记录控制台输出日志 %OSQLLOGNAME% 记录执行备份 restore.sql 输出的日志 
set LOGNAME="D:\backup\logs\%NEWTIME%-sql-restore-log.txt"
set OSQLLOGNAME="D:\backup\logs\%NEWTIME%-sql-restore-osql-log.txt"
@REM 发送邮件提醒消息设置
set title="%date%-%time% Database Restore Log"
set smtpserver="smtp.163.com"
set smtpport=25
set user="user@163.com"
set token="password"
set sendto="user@163.com"
@REM 变量设置结束,下部分请勿更改
del sendlog.txt /f /q /a
del send.old /f /q /a
del send.txt /f /q /a
set TEMPPATH="D:\backup\temp"
set "err=%errorlevel%"
echo. %date% - %time% Create log file %LOGNAME%.
echo. %date% - %time% Create osql log file %OSQLLOGNAME%. >> %LOGNAME%
echo. > %OSQLLOGNAME%
for /f "tokens=2 delims=:" %%i in ('ipconfig^|findstr "Address"') do set ip=%%i
echo. %date% - %time% Server IP is !ip!. >> %LOGNAME%
if exist %BACKUPPATH% (
    @REM 输出安全警告提示,防止把生产环境搞毁
    echo. %date% - %time% Warning. Do not use this in production environment, you must know what you will do.
    echo. %date% - %time% If you still need to do, please input 'unlock' or input others to exit.
    set /p input=  %date% - %time% Unlock is very dangerous. Please think it over. Your input:
    @REM 输入解锁判断
    if "!input!"=="unlock" (
        cd /d "%BACKUPPATH%"
        @REM 2020.11.9 修改:使用固定名字的备份还原文件,不再使用日期排序判断
        @REM for /f "tokens=*" %%f in ('dir /b /od /a-d') do (set f=%%f)
        @REM echo. %date% - %time% The latest backup file is !f! 
        set /p i=  %date% - %time% Press Y To Start Restore or other to exit. Your input:
        if "!i!"=="Y" (
            @REM 判断还原脚本是否存在
            if exist %SQLNAME% ( 
                @REM 调用路径处理函数,防止路径拼贴错误
                call :PathHandler !BACKUPPATH! "latest.bak"
                @REM 判断最新的备份还原文件是否存在
                if exist "%BACKUPPATH%" (
                    @REM 拷贝备份还原文件到绝对路径(临时工作目录)
                    if exist "%TEMPPATH%" (
                        @REM 删掉临时目录里的所有东西,并复制备份还原文件到临时目录
                        echo. %date% - %time% Now copy restore file to temp folder.
                        echo. %date% - %time% Now copy restore file to temp folder. >> %LOGNAME%
                        del /s /Q %TEMPPATH% >> %LOGNAME%
                        copy "!filepath!" "!TEMPPATH!" /Y >> %LOGNAME%
                        if "%err%"=="0" (
                            @REM 调用 OSQL 进行还原,输出日志
                            echo. %date% - %time% Now OA SQL Restore start. 
                            echo. %date% - %time% Now OA SQL Restore start. >> %LOGNAME%
                            osql.exe -S %SERVER% -U %USERNAME% -P %PASSWORD% -i %SQLNAME% -o %OSQLLOGNAME%
                            echo. %date% - %time% Restore end.
                            echo. %date% - %time% Restore end. >> %LOGNAME%
                            echo. %date% - %time% Now clean all temp file.
                            echo. %date% - %time% Now clean all temp file. >> %LOGNAME%
                            del /s /Q %TEMPPATH% >> %LOGNAME%
                        ) else (
                            echo. Can not restore, temp folder may be wrong.
                            echo. Can not restore, temp folder may be wrong. >> %LOGNAME%
                        )
                    ) else (
                        echo. %date% - %time% Temp folder not exist, Restore failed.
                        echo. %date% - %time% Temp folder not exist, Restore failed. >> %LOGNAME%
                    )
                ) else (
                    echo. %date% - %time% Can not Found The latest backup file, Restore failed.
                    echo. %date% - %time% Can not Found The latest backup file, Restore failed. >> %LOGNAME%
                )  
            ) else (
                echo. %date% - %time% Can not Found Restore SQL Script, Restore failed.    
                echo. %date% - %time% Can not Found Restore SQL Script, Restore failed. >> %LOGNAME%   
            )
        ) else (
            exit
        )    
    ) else (
        exit
    )
) else (
    echo. %date% - %time% Can not Found Backup Path,Backup failed.    
    echo. %date% - %time% Can not Found Backup Path,Backup failed. >> %LOGNAME%      
)


@REM 文件编码转换 & 两日志合一
cd /d "%~dp0"
echo. %date% - %time% Convert file encoding.
echo. %date% - %time% Convert file encoding. >> %LOGNAME%
copy %LOGNAME% sendlog.txt /Y 
echo ----------------------------------- >> sendlog.txt
rem echo "备份SQL执行结果:" >> sendlog.txt
type sendlog.txt > send.txt
more +1 %OSQLLOGNAME% >> send.txt
ren send.txt send.old
iconv -f GB2312 -t UTF-8 < send.old > send.txt

@REM 发送日志
cd /d "%~dp0"
echo. %date% - %time% Send log file to mail.
echo. %date% - %time% Send log file to mail. >> %LOGNAME%
mailsend-go -sub %title% -smtp %smtpserver% -port %smtpport% auth  -user  %user% -pass %token% -to %sendto% -from %user% -subject %title% -cs "utf8" body -file send.txt

@REM 删除产生文件
echo. %date% - %time% Delete all send temp files.
echo. %date% - %time% Delete all send temp files. >> %LOGNAME%
del sendlog.txt /f /q /a
del send.old /f /q /a
del send.txt /f /q /a

echo. %date% - %time% Thanks for your use. Press any key to exit.
echo. %date% - %time% Thanks for your use. Press any key to exit. >> %LOGNAME%
pause > nul
exit


:PathHandler
@REM 由于直接进行拼贴路径变量会产生引号问题,使用该方法去掉引号。
@REM ref:http://www.bathome.net/viewthread.php?tid=2397
set "filepath=%~1\%~2"
goto:eof

T-SQL 还原SQL脚本 restore.bat

use master
go 
DECLARE @name varchar(70)
DECLARE @msg varchar(50)
DECLARE @datetime char(15)
DECLARE @temppath varchar(70)
DECLARE @filepath varchar(100)
set @name='db'
set @temppath='D:\backup\temp\'
set @filepath=@temppath+'latest.bak'
print CONVERT(char(20),getdate(),20) + ' ' + '还原开始。'

print CONVERT(char(20),getdate(),20) + ' ' + '更改数据库为单用户模式。'
--将数据库模式设置为单用户,限制其他人访问
exec('ALTER DATABASE '+ @name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
 
--开始还原数据库(覆盖现在有数据库)
print CONVERT(char(20),getdate(),20) + ' ' + '覆盖数据库还原中。'
restore database @name from disk=@filepath with replace
 
if (@@ERROR <> 0 )
begin
   select @msg=convert(char(26),getdate(),20)+ ' ' + '还原失败!'
   print @msg
end
else
begin
   select @msg=convert(char(26),getdate(),20)+ ' ' + '还原成功!'
   print @msg
end

print CONVERT(char(20),getdate(),20) + ' ' + '更改数据库为多用户模式。' 
--将数据库模式设置为多用户
exec('ALTER DATABASE '+ @name + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE')
go

print CONVERT(char(20),getdate(),20) + ' ' + '还原结束。'
go

当然,也可以写sql自动备份任务和文件自动备份任务进行备份。

sql自动备份任务

@ECHO OFF&PUSHD %~DP0
setlocal EnableDelayedExpansion&color 3e & cd /d "%~dp0"
echo ----------------------------------- 
echo 创建自动定时任务
schtasks  /create  /tn  backupsql /tr  D:\backup\backupsql.bat  /sc  DAILY /st  01:30:00
echo ----------------------------------- 
echo 查看自动定时任务
schtasks  /Query  /tn backupsql 
echo ----------------------------------- 
rem 删除自动定时任务
rem schtasks /Delete /tn backupsql 
pause > nul
exit

文件自动备份任务

@ECHO OFF&PUSHD %~DP0
setlocal EnableDelayedExpansion&color 3e & cd /d "%~dp0"
echo ----------------------------------- 
echo 创建自动定时任务
schtasks  /create  /tn  backupfile /tr  D:\backup\backupfile.bat  /sc  DAILY /st  02:00:00
echo ----------------------------------- 
echo 查看自动定时任务
schtasks  /Query  /tn backupfile 
echo ----------------------------------- 
rem 删除自动定时任务
rem schtasks /Delete /tn backupfile 
pause > nul
exit

由于涉及到公司隐私安全,就不把源码上传到 GitHub 了,不过我已经把核心代码都贴了出来,懂得都懂。