restore from another instance?

25112
25112 used Ask the Experts™
on
is it possible to restore a database in another instance from tsql from another instance? (with proper credentials) if so, what is the syntax for that?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
What are you trying to accomplish?
You typically would script the backup of database on the first instance, copy the files, then script restore on second.
If you want to do this live, you can copy a database across instances.

Author

Commented:
Kevin we are trying to send the backup from instance1 to instance 2 to restore there, and hoping to do all in one step.. if somehow like in sqlcmd if we can pass parameters (credentials), was hoping to do all in scripts from one place..

if not , ssis, but I was hoping to do all in script from one place..
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You'd have to run the restore command on the instance to which the db is to be restored, but you could run that statement itself from another instance.

--assume this is running on "server1\instance1"
--and you want to restore a db on "server2\instance2"
DECLARE @sql_cmd varchar(8000)
SET @sql_cmd = 'RESTORE DATABASE db_to_restore FROM DISK = ''...'' WITH MOVE ''...'' TO ''...'', ... WITH ...'
EXEC (@sql_cmd) AT [server2\instance2]
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Author

Commented:
Scott, I tried a simple example:


exec ('select @@version') at POST01

it gives:
Msg 7202, Level 11, State 2, Line 2
Could not find server 'POST01' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.


does your method involved linked server usage?

Author

Commented:
what about this ?
http://msdn.microsoft.com/en-us/library/ms162773.aspx
is it more easy to make work without linked server or sys.servers entry?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Yes, it uses linked servers.  It's going to be more difficult to run a lot of code on other servers without using a linked server.

Author

Commented:
OK.  we will get permissions to set up linked servers.

will [sqlcmd Utility] also need linked servers?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial