如何使用批处理脚本备份SQL Server数据库？

为什么要使用批处理脚本来备份SQL Server数据库？

在 Windows 中，批处理脚本是存储在文本文件中的命令序列，可用于自动执行重复性任务。在SQL Server备份方面，批处理脚本可能不是相对简单的学习方法，但当您需要批量或定期备份数据库时，它无疑是一种高效的方法。

在接下来的文章中，我将分享备份 SQL数据库（甚至是一个实例的所有数据库）的批处理脚本，以及自动化任务的方法。

在开始之前，请确保您的计算机上安装了 SQLCMD 实用程序，因为它是输入 Transact-SQL 语句、系统过程和脚本文件的先决条件。

如果您运行的是SQL Server2014 或更早版本，则该实用程序随 SSMS 一起提供。但从SQL Server2016 开始，您需要单独安装它。下载 SQLCMD 实用程序并在这一页.

使用此实用程序，您可以编写批处理脚本来备份SQL Server数据库。基本命令是：

SqlCmd -E -S servername -Q "BACKUP DATABASE databasename TO DISK ='filepath\filename.bak'"

如果您只想备份一个或几个数据库而没有其他要求，只需在文本编辑器中输入此命令并填写您自己的信息即可。但是如果你想保留不同版本的备份，并且每个文件都包含了具体的备份时间，那么你可以使用下面的脚本：

ECHO OFF
:: 设置保存备份文件的路径例如 D:\backup
set BACKUPPATH=

:: 设置服务器和实例的名称
set SERVERNAME=

:: 设置数据库名称
set DATABASENAME=

:: 文件名格式 Name-Date
For /f "tokens =2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)

set DATESTAMP=%mydate%_%mytime%
set BACKUPFILENAME=%BACKUPPATH%\%DATABASENAME%-%DATESTAMP%.bak

SqlCmd -E -S %SERVERNAME% -d master -Q "备份数据库 [%DATABASENAME%] 到磁盘 = N'%BACKUPFILENAME%' WITH INIT, NOUNLOAD, NAME = N'%DATABASENAME% backup', NOSKIP, STATS = 10, NOFORMAT”
ECHO.

填写您的备份路径、服务器名称和数据库名称，然后使用 .bat 扩展名保存此备份脚本。因此，您可以随时双击批处理文件备份 SQL数据库。

在某些情况下，您可能不仅要备份一两个数据库，还要备份SQL Server实例中的所有数据库。什么样的备份脚本可以帮助您做到这一点？

实际上，您只需要构建一个数据库列表并指定要从中排除的那些。例如，我想将我所有的 SQL数据库（系统数据库除外）备份到 D:\Backup，那么下面的批处理脚本将起作用：

@ECHO OFF
SETLOCAL
REM 以 YYYY-MM-DD 格式获取日期（假设语言环境是美国）
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T' ) DO SET NowDate=%%D-%%B-%%C

REM 建立要备份的数据库列表
SET DBList=D:\SQLDBList.txt
SqlCmd -E -S DESKTOP-AC51C0M -h-1 -W -Q " SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

REM 备份每个数据库，添加日期到文件名
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO 备份数据库：%%I
SqlCmd -E -S DESKTOP-AC51C0M -Q "BACKUP DATABASE [%%I] TO Disk ='D:\Backup\%%I-%NowDate%.bak'"
ECHO.)

REM 清理临时文件
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

ENDLOCAL

您可以修改构建 DBlist 的路径（之后会自动删除）和保存备份的文件夹。此外，此脚本不会备份系统数据库（'master'、'model'、'msdb'、'tempdb'），您可以添加要排除的其他数据库。

为了便于管理，一些用户可能希望创建一个带有时间标签的文件夹，以单独保存每个备份创建的所有 bak 文件。因此，我还将提供一个替代脚本供您参考。

@ECHO OFF
SETLOCAL
REM 以 YYYY-MM-DD 格式获取日期（假设语言环境是美国）
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T' ) DO SET NowDate=%%D-%%B-%%C

REM 建立要备份的数据库列表
SET DBList=D:\SQLDBList.txt
SqlCmd -E -S DESKTOP-AC51C0M -h-1 -W -Q " SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

mkdir D:\Backup
mkdir D: \Backup\%NowDate%
REM 备份每个数据库，在文件名前添加日期
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO 备份数据库：%%I
SqlCmd -E -S DESKTOP -AC51C0M -Q"将数据库 [%%I] 备份到磁盘 ='D:\Backup\%NowDate%\%%I.bak'"
ECHO。）

REM 清理临时文件
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

ENDLOCAL