How to backup linked servers with SQL Server?

sql-server linked-server database-backups database-restore
Steven Rojas picture Steven Rojas · Nov 15, 2012 · Viewed 12.1k times · Source

I want to backup/restore my data which is distributed in some linked servers, which are connected or linked in a central server. How can I do this?

I'm using SQL Server 2012 in all my nodes.

Answer

Leon Armstrong picture Leon Armstrong · Nov 15, 2012

you can use the remote machine to run sp_executesql command within the dynamic SQL.

exec server.master.dbo.sp_executesql 'BACKUP DATABASE DBNAME1 to disk='\\Server\Share\backupfilename.bak''

Or you can do this

exec server.master.dbo.sp_executesql 'BACKUP DATABASE [DBNAME1] to disk='\\Server\Share\backupfilename.bak''

Both line are almost identical , except the square bracket [] on the second syntax.

Related questions

SQL Server Linked Server Example Query

While in Management Studio, I am trying to run a query/do a join between two linked servers. Is this a correct syntax using linked db servers: select foo.id from databaseserver1.db1.table1 foo, databaseserver2.db1.table1 bar where …

Read More
How to select data of a table from another database in SQL Server?

Suppose that I have a database which name is testdb in test server. I also have a database named proddb in prod server. Now I want to select data of a table of testdb database from proddb database. How can …

Read More
How to find a text inside SQL Server procedures / triggers?

I have a linkedserver that will change. Some procedures call the linked server like this: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE. We have triggers also doing this kind of work. We need to find all places that uses [10.10.100.50] to change it. In SQL …

Read More