是我使用光标时出现错误还是该脚本的其余部分存在问题?

发布于 2024-11-19 02:54:28 字数 948 浏览 6 评论 0原文

此代码在 SQL 2005 中运行良好,但在 SQL 2008 或 SQL 2008R2 中似乎丢失了 select 末尾的随机数量的记录。我使用此代码来备份生产服务器上的数据库。 2008 服务器上有 37 个数据库(不包括 tempdb),并且每天备份其中 17 到 35 个数据库(即使我运行 select,我总是返回 37 行)。它所在的作业完成时没有错误,但不会备份所有数据库。

DECLARE @today VARCHAR(10)
SELECT @today = Convert(varchar(10),dateadd(day,0,Dateadd(day,datediff(day,0,getdate()),0)),120)

DECLARE @DBName varchar(500)
DECLARE DB_Cursor CURSOR FOR
SELECT name FROM sys.databases 
OPEN DB_Cursor;
FETCH NEXT FROM DB_Cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
   BEGIN
IF @DBNAME <> 'tempdb'
BEGIN
    declare @Path varchar(500)              
    select @Path = 'g:\DBBackups\'

    declare @FileName varchar(4000)
    select @FileName = @Path + @DBNAME + '_Full_' + @today + '.bak'

        BACKUP DATABASE @DBName 
            TO DISK = @FileName
            WITH NoInit, NoFormat, SKIP
END

  FETCH NEXT FROM DB_Cursor INTO @DBNAME;
END;
CLOSE DB_Cursor;
DEALLOCATE DB_Cursor;

This code works fine in SQL 2005 but appears to miss a random number of records from the end of the select in SQL 2008 or SQL 2008R2. I use this code to backup databases on my production servers. The 2008 Server has 37 db's on it (not counting tempdb) and it backs up between 17 to 35 of those db's each day (even though if I run the select I always get 37 rows returned). The job it is in completes with no errors, but doesn't back up all of the databases.

DECLARE @today VARCHAR(10)
SELECT @today = Convert(varchar(10),dateadd(day,0,Dateadd(day,datediff(day,0,getdate()),0)),120)

DECLARE @DBName varchar(500)
DECLARE DB_Cursor CURSOR FOR
SELECT name FROM sys.databases 
OPEN DB_Cursor;
FETCH NEXT FROM DB_Cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
   BEGIN
IF @DBNAME <> 'tempdb'
BEGIN
    declare @Path varchar(500)              
    select @Path = 'g:\DBBackups\'

    declare @FileName varchar(4000)
    select @FileName = @Path + @DBNAME + '_Full_' + @today + '.bak'

        BACKUP DATABASE @DBName 
            TO DISK = @FileName
            WITH NoInit, NoFormat, SKIP
END

  FETCH NEXT FROM DB_Cursor INTO @DBNAME;
END;
CLOSE DB_Cursor;
DEALLOCATE DB_Cursor;

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

牵强ㄟ 2024-11-26 02:54:28

我从这里开始简要讨论了这一点:

基本上,将光标更改为使用 READ_ONLY LOCAL FORWARD_ONLY STATIC ,您不应该受到锁定或数据库更改的影响(这是我对实际解释的唯一疯狂猜测)。这是我在所有未发生神秘数据库跳过的情况下所能找到的唯一真正差异。

我没有尝试调查 @@FETCH_STATUS 的值 - 随着光标的前进,这可能会更改为 0 和 -1 以外的值(我通常检查后者,而不是前者)。因此,也许将 WHILE @@FETCH_STATUS = 0 更改为 WHILE @@FETCH_STATUS <> -1

I talked about this briefly starting here:

Basically, change the cursor to use READ_ONLY LOCAL FORWARD_ONLY STATIC and you shouldn't be affected by locks or changes to databases (which is the only wild stab guess I have for an actual explanation). This was the only true difference I could find in all the cases where the mysterious database skipping did not occur.

I did not attempt to investigate the values of @@FETCH_STATUS - it is possible that as the cursor progresses, this changes to values other than 0 and -1 (I usually check against the latter, not the former). So maybe change WHILE @@FETCH_STATUS = 0 to WHILE @@FETCH_STATUS <> -1.

愛放△進行李 2024-11-26 02:54:28

或者更好的是,根本不使用光标。 :)

该脚本存在严重缺陷。它不会检查数据库的状态,因此如果您有一个不在线的数据库(例如数据库镜像或辅助日志传送),则该过程将失败,并且不会备份该过程之后的所有数据库。另外,您使用了错误的数据类型

Or better yet, don't use a cursor at all. :)

The script is heavily flawed. It doesn't check the state of the database so if you have a database that is not online like a database mirror or log shipping secondary, the process will fail and all databases that come after it won't be backed up. Also, your using the wrong data types

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文