restore from another instance?

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?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author 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..
0
Scott PletcherSenior DBACommented:
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]
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

25112Author 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?
0
25112Author 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?
0
Scott PletcherSenior DBACommented:
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.
0
25112Author Commented:
OK.  we will get permissions to set up linked servers.

will [sqlcmd Utility] also need linked servers?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.