locked
backup/restore RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,
    Where do I get the step by step sql scripts and explanations on how to get point in time restore?
    I have read about doing a full backup once a day, then a differential and transaction logs.
    But I do not find any practical examples which I can apply to the test database server to see if I can get back the data in a particular time.
    Can you guide me please?
    Thanks
    Thursday, November 8, 2012 12:41 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Restore full back up with no recovery option

    Restore the very last diff backup with no recovery option

    Restore all tran log backup since the last Diff backup with no recovery option

    restore the very last tran log backup with recovery option and specify the stop at time

    hope this helps

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by arkiboys Sunday, November 11, 2012 4:24 PM
    Thursday, November 8, 2012 12:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    http://msdn.microsoft.com/en-us/library/ms190982%28v=sql.105%29.aspx
    http://www.mssqltips.com/sqlservertip/1229/sql-server-point-in-time-recovery/
    http://www.databasejournal.com/features/mssql/article.php/3530616/Point-in-Time-Recovery.htm

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Ramesh Babu Vavilla Thursday, November 8, 2012 1:18 PM
    • Marked as answer by arkiboys Monday, November 19, 2012 2:19 PM
    Thursday, November 8, 2012 1:13 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Restore full back up with no recovery option

    Restore the very last diff backup with no recovery option

    Restore all tran log backup since the last Diff backup with no recovery option

    restore the very last tran log backup with recovery option and specify the stop at time

    hope this helps

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by arkiboys Sunday, November 11, 2012 4:24 PM
    Thursday, November 8, 2012 12:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Restore full back up with no recovery option

    Restore the very last diff backup with no recovery option

    Restore all tran log backup since the last Diff backup with no recovery option

    restore the very last tran log backup with recovery option and specify the stop at time

    hope this helps

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Sounds good.
    Can you please let me know or point me to the right site where I can backup/restore based on these criteria ?
    Thursday, November 8, 2012 1:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    http://msdn.microsoft.com/en-us/library/ms190982%28v=sql.105%29.aspx
    http://www.mssqltips.com/sqlservertip/1229/sql-server-point-in-time-recovery/
    http://www.databasejournal.com/features/mssql/article.php/3530616/Point-in-Time-Recovery.htm

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Ramesh Babu Vavilla Thursday, November 8, 2012 1:18 PM
    • Marked as answer by arkiboys Monday, November 19, 2012 2:19 PM
    Thursday, November 8, 2012 1:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Restore full back up with no recovery option

    Restore the very last diff backup with no recovery option

    Restore all tran log backup since the last Diff backup with no recovery option

    restore the very last tran log backup with recovery option and specify the stop at time

    hope this helps

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Hi,
    I now have the scripts (Generated from the script button when backup/Restore).

    A few questions:
    1- What do you mean by "no recovery option" ?
    2- At present I have the full backup of the database scheduled to be at mid-night. This is scheduled by the sql job running the backup script.
    Should I also schedule the backup of transaction log and Differential backup using the script too?
    How often? Can you give me an example please?
    Thanks

    • Edited by arkiboys Sunday, November 11, 2012 3:39 PM
    Sunday, November 11, 2012 3:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    NoRecovery  ->In simple way ->it says that-leave the database is in restoring state so that you can apply further backups to recovery the database it might be

    point -in-time or specific time

    for ex->you have one full backup and up to 10 Log backups...

    Incase if you need point in time then you have to do like below-

    so first you need to restore the full backup with Norecovery state

    ex- restore database <dbname> from disk='<path where the bacup file exists>' with Norecovery -->once its done the database will be in restoring state so that you can allow next trns...

    for restore log database from disk='<where the trnlogbackup exists for ex-Trn1.trn>' with norecovery

    similarly....you can restore upt to 9th trn... like

    restore log database from disk='<trn2.trn' with norecovery

    go

    restore log database from disk='<trn3.trn>' with Norecovery

    go

    ---keep continue up to trn9.trn

    finally to make database to online that is last trn the database you need to bring online by using with recovery..

    for ex->restore log database from disk='<trn10.trn>' with recovery

    go

    --the same ex -you can also you use for latest Full+latest differential + next transaction log backups after latest differential backup restored

    Note ->if you really dont know then I request you to test on your test server please....

    if you want to know further information see below-

    Relationship of RECOVERY and NORECOVERY Options to Restore Phases

    A specific RESTORE statement either ends after the redo phase or continues through the undo phase, depending on whether the statement specified WITH NORECOVERY, as follows:

        WITH RECOVERY includes both the redo and undo phases and recovers the database; additional backups cannot be restored. This is the default.

        If the roll forward set has not been rolled forward far enough to be consistent with the database, the undo phase cannot occur. The Database Engine issues an error and recovery stops.

        If the whole roll forward set is consistent with the database, recovery is performed, and the database can be brought online.

        WITH NORECOVERY omits the undo phase to preserve uncommitted transactions. Omitting the undo phase allows for restoring other backups to roll the database further forward in time. Occasionally, RESTORE WITH NORECOVERY rolls forward data to where it is consistent with the database. In these cases, the Database Engine issues an informational message stating that the roll forward set can now be recovered by using the RECOVERY option.
     reference ->http://msdn.microsoft.com/en-us/library/ms186858%28v=sql.90%29.aspx

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

    Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.



    • Edited by Rama Udaya Sunday, November 11, 2012 5:00 PM
    Sunday, November 11, 2012 3:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you
    Sunday, November 11, 2012 4:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    NoRecovery  ->In simple leave the database is in restoring state so that you can apply further backups to recovery the database it might be

    point -in-time or specific time

    for ex->you have one full backup and up to 10 Log backups...

    Incase if you need point in time then you have to do like below-

    so first you need to restore the full backup with Norecovery state

    ex- restore database <dbname> from disk='<path where the bacup file exists>' with Norecovery -->once its done the database will be in restoring state so that you can allow next trns...

    for restore log database from disk='<where the trnlogbackup exists for ex-Trn1.trn>' with norecovery

    similarly....you can restore upt to 9th trn... like

    restore log database from disk='<trn2.trn' with norecovery

    go

    restore log database from disk='<trn3.trn>' with Norecovery

    go

    ---keep continue up to trn9.trn

    finally to make database to online that is last trn the database you need to bring online by using with recovery..

    for ex->restore log database from disk='<trn10.trn>' with recovery

    go

    --the same ex -you can also you use for latest Full+latest differential + next transaction log backups after latest differential backup restored

    Note ->if you really dont know then I request you to test on your test server please....

    if you want to know further information see below-

    Relationship of RECOVERY and NORECOVERY Options to Restore Phases

    A specific RESTORE statement either ends after the redo phase or continues through the undo phase, depending on whether the statement specified WITH NORECOVERY, as follows:

        WITH RECOVERY includes both the redo and undo phases and recovers the database; additional backups cannot be restored. This is the default.

        If the roll forward set has not been rolled forward far enough to be consistent with the database, the undo phase cannot occur. The Database Engine issues an error and recovery stops.

        If the whole roll forward set is consistent with the database, recovery is performed, and the database can be brought online.

        WITH NORECOVERY omits the undo phase to preserve uncommitted transactions. Omitting the undo phase allows for restoring other backups to roll the database further forward in time. Occasionally, RESTORE WITH NORECOVERY rolls forward data to where it is consistent with the database. In these cases, the Database Engine issues an informational message stating that the roll forward set can now be recovered by using the RECOVERY option.
     reference ->http://msdn.microsoft.com/en-us/library/ms186858%28v=sql.90%29.aspx

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

    Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    Hi,
    How can you specify if the backup is with Recovery or with NoRecovery?
    Thanks
    Sunday, November 11, 2012 4:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Recovery and Norecovery option is comes for restore not for the Backup..but except for tail log backup (you can use norecovery).

    Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Sunday, November 11, 2012 4:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you all
    Monday, November 19, 2012 2:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    you are Welcome!!

    Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Tuesday, November 20, 2012 4:12 AM