0

I am trying to backup a db on ServerA via ServerB.

I have:

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'BACKUP DATABASE DBname to disk=''\\ServerB\E$\folder\DBname.bak'''
EXECUTE ServerA.master.dbo.sp_executesql @SQL

I execute this on ServerB but I get error:

Msg 3013, Level 16, State 1, Line 9 BACKUP DATABASE is terminating abnormally. Msg 3201, Level 16, State 1, Line 9 Cannot open backup device '\ServerB\E$\folder\DBname.bak'. Operating system error 5(Access is denied.).

I have given NT SERVICE\MSSQLSERVER full control to the folder but that didn't work.

Is what I am trying to do possible? What am I doing wrong?

EDIT

I have also tried:

EXEC ServerA.master.dbo.sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

EXEC ServerA.master.dbo.sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

EXEC ServerA.master.dbo.XP_CMDSHELL 'net use G: \\ServerB\E$\folder /user:user password'
EXEC ServerA.master.dbo.XP_CMDSHELL 'Dir G:'
GO 
 
DECLARE @SQL NVARCHAR(100)
SET @SQL = 'BACKUP DATABASE DBname to disk=''G:/DBname.bak'' WITH INIT, FORMAT, COPY_ONLY'
EXECUTE ServerA.master.dbo.sp_executesql @SQL
GO

EXEC ServerA.master.dbo.XP_CMDSHELL 'net use G: /delete'
GO

EXEC ServerA.master.dbo.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC ServerA.master.dbo.sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

It reports it has enabled xp_cmdshell etc but when I run the mapping part it errors saying it's off. If I enable xp_cmdshell directly on ServerA the mapping part works via ServerB??

  • How local server login in linked server? – McNets Jun 15 at 14:15
  • if you execute the backup command directly on serverA, does it work? also, i wouldnt use admin share (E$). try creating share on serverb, and give the service account running sql server on servera permissions to share and folder. NT SERVICE\MSSQLSERVER will be the account on serverb, servera does not have access – Bob Klimes Jun 15 at 14:18
  • ServerA is set up as a linked server on ServerB and connects ok. – Round Jun 15 at 14:27
  • 1
    @round I meant take linkled server out of equation and run BACKUP DATABASE DBname to disk='\\ServerB\E$\folder\DBname.bak' on servera. does this work? And does the service account running sql server on servera have access to share? – Bob Klimes Jun 15 at 14:56
2

You wrote that "I have given NT SERVICE\MSSQLSERVER full control", so it looks like your SQL Server is running under "local system accounts".
If you granted full control to NT SERVICE\MSSQLSERVER on a second server, it will not work, because NT SERVICE\MSSQLSERVER is a different account on each machine.
You need to grant all network privileges to account [DomainName]\[ServerName]$ for a local service accounts to have access to network resources.
In your case it will be [DomainName]\ServerA$ account and this account will need to have access to the network share.

| improve this answer | |
  • Perfect. Now working. Many thanks. – Round Jun 16 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.