locked
Query for auto restore db RRS feed

  • Question

  • Hi Guys,

    I've already managed to do an scheduled dowload from a ftp server and to auto unpack it when it arrives. The next step i want to do is a automated restore from this db.

    I found a query but i'm not quite sure if it is the correct command query to copy into the job i want to schedule in the SSMS.

    I hope you can help me..

    USE master
    GO
    
    ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    
    
    RESTORE DATABASE YourDB FROM DISK=N'D:\Backup\Pristine.BAK' WITH  FILE = 1,  
    NOUNLOAD,  REPLACE,  STATS = 10
    GO
    
    ALTER DATABASE YourDB SET MULTI_USER
    GO

    Friday, June 13, 2014 1:14 PM

Answers

  • Its  basic query and seems ok to me. but would not work if database you are trying to restore is not present . I removed multi user part as restoring with recovery puts database in multi user

    USE master
    GO
    ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    RESTORE DATABASE YourDB FROM DISK=N'D:\Backup\Pristine.BAK' WITH  FILE = 1,  
    NOUNLOAD,  REPLACE,  STATS = 10, Recovery
    GO

    See various examples mentioned in below link at botom

    http://msdn.microsoft.com/en-us/library/ms186858.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Ramesh Babu Vavilla Friday, June 13, 2014 1:29 PM
    • Marked as answer by MVP_88 Monday, June 16, 2014 9:48 AM
    Friday, June 13, 2014 1:27 PM
  • USE master
    GO
    
    ALTER DATABASE test2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    restore database test2 from disk='D:\Backup\test2.BAK' with file=1, REPLACE,  STATS = 10,
    move 'test2' to 'E:\DATA\test2.mdf',
    move 'test2_log' to 'F:\Log\test2_log.ldf'
    Go
    
    ALTER DATABASE test2 SET MULTI_USER
    GO
    

    It should work. Make sure that owner of the job have enough rights to execute this query. If you are trying to write to a different file than use the move option

    --Prashanth

    • Marked as answer by MVP_88 Monday, June 16, 2014 9:47 AM
    Friday, June 13, 2014 1:39 PM

All replies

  • Its  basic query and seems ok to me. but would not work if database you are trying to restore is not present . I removed multi user part as restoring with recovery puts database in multi user

    USE master
    GO
    ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    RESTORE DATABASE YourDB FROM DISK=N'D:\Backup\Pristine.BAK' WITH  FILE = 1,  
    NOUNLOAD,  REPLACE,  STATS = 10, Recovery
    GO

    See various examples mentioned in below link at botom

    http://msdn.microsoft.com/en-us/library/ms186858.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Ramesh Babu Vavilla Friday, June 13, 2014 1:29 PM
    • Marked as answer by MVP_88 Monday, June 16, 2014 9:48 AM
    Friday, June 13, 2014 1:27 PM
  • USE master
    GO
    
    ALTER DATABASE test2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    restore database test2 from disk='D:\Backup\test2.BAK' with file=1, REPLACE,  STATS = 10,
    move 'test2' to 'E:\DATA\test2.mdf',
    move 'test2_log' to 'F:\Log\test2_log.ldf'
    Go
    
    ALTER DATABASE test2 SET MULTI_USER
    GO
    

    It should work. Make sure that owner of the job have enough rights to execute this query. If you are trying to write to a different file than use the move option

    --Prashanth

    • Marked as answer by MVP_88 Monday, June 16, 2014 9:47 AM
    Friday, June 13, 2014 1:39 PM
  • Thanks Guys,

    I'll check it monday and give you a tumbs up if it works. Have a nice weekend!

    • Marked as answer by MVP_88 Monday, June 16, 2014 7:44 AM
    • Unmarked as answer by MVP_88 Monday, June 16, 2014 9:47 AM
    Friday, June 13, 2014 2:59 PM
  • Thanks Guys,

    I'll check it monday and give you a tumbs up if it works. Have a nice weekend!

    This is not the correct answer you have marked, Please mark the correct answer

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Monday, June 16, 2014 9:20 AM
  • Than k you with this other members can see and benefit from answer

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Monday, June 16, 2014 10:02 AM
  • No problem @Shanky_621

    Always happy to get tips and tricks over here!

    Monday, June 16, 2014 12:00 PM