Trouble backing up remote database from local server

Posted on

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

Leave a Reply

Your email address will not be published.