如何构建一个脚本来备份SQL Server中的所有数据库？

We need to backup 40 databases inside a SQL Server instance. We backup each database with the following script:

我们需要在SQL Server实例中备份40个数据库。我们使用以下脚本备份每个数据库：

BACKUP DATABASE [dbname1] TO  DISK = N'J:\SQLBACKUPS\dbname1.bak' WITH NOFORMAT, INIT,  NAME = N'dbname1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'dbname1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'dbname1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''dbname1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'J:\SQLBACKUPS\dbname1.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

We will like to add to the script the functionality of taking each database and replacing it in the above script. Basically a script that will create and verify each database backup from an engine.

我们想在脚本中添加获取每个数据库并在上面的脚本中替换它的功能。基本上是一个脚本，它将从引擎创建和验证每个数据库备份。

I am looking for something like this:

我正在寻找这样的东西：

For each database in database-list
    sp_backup(database) // this is the call to the script above.
End For

any ideas?

有任何想法吗？

5 个解决方案

#1

Use SqlBackupAndFTP. Select "Backup all non-system databases"

使用SqlBackupAndFTP。选择“备份所有非系统数据库”

#2

Maybe you can check the sp_MSForEachDB stored procedure.

也许你可以检查sp_MSForEachDB存储过程。

EXEC sp_MSForEachDB 'EXEC sp_backup ?'

Where the ? mark stands for the current database on each iteration.

在哪里？ mark代表每次迭代的当前数据库。

#3

You said "verify" too... This would require a 2nd statement per database (RESTORE VERIFYONLY, as well the other answers offered.

你说过“验证”......这需要每个数据库有第二条语句（RESTORE VERIFYONLY，以及其他提供的答案）。

I'd investigate using maintenance plans to include index maintenance too...

我会调查使用维护计划来包括索引维护......

#4

Something like:

就像是：

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'BACKUP ' + Name + ' To Disk=''c:\' + Name + '.bak'';'
    FROM sys.sysdatabases

EXEC(@SQL)

#5

SQL Backup Master has the ability to back up all databases on a given server instance and then send the backups to Dropbox, FTP, Amazon S3, Google Drive, or local/network folder.

SQL备份主机能够备份给定服务器实例上的所有数据库，然后将备份发送到Dropbox，FTP，Amazon S3，Google Drive或本地/网络文件夹。

#1