Network Copy Location keeps trying to copy to the old path

aultmike

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

petey

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.

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.

sqbdata 'UPDATE backupfiles_copylist SET copyto = REPLACE(copyto, ''g:\backups\copies\'', ''h:\sqlbackup\copies\'') WHERE status = ''P'''