MSSQL:  Data recovery or force restore?

Posted on 2006-05-30
Last Modified: 2006-11-18
I need to restore my sql server from backup.  Normally no problem, however my last full backup filled up the disk before it finished thus that backup is no good.  The subsequent differentials ran OK.  However I'm in a situation where the database blewup and I need to restore.  

My situations.
Week 1 - successful full
week 1 - successful differentials
week 2 - failed full
week 2 - successful differentials
end of week 2 - database crash - need to restore.

I see to not be able to apply the successful differentials from week 2 to the full from week 1.  I suspect it thinks the full from week2 was successful and differentials are from the point?  "Cannot apply the backup on device '<file>' to the database '<database>'" is the message I get.

At this point we're not concerned about consistency, we just want to get raw data back to re-insert into a new database.  Is there a way?  Just to see raw tables, csv, tab delimited or anything would be fine.  Or better yet, apply the week2 differential to the week1 full.  Am I just SOL?
Question by:hr_sea
    LVL 27

    Accepted Solution

    The differentials from week 2 are based on the week 2 full backup. You will be able to restore week 1 backups. The week 2 backups are useless and cannot be restored.
    LVL 28

    Expert Comment

    Database crashed what is it in suspect state or what tell us may be there is way to get it back online.

    if you are dependend on backup than the only way is what ptjcb suggested.

    Author Comment


    (disclaimer:  I'm not a sql expert so my terminology may be wrong)

    An update statement which changed all the values in one field (column) was executed.  Our "dba" was not using transactional updates (?) so could not roll back the action.

    So, I guess what I'm seeing is that any differential after any sort of incomplete full is worthless.
    LVL 27

    Expert Comment

    Yes. A differential is based on the changes of the last full backup (whether or not it was successful) - because your full backup failed, then the differentials based on it are useless.

    Your terminology is fine - it sounds as if your dba ran an update statement without the WHERE clause. He is not the first one to do that or the last.

    Author Comment


    Yes, our dba ran and update without the WHERE.  I know it happens!  Thank you!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now