Red Gate forums :: View topic - SQL Log backups with COPYTO (How to Stop the retries)
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

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

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
msalas



Joined: 18 May 2010
Posts: 14

PostPosted: Wed Oct 06, 2010 5:41 am    Post subject: SQL Log backups with COPYTO (How to Stop the retries) Reply with quote

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!
Back to top
View user's profile Send private message Yahoo Messenger
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Wed Oct 06, 2010 6:19 am    Post subject: Reply with quote

Run the following to find the wrong entry:

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


Get the ID value, then run the following:

Code:
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
Back to top
View user's profile Send private message Send e-mail
msalas



Joined: 18 May 2010
Posts: 14

PostPosted: Wed Oct 06, 2010 4:43 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message Yahoo Messenger
msalas



Joined: 18 May 2010
Posts: 14

PostPosted: Wed Oct 06, 2010 4:52 pm    Post subject: Reply with quote

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)

Sad Please help! I hope it stop retrying soon.
Back to top
View user's profile Send private message Yahoo Messenger
msalas



Joined: 18 May 2010
Posts: 14

PostPosted: Wed Oct 06, 2010 5:02 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Yahoo Messenger
msalas



Joined: 18 May 2010
Posts: 14

PostPosted: Wed Oct 06, 2010 5:30 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message Yahoo Messenger
msalas



Joined: 18 May 2010
Posts: 14

PostPosted: Wed Oct 06, 2010 6:59 pm    Post subject: Reply with quote

The email and attempts finally stopped/cancelled. Whew!

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

Thanks a lot!
Back to top
View user's profile Send private message Yahoo Messenger
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Thu Oct 07, 2010 4:54 am    Post subject: Reply with quote

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:
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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group