Post reply

RESTORE DATABASE

  • April 18, 2013 at 1:31 pm

    #271297

    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

  • April 18, 2013 at 1:35 pm

    #1607985

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 18, 2013 at 2:50 pm

    #1608012

    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

  • April 18, 2013 at 2:59 pm

    #1608016

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 19, 2013 at 6:56 am

    #1608189

    Is there way maybe, to call sql job from another server and that job will have restore syntax?

    Thank you

  • April 19, 2013 at 8:09 am

    #1608239

    SP_START_JOB 'YourJOBname' will do it. also follow Lowell's instruction.

  • July 1, 2013 at 9:51 am

    #1628763

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply