Network Copy Location keeps trying to copy to the old path

Compresses, encrypts, secures and monitors SQL Server backups.

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

Network Copy Location keeps trying to copy to the old path

Postby aultmike » Tue Jul 16, 2013 6:41 pm

We were using the Network Copy Location feature to copy backed up databases and transaction logs from our sql server to a NAS. The NAS died the other day and I changed the Folder in which I wanted these backups copied to. I have verified in the job settings in the SQL Backup program and I even verified that the path had been changed in the underlying sql job in sql server. However I'm still getting error emails that its failing to copy to the now nonexistant NAS. That was yesterday. Today when I checked it seems like it is starting to copy to the new location.

Any idea why it seems like it took 24hrs to make this change? Is there a service I should restart to make the change instantaneous? What will that do to database and transaction log backups in progress? Is there a separate service that handles the copying of files off of the server?

Thanks
Mike
aultmike
 
Posts: 26
Joined: Fri Jan 08, 2010 3:49 pm
Location: Canton, OH

Postby petey » Wed Jul 17, 2013 3:14 am

When a transaction log is marked to be copied, it is placed in a queue, and the copying performed by a separate process, but still running within the SQL Backup Agent service. The details of the target location is stored at that point in time.

When you changed the target location, it does not update the details of the files already in the queue that are still waiting to be copied. That is why SQL Backup attempts to copy the files to the previous target location. After 24 hours, SQL Backup gives up trying to copy the files.

To address this, you will need to update the target location details manually. You can find details of the files in the backupfiles_copylist table, in the SQL Backup local database (a SQL Server Compact database). You can use the sqbdata extended stored procedure to view the contents e.g.

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

Those entries with a status of 'P' (Pending) means they have not been copied yet, and copyto indicates the target location. Assuming that your previous location was 'g:\\backups\\copies\\' and you now want to copy to 'h:\\sqlbackup\\copies\\', you can use the REPLACE function via sqbdata to perform the change e.g.

Code: Select all
sqbdata 'UPDATE backupfiles_copylist SET copyto = REPLACE(copyto, ''g:\\backups\\copies\\'', ''h:\\sqlbackup\\copies\\'') WHERE status = ''P'''
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 7

Who is online

Users browsing this forum: No registered users and 0 guests