WinServer + MSSQL 备份还原脚本
由于工作需要，需要将安装在 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:eofT-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:eofT-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 了，不过我已经把核心代码都贴了出来，懂得都懂。
评论已关闭