[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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?
0
25112
Asked:
25112
  • 4
  • 2
2 Solutions
 
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now