COPYTO Fails to DFS Namespace

Compresses, encrypts, secures and monitors SQL Server backups.

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

COPYTO Fails to DFS Namespace

Postby MattS » Tue Jan 24, 2012 1:47 pm

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!
MattS
 
Posts: 18
Joined: Tue Sep 20, 2011 12:35 pm

Postby petey » Tue Jan 24, 2012 4:19 pm

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.

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby MattS » Tue Jan 24, 2012 4:38 pm

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.
MattS
 
Posts: 18
Joined: Tue Sep 20, 2011 12:35 pm

Postby petey » Tue Jan 24, 2012 4:51 pm

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.

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby MattS » Tue Jan 24, 2012 5:04 pm

It errors after 5m30s:

Code: Select all
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
MattS
 
Posts: 18
Joined: Tue Sep 20, 2011 12:35 pm

Postby petey » Tue Jan 24, 2012 5:41 pm

Could you please run the following and post the results:

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby MattS » Tue Jan 24, 2012 5:53 pm

It returns the following:

Code: Select all
<SQBUTILITYRESULT>:0:Folder does not exist : <network share>


Thanks for your help.
MattS
 
Posts: 18
Joined: Tue Sep 20, 2011 12:35 pm

Postby petey » Tue Jan 24, 2012 6:29 pm

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Update

Postby MattS » Wed Jan 25, 2012 11:13 am

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.
MattS
 
Posts: 18
Joined: Tue Sep 20, 2011 12:35 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: Yahoo [Bot] and 0 guests