Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: SQL Server Restore

    I have a little problem guys, any help will be very gratefull, here's the case:

    I have these backup files :

    - FullTLM_Full_25122005_0000.bak ( SQL Full Backup every 1 week )
    - FullTLM_Diff_26122005_0030.bak until FullTLM_Diff_30122005_0030.bak ( SQL Differential Backup every 1 day )
    - FullTLM_Log_25122005_0100.bak until FullTLM_Log_30122005_1100.bak ( SQL Log Backup every 2 hours )

    I want to restore my database to 30 December 2005 at 09:00. I've run these sequence of restore :
    ------------------------------------------------------------------------------------------------

    restore database fulltlm30
    from disk = 'D:\TestLog\FullTLM_Full_25122005_0000.bak'
    with norecovery,
    move 'FullTLM_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30.mdf',
    move 'FullTLM_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30_Log.mdf'
    go

    restore database fulltlm30
    from disk = 'D:\TestLog\FullTLM_Diff_26122005_0030.bak'
    with norecovery,
    move 'FullTLM_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30.mdf',
    move 'FullTLM_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30_Log.mdf'
    go

    restore database fulltlm30
    from disk = 'D:\TestLog\FullTLM_Diff_27122005_0030.bak'
    with norecovery,
    move 'FullTLM_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30.mdf',
    move 'FullTLM_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30_Log.mdf'
    go

    restore database fulltlm30
    from disk = 'D:\TestLog\FullTLM_Diff_28122005_0030.bak'
    with norecovery,
    move 'FullTLM_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30.mdf',
    move 'FullTLM_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30_Log.mdf'
    go

    ---------------------------------------------------------
    This sequence work perfectly, but when i run this query :
    ---------------------------------------------------------

    restore database fulltlm30
    from disk = 'D:\TestLog\FullTLM_Diff_29122005_0030.bak'
    with norecovery,
    move 'FullTLM_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30.mdf',
    move 'FullTLM_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30_Log.mdf'
    go

    ----------------------------------------
    error happened with this error message :
    ----------------------------------------

    Server: Msg 3136, Level 16, State 1, Line 1
    Cannot apply the backup on device 'D:\TestLog\FullTLM_Diff_29122005_0030.bak' to database 'fulltlm30'.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    ------------------------------------------------------------------------------------------------------------------------------------
    i also try to restore to date 30 december and receive same error message, and then i try to restore the log file, i run this query :
    ------------------------------------------------------------------------------------------------------------------------------------

    restore log fulltlm30
    from disk = 'D:\TestLog\FullTLM_Log_28122005_0100.bak'
    with norecovery,
    move 'FullTLM_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30.mdf',
    move 'FullTLM_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30_Log.mdf'
    go

    ...
    ...
    ... until

    restore log fulltlm30
    from disk = 'D:\TestLog\FullTLM_Log_30122005_0900.bak'
    with recovery,
    move 'FullTLM_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30.mdf',
    move 'FullTLM_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\FullTLM30_Log.mdf'
    go

    -----------------------------
    This sequence work perfectly.

    All i want to know is the result between restoring each log until dec 30 09:00 has the same result with if I restore the
    differential at dec 30 and then restore the log from dec 30 01:00 until dec 30 09:00. I also want to know why those errors happened.

    Thank you very much

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Restoring just the transaction logs should give you the same result as restoring the differentials in their place. If you still have the possibly faulty backup, you can try to run restore verifyonly (not sure of the syntax) on it to see if it is bad. But that is just my own curiosity.

  3. #3
    Join Date
    Jan 2006
    Posts
    2

    Thumbs up

    Quote Originally Posted by MCrowley
    Restoring just the transaction logs should give you the same result as restoring the differentials in their place. If you still have the possibly faulty backup, you can try to run restore verifyonly (not sure of the syntax) on it to see if it is bad. But that is just my own curiosity.


    I've found the answer of my question, it is because at 28 des 2005, someone has perform full backup database, but the file is at another place. That's why the differential backup for 29 des cannot apply to the full backup at 25 des. BTW, thank you for your knowledge sharing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •