Sql-server – Backup database on linked server

backupsql serversql-server-2016

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??

Best Answer

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.

Related Question