Red Gate forums :: View topic - Log Ship CopyTo function failed silently
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

Log Ship CopyTo function failed silently

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
shu lee



Joined: 02 Apr 2013
Posts: 9

PostPosted: Tue Apr 02, 2013 5:19 am    Post subject: Log Ship CopyTo function failed silently Reply with quote

I am trying to use SQL backup 6.4.0.56 to setup log shipping between 2 servers. The source server is running Windows 2008 R2 and SQL server 2005. The destination server is running Windows 2003 and SQL server 2005.

Using the SQL Backup GUI I setup the "Backup location" to a local folder on the source server and the "Network copy location" to a share on the destination server. I clicked on the 'Test' button under 'Network copy location' and the GUI displayed the "Server has the correct permissions" text. The destination share has all permissions available to 'Everyone'.

From the SQL Backup GUI on the source server, I goto the jobs tab and I right click on the log shipping job and 'start' it. The GUI reports a successful run and I verify 1) the tran log was created on the local folder, 2) the tran log WAS NOT copied to the destination folder.

Viewing the history of the backup log ship job from SSMS indicates the log ship backup job completed successfully. Looking at the SQL Backup log, it also indicates the job completed successfully.

Here's the backup tsql generated by the SQL Backup GUI -

EXECUTE master..sqlbackup '-SQL "BACKUP LOG [TestLogShip] TO DISK = ''E:\sqlserver2005\Backups\LogsForLogShip\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH ERASEFILES = 14, ERASEFILES_REMOTE = 14, FILEOPTIONS = 4, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COPYTO = ''\\192.168.100.215\LogsForLogShipSlave'', THREADCOUNT = 7"', @errorcode OUT, @sqlerrorcode OUT;

I also ran this with SSMS and got no errors.

Any ideas why the tran log was not copied to the destination share?

Thanks,

-Shu
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Wed Apr 03, 2013 5:28 am    Post subject: Reply with quote

Could you please run the following in SSMS:

Code:
EXEC master..sqbdata 'SELECT a.name, b.copystart, b.copyend, b.message
FROM backupfiles_copylist a
INNER JOIN backupfiles_copylist_log b ON a.ID = b.copylist_id
INNER JOIN backuphistory c ON a.backup_id = c.id
WHERE c.dbname = 'TestLogShip''
AND b.message IS NOT NULL
ORDER BY a.ID DESC, b.attempt DESC'


and see if it provides any useful information?
_________________
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
shu lee



Joined: 02 Apr 2013
Posts: 9

PostPosted: Thu Apr 04, 2013 3:32 am    Post subject: Reply with quote

Thanks Peter.

Here are the top 3 records returned from that query:

name copystart copyend message
E:\sqlserver2005\Backups\LogsForLogShip\LOG_TestLogShip_20130402111500.sqb 2013-04-03 10:54:53.000 2013-04-03 10:54:53.000
E:\sqlserver2005\Backups\LogsForLogShip\LOG_TestLogShip_20130402110000.sqb 2013-04-03 10:54:53.000 2013-04-03 10:54:53.000
E:\sqlserver2005\Backups\LogsForLogShip\LOG_TestLogShip_20130402101500.sqb 2013-04-03 09:57:34.000 2013-04-03 09:57:34.000

The message column is empty.

Ok. This might sound odd, but it appears to me the file copying occurs almost 24 hour later. Looking at the 1st record above, note from the 1st filename that the log-create datetime is 2013-04-02-11:15:00. Then note that file's copyStart time is 2013-04-03 10:54:53. Does that sound right?

Thanks,

-Shu
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Thu Apr 04, 2013 3:38 am    Post subject: Reply with quote

Could you please post the results of running the following:

Code:
EXEC master..sqbdata 'SELECT a.name, b.attempt, b.copystart, b.copyend, b.message
FROM backupfiles_copylist a
INNER JOIN backupfiles_copylist_log b ON a.ID = b.copylist_id
INNER JOIN backuphistory c ON a.backup_id = c.id
WHERE a.name = ''E:\sqlserver2005\Backups\LogsForLogShip\LOG_TestLogShip_20130402111500.sqb''
ORDER BY b.attempt DESC'


I would like to see how many attempts had been made to copy that file, and from when the attempts started.

Thanks.
_________________
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
shu lee



Joined: 02 Apr 2013
Posts: 9

PostPosted: Thu Apr 04, 2013 3:45 am    Post subject: Reply with quote

Thanks Peter.

Here's the result of your query:

E:\sqlserver2005\Backups\LogsForLogShip\LOG_TestLogShip_20130402111500.sqb 1 2013-04-03 10:54:53.000 2013-04-03 10:54:53.000

Seems like a normal 1 attempt.

How much of the file copying depends on msdb? Either before or after the copy?

-Shu
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Thu Apr 04, 2013 3:54 am    Post subject: Reply with quote

Could you please run the following?

Code:
EXEC master..sqbdata 'SELECT a.name, b.attempt, b.copystart, b.copyend, b.message
FROM backupfiles_copylist a
INNER JOIN backupfiles_copylist_log b ON a.ID = b.copylist_id
WHERE b.copystart >= ''2013-04-02 11:15:00''
  AND b.copyend <= ''2013-04-03 10:54:00''
ORDER BY a.name, b.attempt'

Were there a lot of copying activity going on during the specified interval?

Quote:
How much of the file copying depends on msdb? Either before or after the copy?

None. Once the backup completes, the file name is placed in the backupfiles_copylist table, and is queued for copying.
_________________
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
shu lee



Joined: 02 Apr 2013
Posts: 9

PostPosted: Thu Apr 04, 2013 4:05 am    Post subject: Reply with quote

There should be no other activities between these 2 servers at the time of the log copying. I should add that for a sanity check, at one point I added a 2nd step to the log shipping job that calls robocopy to copy the logs and that copies the logs to the secondary server without issues. One issue with robocopy is I haven't figured out how to only copy the logs that are new.

I ask about the msdb because my last DBA neglected to setup a job to clean up msdb and as a result the msdb has entries going back to 2011. Because of that, it could take a long time to delete entries out of msdb.

-Shu
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Thu Apr 04, 2013 4:25 am    Post subject: Reply with quote

What I meant was if you ran the following script:

Code:
EXEC master..sqbdata 'SELECT a.name, b.attempt, b.copystart, b.copyend, b.message
FROM backupfiles_copylist a
INNER JOIN backupfiles_copylist_log b ON a.ID = b.copylist_id
WHERE b.copystart >= ''2013-04-02 11:15:00''
  AND b.copyend <= ''2013-04-03 10:54:00''
ORDER BY a.name, b.attempt'

are there a lot of entries returned?

Quote:
I ask about the msdb because my last DBA neglected to setup a job to clean up msdb and as a result the msdb has entries going back to 2011. Because of that, it could take a long time to delete entries out of msdb.

The copying task is independent of the msdb deletion task. Is SQL Backup set up to delete old entries from msdb now?
_________________
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
shu lee



Joined: 02 Apr 2013
Posts: 9

PostPosted: Thu Apr 04, 2013 4:44 am    Post subject: Reply with quote

Oops. My bad. I totally missed your query at the top of the last post. I ran the query just now and there are over 6000 records returned. Most of the records are copying tran logs of the 'real' databases to a network drive. The only traffic between the 2 servers are the copying of the TestLogShip tran logs.

SQL Backup is not currently setup to delete the old entries from msdb.

-Shu
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Thu Apr 04, 2013 4:53 am    Post subject: Reply with quote

SQL Backup gives priority to older files when copying files. Were the files that were being copied over during that interval older than the transaction log backup file for TestLogShip created on 2 April 2013 11:15 AM?
_________________
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
shu lee



Joined: 02 Apr 2013
Posts: 9

PostPosted: Thu Apr 04, 2013 5:08 am    Post subject: Reply with quote

Possibly. The other tran logs are taken every 30 minutes and there should be about 40 of them every 30 minutes. These tran logs are compressed so they aren't that big that they take that long to copy. The 1 tran log for TestLogShip are taken every 15 minutes.

-Shu
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Thu Apr 04, 2013 7:08 am    Post subject: Reply with quote

Looking at the results of the last query you ran, were the files that were being copied during that interval all older than the backup file you created and expected to be copied at that time (E:\sqlserver2005\Backups\LogsForLogShip\LOG_TestLogShip_20130402111500.sqb)?
_________________
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
shu lee



Joined: 02 Apr 2013
Posts: 9

PostPosted: Fri Apr 05, 2013 2:50 am    Post subject: Reply with quote

No, not all files are older than the expected log ship backup file. But I did notice that all my other files exhibit the same delay behavior, meaning the other tran logs are also getting copied almost a day later. I am also seeing some 'COPYTO error:' messages with some of the records returned from this query.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Fri Apr 05, 2013 6:01 am    Post subject: Reply with quote

If you run this query now:

Code:
EXEC master..sqbdata 'SELECT COUNT(*) FROM backupfiles_copylist WHERE status = ''P'''


what is the result?
_________________
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
shu lee



Joined: 02 Apr 2013
Posts: 9

PostPosted: Fri Apr 05, 2013 6:29 am    Post subject: Reply with quote

2392
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
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