Post reply

how to Restore a database

  • September 23, 2009 at 6:29 am

    #204890

    Hi,

    I have scheduled the backup plan as follows:

    1. Full Backup : daily midnight.

    2. Transaction Backup : Every 1 hour

    If my database has crashed on 3:30 PM. Then How i will recover the data of 30m ?

    Please help me to resolve this problem.

    Thanks,

    Sunil Kumar

  • September 23, 2009 at 6:45 am

    #1057445

    How to restore your database to Point-in-Time:

    http://www.databasejournal.com/features/mssql/article.php/3530616/Point-in-Time-Recovery.htm

  • September 23, 2009 at 6:48 am

    #1057449

    Hey Sunil,

    I'm going to do this by using the GUI

    1. Right Click on the database you want to restore

    2. Click Tasks | Click Restore |Click Database

    3. put in all the info you need to, point to the .bak file on disk, ensure to check overwrite the existing database on the options page

    4. Most importantly before you click OK, click the option button (RESTORE WITH NORECOVERY)

    5. click ok and restore from backup

    Now for the logs (if you append your backup you may only have one .trn file to point to, if not you will have 3 to add under step 3. )

    1. Right Click on the database you want to restore

    2. Click Tasks | Restore | Transaction Log

    3. Click the restore from file (if you have multiple files you will want to add all of them)

    4. Check each file that you want to restore from

    5. At the bottom make sure that point in time option is selected. Click the ... button to the right of Most Recent possible.

    6. Under there you will find the option of A specific date and time pick 3:30 am for todays date under there

    7. Click on options and make sure that Restore with Recovery is selected (it should be it is the default but it never hurts to double check)

    I hope this helps good luck.

    Twitter: @SQLBalls
    Blog: http://www.SQLBalls.com
    Channel: https://www.youtube.com/@Tales-from-the-Field

  • September 23, 2009 at 7:11 am

    #1057471

    You'll only be able to get the the point in time since the last transaction log backup if you can back up the tail of the log. That's what's in the current transaction log. If that is not possible, then you will only be able to recover to the last log backup.

    If you need recovery to 30 minutes guaranteed, you need to run log backups every 30 minutes and copy them to a remote system.

Viewing 4 posts - 1 through 3 (of 3 total)

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