COPYTO Fails to DFS Namespace

MattSMattS Posts: 25
edited January 25, 2012 5:13AM in SQL Backup Previous Versions
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!
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • MattSMattS Posts: 25
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • MattSMattS Posts: 25
    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
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • MattSMattS Posts: 25
    It returns the following:
    <SQBUTILITYRESULT>:0:Folder does not exist : <network share>

    Thanks for your help.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • MattSMattS Posts: 25
    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.
    · Share on Twitter
Sign In or Register to comment.