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