How to back up a linked server database? 1

[andreis]

Hi, I try to set up a job that would back up databases of several SQL 2000 servers onto one db storage. I'd like to run this job from one place. I linked the servers. But backup database command:
BACKUP database LinkedServerName.DatabaseName TO DISK = 'mypath'
fails.
How can I do backing up multiple databases from different servers without creating DTS package/BusinessIntelligence project?

 

[mrdenny]

You would need to run the sp_executesql command on the remote machine with the backup database command within the dynamic SQL. Something like...

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

Or better yet, back them all up...

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

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog