Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RESTORE DATABASE Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 1:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 12:31 PM
Points: 1,271, Visits: 1,870
Hello,

What is the syntax on how to restore database on these requirement:

I need to run restore sql job from server 1,but the bakup file is on server2 and database where I need to restore is on server2

here is how I started

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'

Can anyone help me?

Thank you
Post #1444118
Posted Thursday, April 18, 2013 1:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 12,744, Visits: 31,078
you can use a UNC path, assuming permissions for the network are in place:
RESTORE DATABASE MyDatabase FROM DISK='\\LOWELLSMACHINE\c$\SQLBackups\2008Backups\MyDatabase.bak'



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1444119
Posted Thursday, April 18, 2013 2:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:07 AM
Points: 182, Visits: 335
Hi Krasavita,
I could not find any method to directly restore on another server through query. Probably you can use osql command utility from inside the job to get it done. Alternately, you can create a windows scheduled task on the server2 using osql commmand line to resotre on server2.

Seraj Alam
Post #1444149
Posted Thursday, April 18, 2013 2:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 12,744, Visits: 31,078
i missed the part about restoring on the remote server.

if you have a linked server, i know you can do EXECUTE AT;

here's an example of creating a table on the remote server, you could change that to run the BACKUP command instead.

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl
(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;

untested:
EXECUTE('RESTORE DATABASE MyDatabase FROM DISK=''E:\Backups\MyDatabase.bak''') AT SeattleSales



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1444153
Posted Friday, April 19, 2013 6:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 12:31 PM
Points: 1,271, Visits: 1,870
Is there way maybe, to call sql job from another server and that job will have restore syntax?

Thank you
Post #1444364
Posted Friday, April 19, 2013 8:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
SP_START_JOB 'YourJOBname' will do it. also follow Lowell's instruction.
Post #1444426
Posted Monday, July 01, 2013 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 14,802, Visits: 27,278
Can you just copy the backup file to a place that is accessible to the second server?

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1469126
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse