[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MSSQL:  Data recovery or force restore?

Posted on 2006-05-30
Medium Priority
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
  • 2
  • 2
LVL 27

Accepted Solution

ptjcb earned 2000 total points
ID: 16791390
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

ID: 16796619
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

ID: 16799656

(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

ID: 16800659
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

ID: 16800751

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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

872 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