Stored procedure taking Backup of data files stored on a linked server

Question

I have a stored procedure which will be taking backups of data files stored on linked servers on the same linked server. The linked server name is retrieved from a table for each company. So the linked server name is saved in a variable. So in my stored procedure, I run the following backup Database command:

SET @Sql='BACKUP DATABASE  [' + @datafileID  + '] TO DISK = ''' + @fullPath + ''' WITH INIT, COMPRESSION;'
EXEC (@Sql) AT [@ServerName]

@datafileID is the data file name

@fullPath has the path to the backup folder on the linked server

@ServerName has the linked server name

When the stored procedure is run, I get the following error:

Could not find server '@ServerName' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

The linked server is created earlier in the code

IF NOT EXISTS(SELECT * FROM sys.servers where name = @ServerName  )
SET @Sql = 'sp_addlinkedserver @server = '' ' + @ServerName + ''''
EXECUTE( @SQL)

How do I pass the server name as a variable in the Backup Database command.

Answer 1

Compound that @Sql variable with another one which stores Exec AT command like this:

declare @ServerName varchar(20), @Sql varchar(max),@datafileID varchar(100),@FullPath varchar(150), 
@cmd varchar(max)

set @ServerName = 'YourLinkedServerName'
set @datafileID = 'YourDatabaseName'
set @FullPath = 'YourFullPath\Backups.bak' 

SET @Sql='''BACKUP DATABASE  [' + @datafileID  + '] TO DISK = ''''' + @fullPath + ''''' With INIT, COMPRESSION;'''
set @cmd = 'EXEC (' + @Sql + ') AT [' + @ServerName + ']'

--print @cmd
-- will display: 
-- EXEC ('BACKUP DATABASE  [YourDatabaseName] TO DISK = ''YourFullPath\Backups.bak'' With INIT, COMPRESSION') AT [YourLinkedServerName]

exec (@cmd)