Question :
I have 2 servers LOCAL and REMOTE. REMOTE has a database ‘remotedb’.
From REMOTE the following statement backs up the database just fine.
BACKUP DATABASE [remotedb] TO DISK = N'C:Tempremotedb.bak'
Now I want to issue the backup from LOCAL. From LOCAL I create a linked server to REMOTE.
exec sp_addlinkedserver 'REMOTE'
From LOCAL I issue the same statement…
BACKUP DATABASE [remotedb] TO DISK = N'C:Tempremotedb.bak'
I get the following error message…
Msg 911, Level 16, State 11, Line 3
Database 'remotedb' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
I am SYSADMIN on both SQL Servers. Can someone tell me why I am not able to issue a backup command from LOCAL for remotedb and what can I do to make this work?
Much thanks!
Answer :
As is the command is trying to execute on your LOCAL instance and not REMOTE. You need to have it execute through the linked server itself. You can build the query as a dynamic statement and then simply execute the query against sp_executesql
on the linked server. So something like this should:
DECLARE @qry varchar(2000)
SET @qry = 'BACKUP DATABASE [remotedb] TO DISK = N''C:Tempremotedb.bak'''
EXEC REMOTE.master.dbo.sp_executesql @qry