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.

The @ServerName in your exec(@sql) at ... cannot be a variable. You might be able to run your command with openquery. http://stackoverflow.com/questions/3408505/dynamic-linked-server-query — InbetweenWeekends, Jun 23 '15 at 19:00
One restriction to the use of OPENQUERY is that it does not accept variables for either parameter. — NF22, Jun 23 '15 at 20:41
But it would allow the construct of a dynamic SQL statement as seen in the accepted answer on my link. `exec ('select * from openquery(' + @linkedServer + ', ''select c1,c2 from t1 where p1 = '' + @var1 + ''and p2= '' + @var2 + ''')` — InbetweenWeekends, Jun 23 '15 at 20:42
You could also use `sp_executesql`, that would be much cleaner, and supports variables. — Greg, Jun 23 '15 at 22:21

Vali Maties · Answer 1 · 2020-02-27T08:43:42.257

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)

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

1 Answers1