SQL Server：是否可以防止 SQL 代理因错误而失败？
我有一个存储过程，可以为大约 60 个 SQL 服务器（混合 2000 到 2008R2）运行自定义备份。
有时，由于我无法控制的问题（备份设备无法访问、网络错误等），一两个数据库上的单独备份会失败。这会导致整个步骤失败，这意味着不会执行任何后续备份命令，并且给定服务器上的一半数据库可能不会备份。
在 2005 年以上的机器上，我使用 TRY/CATCH 块来管理这些问题并继续备份剩余的数据库。然而，例如，在 2000 服务器上，我无法阻止此错误导致整个步骤失败：
Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'db-diff(\PATH\DB-DIFF- 03-16-2010.DIF)'。操作系统错误 5（访问被拒绝。）。消息 3013，级别 16，状态 1，第 1 行备份数据库异常终止。
我只是想问在 SQL 2000 中是否可以使用 TRY/CATCH 之类的功能？我意识到没有内置的方法，所以我想我正在寻找一些创造力。
即使通过 sp_executesql 包装每个备份（或任何失败的语句），作业也会立即失败。例子：
DECLARE @x INT,
@iReturn INT
PRINT 'Executing statement that will fail with 208.'
EXEC @iReturn = Sp_executesql N'SELECT * from TABLETHATDOESNTEXIST;'
PRINT Cast(@iReturn AS NVARCHAR) --In SSMS this return code prints. Executed as a job it fails and aborts before this statement.
I have a stored procedure that runs custom backups for around 60 SQL servers (mixes 2000 through 2008R2).
Occasionally, due to issues outside of my control (backup device inaccessible, network error, etc.) an individual backup on one or two databases will fail. This causes this entire step to fail, which means any subsequent backup commands are not executed and half of the databases on a given server may not be backed up.
On the 2005+ boxes I am using TRY/CATCH blocks to manage these problems and continue backing up the remaining databases. On a 2000 server however, for example, I have no way to prevent this error from failing the entire step:
Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'db-diff(\PATH\DB-DIFF-03-16-2010.DIF)'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
I am simply asking if anything like TRY/CATCH is possible in SQL 2000? I realize there are no built in methods for this, so I guess I am looking for some creativity.
Even when wrapping each backup (or any failing statement) via sp_executesql the job fails instantly. Example:
DECLARE @x INT,
@iReturn INT
PRINT 'Executing statement that will fail with 208.'
EXEC @iReturn = Sp_executesql N'SELECT * from TABLETHATDOESNTEXIST;'
PRINT Cast(@iReturn AS NVARCHAR) --In SSMS this return code prints. Executed as a job it fails and aborts before this statement.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我通过为每个数据库重新创建作业步骤来解决这个问题，而不是迭代数据库的一个作业。
也就是说，确保在备份开始时每个数据库都有一个作业步骤。然后继续失败。
I got around this by recreating job steps for each database, rather then one job that iterates through DBs.
That is, ensure you have a job step for each DB at the start of the backup. Then continue on fail.
简短的回答：不。
您还可以采取其他措施来尝试先发制人地避免该错误吗？检查连接/交易等。您知道为什么它被认为是“访问被拒绝”吗（我假设它“正在使用”）。
基本过程可以分成多个段，每个 db 1 个段吗？即通过批处理文件或其他根文件多次调用同一过程？
您能否将其提升一个级别，以便在另一个管理应用程序或层中进行调用？
使用 RedGates SQL Backup 这样的第 3 方工具会更好吗？随着时间的推移，这种工具的前期费用可能不仅仅可以节省成本支持。
http://www.red-gate.com/products/SQL_Backup/index.html嗯
Short answer: No.
Is there anything else you can do to try to preemptively avoid the the error? Check for connections/transactions etc. Do you know why it's considered Access is Denied (I'm assuming it's "in use").
Can the base proc be split into multiple segments of 1 per db? i.e. same proc called multiple times via a batch file or other root file?
Can you move it up a level to shell the call in another managing app or layer?
Would it be better to use a 3rd party tool like RedGates SQL Backup? The upfront expense of such a tool may more than save the cost support over time.
http://www.red-gate.com/products/SQL_Backup/index.htm
如果启用了 XP_CmdShell，则可以将其用于 SQL2000 系统。您将失去一些错误处理能力，并且必须在捕获日志文件的输入方面发挥创意。
If you have XP_CmdShell enabled, then you can use that for SQL2000 systems. You will lose some error handling, and will have to get creative around capturing input for log files.