Post reply

Not able Restore a Differential backup.

  • June 5, 2008 at 3:18 pm

    #191758

    One of my servers crashed due to power fluctuations.

    I have the full and Differential backup files from all the databases on the failed server.

    I take 1 full backup on saturday and differentials for the rest of the week.

    problem: is that I was able to restore the Full back up on a different box. but not the differential.

    RESTORE DATABASE [db1]

    FROM DISK = N'J:\SQLBackups\db1_backup_200805310215.bak'

    WITH FILE = 1,

    MOVE N'Reporting_Data' TO N'e:\Microsoft SQL Server\MSSQL\Data\db1.MDF',

    MOVE N'Reporting_Log' TO N'd:\Microsoft SQL Server\MSSQL\Logs\db1_Log.LDF',

    NOUNLOAD, REPLACE, STATS = 10

    GO

    -- SUCCESSFUL

    RESTORE DATABASE [DB1]

    FROM DISK = N'J:\SQLBackups\db1_backup_200806040100.Diff'

    WITH FILE = 1,

    with NORECOVERY;

    GO

    Msg 3117, Level 16, State 1, Line 1

    The log or differential backup cannot be restored because no files are ready to rollforward.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Also, when I tried doing it thru the restore wizard(right click on database > tasks > restore)

    It says that the differential backup provided is not a part of the same backup set.

    Any direction as to how to restore that differential is really appreciated.

    Thanks

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 5, 2008 at 3:31 pm

    #825150

    Your code:

    RESTORE DATABASE [db1]

    FROM DISK = N'J:\SQLBackups\db1_backup_200805310215.bak'

    WITH FILE = 1,

    MOVE N'Reporting_Data' TO N'e:\Microsoft SQL Server\MSSQL\Data\db1.MDF',

    MOVE N'Reporting_Log' TO N'd:\Microsoft SQL Server\MSSQL\Logs\db1_Log.LDF',

    NOUNLOAD, REPLACE, STATS = 10

    GO

    what you need to do :

    RESTORE DATABASE [db1]

    FROM DISK = N'J:\SQLBackups\db1_backup_200805310215.bak'

    WITH FILE = 1,

    MOVE N'Reporting_Data' TO N'e:\Microsoft SQL Server\MSSQL\Data\db1.MDF',

    MOVE N'Reporting_Log' TO N'd:\Microsoft SQL Server\MSSQL\Logs\db1_Log.LDF',

    NOUNLOAD, REPLACE, NORECOVERY, STATS = 10

    GO

    Then you restore the differential, WITHOUT the NORECOVERY, unless you need to restore from any transaction log backups taken after the differential backup.

    😎

  • June 5, 2008 at 3:49 pm

    #825158

    Thanks Lynn, I will try that!!

    Also, Since i already have restored the Full backup can't I do,

    RESTORE DATABASE [db1]

    NOUNLOAD, NORECOVERY;

    and then restore the Differential

    Will try both and let you know...

    Thanks a bunch for the quick response!!!

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 5, 2008 at 3:51 pm

    #825161

    Nope. You have to start all over with the initial restore from your full backup.

    😎

  • June 5, 2008 at 3:58 pm

    #825163

    🙁

    Tested that and realized...

    Thanks tho..

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 5, 2008 at 5:35 pm

    #825197

    Thank you Lynn.. It worked beautifully...

    🙂

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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