☰

如何为 MSSQL 引擎上的每个数据库编写脚本备份？

tag-icon • tag-icon windows-server-2003 sql-server backup database
如何为 MSSQL 引擎上的每个数据库编写脚本备份？

我们需要备份 MS SQL Server Engine 中的 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

我们希望在脚本中添加获取每个数据库并替换上述脚本中的数据库的功能。基本上，该脚本将创建并验证来自引擎的每个数据库备份。

我正在寻找这样的东西：

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

有任何想法吗？

答案1

您可以使用未记录的数据库循环程序来执行此操作。

use [master]
go

set quoted_identifier off

exec sp_MSforeachdb "
if ( '?' not in ( 'master', 'msdb', 'model', 'tempdb' ) )
begin

BACKUP DATABASE [?] TO  DISK = N'J:\SQLBACKUPS\?.bak' WITH NOFORMAT, INIT,  NAME = N'?-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'?' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'?' )

if ( @backupSetId is null )
begin
    raiserror(N'Verify failed. Backup information for database ''?'' not found.', 16, 1)
end

RESTORE VERIFYONLY FROM  DISK = N'J:\SQLBACKUPS\?.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

end
"

答案2

就像是：

  1. 为“SELECT name FROM master..sysdatabases WHERE name NOT IN ('list','of','dbs','to','skip') 创建一个游标
  2. 在每个循环中迭代该操作：
    1. 将您的脚本构建为临时 SQL 字符串
    2. （让上述临时 SQL 将任何结果记录到某个小型日志数据库中）
    3. 使用 EXEC (@sql) 运行它
  3. 根据存储的结果输出一份简单的报告以进行健全性检查

应该可以解决问题。

市场上还有许多产品为 SQL Server 提供了非常灵活的备份选项，如果您的脚本变得更加复杂，这些产品可能更适合生产使用（尽管它们无疑并不便宜）。

答案3

我知道这不是对你的问题的直接回答，因为你想要一些与你的特定脚本相关的内容，但我认为我应该提出这个问题。

如果您拥有 MS SQL 的完整版本，而不是 MS SQL Express，则可以执行维护计划路线。以下是 Sunbelt Software 的 PDF，其中很好地说明了如何使用它，您可以在此处选择要备份的数据库：SQL-2005-维护计划.pdf

答案4

我会改进你的脚本：

:: copyonlybackup.bat
IF EXIST "%1_COPYONLY.BAK" (
  sqlcmd -U sa -P xxxxxxx -S SQL-DB -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY,INIT;"
echo Overwriting previous COPY_ONLY backup of %1 .
) ELSE (
  sqlcmd -U sa -P xxxxxxx -S SQL-DB -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY;"
  echo Creating new COPY_ONLY backup of %1 .
)

然后调用它

:: RUNSCRIPTS.bat
F:
CD "F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup"

@REM  Doing simple copies just in case
call COPYONLYBACKUP.bat db1
call COPYONLYBACKUP.bat db2
call COPYONLYBACKUP.bat db3
...

最后，如果您需要循环，请在我上面显示的 RUNSCRIPTS.bat 文件中执行此操作，使用类似下面的内容：

for /f %%a IN (myfile.txt) do call copyonlybackup.bat %%a

要生成 myfile.txt，您可能可以通过 SQL 以某种方式生成它：

EXEC sp_msForEachDB 'PRINT ''?'''

相关内容