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

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

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

Postby msalas » Wed Oct 06, 2010 5:41 am

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!
msalas
 
Posts: 14
Joined: Tue May 18, 2010 4:53 pm

Postby petey » Wed Oct 06, 2010 6:19 am

Run the following to find the wrong entry:

Code: Select all
EXEC master..sqbdata 'SELECT * FROM backupfiles_copylist'


Get the ID value, then run the following:

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby msalas » Wed Oct 06, 2010 4:43 pm

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!
msalas
 
Posts: 14
Joined: Tue May 18, 2010 4:53 pm

Postby msalas » Wed Oct 06, 2010 4:52 pm

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.
msalas
 
Posts: 14
Joined: Tue May 18, 2010 4:53 pm

Postby msalas » Wed Oct 06, 2010 5:02 pm

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.
msalas
 
Posts: 14
Joined: Tue May 18, 2010 4:53 pm

Postby msalas » Wed Oct 06, 2010 5:30 pm

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!
msalas
 
Posts: 14
Joined: Tue May 18, 2010 4:53 pm

Postby msalas » Wed Oct 06, 2010 6:59 pm

The email and attempts finally stopped/cancelled. Whew!

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

Thanks a lot!
msalas
 
Posts: 14
Joined: Tue May 18, 2010 4:53 pm

Postby petey » Thu Oct 07, 2010 4:54 am

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.

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests