批处理脚本以备份所有SQL Server数据库

We have already covered how to back up a SQL Server database from the command line, so what if you want to backup all your databases at once? You could create a batch script which runs the backup command for each database, but this script would have to be updated each time a database is added or removed. Additionally, the database backups will all be appended to one file which will grow by the size of the new backup each time it is run. Instead, in true “set it and forget it” fashion, we will create a batch script which will adapt to your SQL Server as new databases are added and removed.

我们已经介绍了如何从命令行备份SQL Server数据库 ，如果要一次备份所有数据库怎么办？ 您可以创建一个批处理脚本，该脚本为每个数据库运行备份命令，但是每次添加或删除数据库时，都必须更新此脚本。 此外，数据库备份将全部附加到一个文件，该文件每次运行时都会随着新备份的大小而增长。 取而代之的是，我们将以一种真正的“设置并忘记它”的方式创建一个批处理脚本，该脚本将在添加和删除新数据库时适应您SQL Server。

To get right to the point, this is the backup script:

正确地说，这是备份脚本：

@ECHO OFF SETLOCAL

@ECHO OFF SETLOCAL

REM Get date in format YYYY-MM-DD (assumes the locale is the United States) FOR /F “tokens=1,2,3,4 delims=/ ” %%A IN (‘Date /T’) DO SET NowDate=%%D-%%B-%%C

REM以YYYY-MM-DD格式获取日期(假设语言环境是美国)FOR / F“令牌= 1,2,3,4 delims = /” %% A IN('Date / T')DO SET NowDate = %% D-%% B-%% C

REM Build a list of databases to backup SET DBList=%SystemDrive%SQLDBList.txt SqlCmd -E -S MyServer -h-1 -W -Q “SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN (‘master’,’model’,’msdb’,’tempdb’)” > “%DBList%”

REM建立要备份的数据库列表SET DBList =％SystemDrive％SQLDBList.txt SqlCmd -E -S MyServer -h-1 -W -Q 从master.dbo.sysDatabases中选择名称在[名称]不在的位置('master'，'model'，'msdb'，'tempdb')“>”％DBList％“

REM Backup each database, prepending the date to the filename FOR /F “tokens=*” %%I IN (%DBList%) DO ( ECHO Backing up database: %%I SqlCmd -E -S MyServer -Q “BACKUP DATABASE [%%I] TO Disk=’D:Backup%NowDate%_%%I.bak'” ECHO. )

REM备份每个数据库，在日期前添加文件名FOR / F“ tokens = *” %% I IN(％DBList％)DO(ECHO备份数据库：%% I SqlCmd -E -S MyServer -Q“ BACKUP DATABASE [ %% I] TO Disk ='D：Backup％NowDate％_ %% I.bak'” ECHO。)

REM Clean up the temp file IF EXIST “%DBList%” DEL /F /Q “%DBList%”

REM清理临时文件(如果存在)“％DBList％” DEL / F / Q“％DBList％”

ENDLOCAL

本地

Assuming the date is 1/13/2009 and you have 3 databases named ‘MyDB’, ‘AnotherDB’ and ‘DB Name with Spaces’, the script will produce 3 files in the backup location specified:

假设日期为2009年1月13日，并且您有3个名为“ MyDB”，“ AnotherDB”和“带空格的数据库名称”的数据库，则脚本将在指定的备份位置中生成3个文件：

• 2009-01-13_AnotherDB.bak
  2009-01-13_AnotherDB.bak
• 2009-01-13_DB Name with Spaces.bak
  2009-01-13_DB名称带有Spaces.bak
• 2009-01-13_MyDB.bak
  2009-01-13_MyDB.bak

自定义和运行批处理脚本 (Customizing and Running the Batch Script)

Of course, you will want to customize the script to your environment so here is what you need to do:

当然，您将需要根据您的环境自定义脚本，因此您需要执行以下操作：

• If your machine’s locale is not set to the US, the command ‘Date /T’ may not return the date in the format “Tue 01/13/2009”. If this is case, the NowDate variable will not produce the desired format and should be adjusted. (1 place)
  如果您的计算机的语言环境未设置为美国，则命令“ Date / T”可能不会以“ Tue 01/13/2009”的格式返回日期。 如果是这种情况，NowDate变量将不会产生所需的格式，应进行调整。 (1个地方)
• Change ‘MyServer’ to be the name of your SQL Server (add the instance name if applicable). (2 places)
  将“ MyServer”更改为您SQL Server的名称(如果适用，添加实例名称)。 (2个地方)
• The databases named ‘master’, ‘model’, ‘msdb’ and ‘tempdb’ are databases which ship with SQL Server. You can add additional database names to this list if you do not want them to be backed up. (1 place)
  名为“ master”，“ model”，“ msdb”和“ tempdb”的数据库是SQL Server附带的数据库。 如果您不希望备份其他数据库名称，则可以将其添加到此列表中。 (1个地方)
• Change the backup location from ‘D:Backup’ to the location where you want the database backup files stored.
  将备份位置从“ D：Backup”更改为要存储数据库备份文件的位置。

Once you have customized the batch script, schedule it to run via Windows Task Scheduler as a user with Administrator rights and you are all set.

自定义批处理脚本后，安排它以具有管理员权限的用户身份通过​​Windows Task Scheduler运行，并且一切就绪。

翻译自: https://www.howtogeek.com/50299/batch-script-to-backup-all-your-sql-server-databases/