SQL Log backups with COPYTO (How to Stop the retries)

msalasmsalas Posts: 19
edited October 6, 2010 11:54PM in SQL Backup Previous Versions
I have executed a script for log backups without a DISKRETRYCOUNT, with a COPYTO section. However, the path that was given was incorrect and the script kept on retrying and retrying. How can I stop this from retrying.

Please help, the email has been sending numerous times already and I really hope that it stops soon.

Thanks in advance!
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Run the following to find the wrong entry:
    EXEC master..sqbdata 'SELECT * FROM backupfiles_copylist'

    Get the ID value, then run the following:
    EXEC master..sqbdata 'UPDATE backupfiles_copylist SET status = ''C'' WHERE ID = <the above ID value>'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • msalasmsalas Posts: 19
    Thanks a lot Peter... I am just soooo glad to know that there is a way out of this.

    I'm doing the first query for now. I tried passing in some filter for the SELECT query because it looks like its listing out things that has copyto clause from the beginning that redgate backup was used.

    Is there a way to apply filter to this statement? Like filter by COPYTO (I can see the wrong path from the hundreds of email that I have been receiving.

    How many retry attempt will this do?

    Thanks a lot!
    · Share on Twitter
  • msalasmsalas Posts: 19
    Okay, I finally saw the ID's that i needed to apply the second script on:.
    The IDs are as follows (I just listed it here also so I hope I won't need to run the first script)

    356714, 356716, 356718, 356720, 356722, 356724, 356726, 356728, 356730, 356732, 356734, 356736, 356738, 356740, 356742

    When I run the script:
    EXEC master..sqbdata 'UPDATE backupfiles_copylist SET status = ''C'' WHERE ID = 356716'

    Result is: (0 row(s) affected)

    :( Please help! I hope it stop retrying soon.
    · Share on Twitter
  • msalasmsalas Posts: 19
    Okay,
    I finally was able to script out the SELECT with the correct filter (I probably have written it wrong earlier).
    Now that I was able to, although there were (0) affected earlier during the UPDATE, i can see that the status has now been changed/changing to "C"... looks like there may just be a delay with affecting the records. Now there's only few incorrect records that doesn't have "C" which I will view as "Cancel", the rest are in "A" (Attempt?) Hopefully soon they will become "C".

    Will keep you posted.
    · Share on Twitter
  • msalasmsalas Posts: 19
    Hmmm... there are 5 records that even though they were already showing a status of "C", earlier, after a while it goes back to "A" and the count increases.
    Any idea on how to really stop those, now it's down to 5 records (hopefully the others doesn't come back to life.

    Out of curiosity, there are subsequent copies (from job) that were not copied over, and they have a status of "P"... what does that mean? "Pending?" When will they be fulfilled?

    Is there a documentation regarding this that I can read on?

    Thanks a lot!
    · Share on Twitter
  • msalasmsalas Posts: 19
    The email and attempts finally stopped/cancelled. Whew!

    Is it possible to have a link to the documentation for sqbdata?

    Thanks a lot!
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    With regards to sqbdata:

    SQL Backup stores details of its processes in a SQL Server Compact database. Default location is 'C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\<instance name>' on Windows 2003 and older, and 'C:\ProgramData\Red Gate\SQL Backup\Data\<instance name>' on Windows Vista and newer. You can open this database using SQL Server Management Studio (select the 'SQL Server Mobile' server type).

    sqbdata is a SQL Backup extended stored procedure that allows you to run commands against the above SQL Server Compact database. You can run any valid SQL Server Compact command using it e.g.
    EXEC master..sqbdata 'SELECT * FROM INFORMATION_SCHEMA.TABLES'

    However, you should ignore the 'rows affected' values for DDL commands, as that will not reflect the actual no. of rows inserted/updated/deleted.


    With regards to the backupfiles_copylist table:

    This is a table in the above-mentioned database that stores details of the files that need to be copied to one or more directories. Possible 'status' column values are as follows:

    A = active. SQL Backup is currently attempting to copy the file.
    S = successful. SQL Backup has copied the file successfully.
    P = pending. The file is waiting to be copied.
    C = cancelled. The file will not be copied. This happens when the local source file no longer exists.
    E = expired. SQL Backup has hit the limit for the maximum number of tries to copy the file and will no longer attempt to copy the file.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.