Restore a database on the secondary server with the available backups


  • Hi

    I do backup my database,my backup schedule is like this

    Every week on sunday full backup

    Every day,every one hour log backup

    Every day at 6 pm differential backup

    so at the end of the day i do have so many backup files(full backup,log backups,differential backups)

    Now i need to restore the database on secondary server from the available backups (full backup,log backups,differential backup) available

    Now how can i automate the restoring?

    2012年3月12日 下午 12:23



  • Hi,

    If I've got your meaning correct, I think you're going to need a 3rd party tool for this, such as:


    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    2012年3月12日 下午 12:31
  • On the secondary server

    1) RESTORE DATABASE dbname FROM DIESK='\\server\folder\db.bak' --Add WITH MOVE clause if needed  NO RECOVERY.....

    2) Create a job on the secondary server and schedule it

    All the above looks like Log Shipping or Data base Mirroring implementations , have a look into the matter

    Best Regards, Uri Dimant SQL Server MVP

    2012年3月12日 下午 12:45
  • Hi,

    I think first you need to restore your full and the last differential backup and then all log after the last differential backup. then your the secondary server is updated with the primary server.

    After that you can use job to restore log backup after each log backup conducted in the primary server (Every day,every one hour log backup)

    I hope this is helpful.

    Elmozamil Elamir


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    2012年3月12日 下午 02:37
  • Hi i need the restoration automated,any help?
    2012年3月13日 上午 06:55
  • If your looking for a truly automated approach consider using Database mirroring or replication.I would frankly go ahead with replication.

    Backup are better used when you have lost all hope of recovery from any other source. Cosinder this , the fact that you want to perform log backup indicate that you need point in time recovery. How would you achieve this if the logs get restored automatically.

    2012年3月13日 上午 08:43
  • Hi i need the restoration automated,any help?

    if you need to automate it on a regular intervall you should use log shipping for it.

    If you just want to restore all the files by a few clicks, look at the new restore wizard in SSMS 2012 which does have similar feature like Redgate does have.

    2012年3月14日 下午 09:07