Backup/restore using OPENDATASOURCE OR OPENROWSET

Question

Has anyone ever successfully taken a backup or restore to another server using OPENDATASOURCE/OPENROWSET? I am not able to use linked servers so I am exploring this idea.

• Aaron Bertrand: Why do you need to take the backup from T-SQL, instead of from that server's SQL Agent or some external process? If you explain your requirements and constraints better (and maybe why linked servers are not allowed), it is more likely you'll get a better answer than "yes, I tried it, but it didn't work."
• Kin Shah: Dont abuse Linked servers for taking backups and doing restores. There are other methods available which fits the purpose of what you are looking.
• Alf47: I believe OPENROWSET/OPENDATASOURCE only support DML (SELECT,INSERT,UPDATE,DELETE) and Bulk insert: msdn.microsoft.com/en-us/library/ms190312.aspx

Answer 1

You can automate all of below using SQL Agent.

There are many alternatives that you can use :

1. SQLCMD ==> sqlcmd -E -S server_name -q "backup command" You can use it with dynamic sql to connect to different servers for backup and restores.
2. PowerShell ==> There are tons of scripts found on internet that will tell you how to do it. SimpleTalk has -- Backup and Restore SQL Server with the SQL Server 2012 PowerShell
3. SSIS ==> Automate Database Restore to Remote Instance with SSIS

When doing backup - make sure to use compression in sql server 2008 R2 and up which is even supported in standard edition. Also to speed up restore, use Instant File Initialization.

• Ariyel Carver: So the reason for this request. I am needing to configure DB mirroring through t-sql from one server. and this is for SQL 2008R2. and I can't use linked servers because our IT risk team thinks they are evil. I'm needing to be able to place this into a four part query. backup, restore with no recovery, set mirrored db partner, set principal db partner.
• Kin Shah: Why cant you use SQLCMD ?
• Ariyel Carver: Because I am also needing to be able to have this on a Cursor. The purpose for this is after DR testing to re establish db mirroring for a 4 full servers, with 40 + db's each.
• Kin Shah: @AriyelCarver The problem is that everytime you post a comment, you are adding more info which for us becomes impossible to assume on ourside and then try to help you. Now as per your above comment, I would still recommend to use sqlcmd or powershell. They support script arguments as well. Do some research and they will prove to be valuable to you.