COPYTO Fails to DFS Namespace

MattS

We currently have backups running and successfully copying to a network share. We want to move the network share to a new DFS namespace, but SQL backup does not seem to recognise the new share.

I have tried amending via the GUI, but when I change the network copy location to the new DFS location it returns an error message saying the folder does not exist.

I have tried running a backup via SSMS using master..sqlbackup and setting the COPYTO setting to the new DFS location. This generates numerous COPYTO errors, including "COPYTO error: Failed to create folder...".

My initial thoughts were that this is obviously permissions related. However, I have spun up a test SQL install and configured the cmdshell proxy account as the same domain account that the SQL Backup service is running under. On the test install I am able to use xp_cmdshell to write to the DFS location, thereby proving that the service account does have the correct permissions.

Does SQL Backup work correctly with DFS Namespaces? Can anybody help identify the problem?

Thanks in advance for any assistance!

petey

Would it be possible to set up your test install to have the SQL Server service start up using the same account as the SQL Backup Agent service, and see if you can back up natively using SQL Server to that network share e.g.

BACKUP DATABASE model TO DISK = '<your network share\model.bak'

Thanks.

MattS

Hi pete.

That backs up successfully.

To confirm, the test install was running under the local system account and attempting to backup to the new location failed. Changed the SQL Server service to run under the same domain account that the SQL Backup Agent Service runs under, and the backup succeeds.

Thanks for your help.

petey

What is the output from SQL Backup when you try to back up to that exact same folder using an explicit name on that test server e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DISK = [<network share>\model.sqb]" '

Thanks.

MattS

It errors after 5m30s:

Backing up model (full database) to: 
  <network share>\model.test.bak
 
Thread 0 error: 
Error 605: Error creating backup file(s).  No files could be created.
Warning 200: Thread 0 warning: 
24/01/2012 15:54:52: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:55:22: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:55:52: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:56:22: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:56:52: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:57:22: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:57:52: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:58:22: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:58:52: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:59:22: Warning 480: Failed to create output folder: <network share>.
24/01/2012 15:59:53: Warning 480: Failed to create output folder: <network share>.
Warning 480: Failed to create output folder: <network share>.
 
SQL error 3013: SQL error 3013: BACKUP DATABASE is terminating abnormally.
SQL error 3271: SQL error 3271: A nonrecoverable I/O error occurred on file "SQLBACKUP_8E056DA2-D1A6-4A48-8D4D-0FB18BE894B9:" 995(failed to retrieve text for this error. Reason: 15105).
SQL error 3202: SQL error 3202: Write on "SQLBACKUP_8E056DA2-D1A6-4A48-8D4D-0FB18BE894B9" failed: 995(failed to retrieve text for this error. Reason: 15100)
 
SQL Backup exit code: 790
SQL error code: 3202

petey

Could you please run the following and post the results:

EXEC master..sqbutility 999, 'RWE', '\\<network share folder>\

This function simulates creating a file and deleting it on the network share, and reports if sufficient rights exist to perform the tasks.

Thanks.

MattS

It returns the following:

<SQBUTILITYRESULT>:0:Folder does not exist : <network share>

Thanks for your help.

petey

If you grant the 'Everyone' group rights to the network share, is SQL Backup able to back up to the network share?

If you use SQL Backup to create a backup file to another network share successfully, is the owner/creator of the file the same user account as the SQL Backup Agent service startup account?

Thanks.

MattS

It's now working!

Simply restarting the SQL Backup Agent service seems to have done the trick.

One of the factors behind the move from the current network share to the new DFS namespace is the new DR solution we are implementing. Part of this solution includes log shipping, which we are using SQL Backup for.

Before approaching the network guys about granting 'EVERYONE' permissions on the new share, I tried the same tests we did before (backup msdb to the DFS namespace/EXEC sqbutility) on the DR server and they worked. The SQL Backup service on the DR server is running under the same domain account as the server we've had the issues with, so this again confirmed it was not a permissions issue. As a final test I thought it may be worth restarting the SQL Backup service, and hey presto everything is working now :-)

Thanks for your assistance Pete. Greatly appreciated.