January 4, 2010 at 7:46 am
Hi Everybody,
One of my sql server 2005 database file (mdf) has corrupted and do not have back up of the database. but there is no problem in log file it seems. Please let me know the steps how do to recover the data file(mdf) with data base log file without any third party tool.
Thanks in Advance.
January 4, 2010 at 7:52 am
Restore from a vaild backup, provided you have one?
-- You can't be late until you show up.
January 4, 2010 at 8:05 am
tosscrosby-60625 (1/4/2010)
Restore from a vaild backup, provided you have one?
No .... i do not have back up of the database.
January 4, 2010 at 8:05 am
Sorry to say that you are basically screwed since you have no backups. Your only option is to run DBCC CHECKDB or CHECKTABLE with one of the repair options, probably REPAIR_ALLOW_DATA_LOSS which does exactly what you it says, it fixes the corruption, but you will most likely lose data. Check out Paul Randal's blog[/url] and look at the CHECKDB and Corruption tags.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 4, 2010 at 8:17 am
And I was actually questioned about why I would bother posting today's editorial[/url].
Why aren't you taking backups of the system?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2010 at 8:25 am
Why on earth is there no backup?
Please run this and post the full and complete output.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Without a database backup, the database cannot be recovered from the log file alone. The log file does not necessarily contain the record of all changes made to the DB, especially if the DB is in simple recovery or there has never been a backup made.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2010 at 8:45 am
Jack Corbett (1/4/2010)
Sorry to say that you are basically screwed since you have no backups. Your only option is to run DBCC CHECKDB or CHECKTABLE with one of the repair options, probably REPAIR_ALLOW_DATA_LOSS which does exactly what you it says, it fixes the corruption, but you will most likely lose data. Check out Paul Randal's blog[/url] and look at the CHECKDB and Corruption tags.
After attaching the user databases in the server, i will apply the some data patches and fixes application related, after completing this process only i will take the backup of the database.But unfortunately after completing this data patches and fixes database got corrupted.
January 4, 2010 at 8:51 am
And previous backups? You know, the ones you've been taking on a daily or weekly basis since you became responsible for this database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2010 at 8:52 am
Those steps 'should not' corrupt the database.
Can you post the output from running CHECKDB ?
January 4, 2010 at 8:58 am
sudhakara (1/4/2010)
Jack Corbett (1/4/2010)
Sorry to say that you are basically screwed since you have no backups. Your only option is to run DBCC CHECKDB or CHECKTABLE with one of the repair options, probably REPAIR_ALLOW_DATA_LOSS which does exactly what you it says, it fixes the corruption, but you will most likely lose data. Check out Paul Randal's blog[/url] and look at the CHECKDB and Corruption tags.After attaching the user databases in the server, i will apply the some data patches and fixes application related, after completing this process only i will take the backup of the database.But unfortunately after completing this data patches and fixes database got corrupted.
Take a backup before, take a backup after, heck, depending on how extensive the number of changes you're performing, take a backup in the middle. If you're mucking with production data, it's difficult to be overly paranoid.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2010 at 5:44 pm
Those steps 'should not' corrupt the database.
Can you post the output from running CHECKDB ?
It is hard to say since none of us know the entire situation involved, but first guess I would have to agree with this assessment. What errors does DBCC CHECKDB come back with?
Joie Andrew
"Since 1982"
January 4, 2010 at 5:57 pm
Grant Fritchey (1/4/2010)
sudhakara (1/4/2010)
Jack Corbett (1/4/2010)
Sorry to say that you are basically screwed since you have no backups. Your only option is to run DBCC CHECKDB or CHECKTABLE with one of the repair options, probably REPAIR_ALLOW_DATA_LOSS which does exactly what you it says, it fixes the corruption, but you will most likely lose data. Check out Paul Randal's blog[/url] and look at the CHECKDB and Corruption tags.After attaching the user databases in the server, i will apply the some data patches and fixes application related, after completing this process only i will take the backup of the database.But unfortunately after completing this data patches and fixes database got corrupted.
Take a backup before, take a backup after, heck, depending on how extensive the number of changes you're performing, take a backup in the middle. If you're mucking with production data, it's difficult to be overly paranoid.
In agreement with Grant - It is better to be overly paranoid, than ill-prepared.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 5, 2010 at 6:49 am
Are your other databases backed up ?
This might help show holes in your backup strategy:
January 5, 2010 at 7:35 am
Do you have old .mdf and .ldf files(not of the day when DB got corrupted)? you might have taken file backup on some storage.... so get it and save it some where on this server.
If yes, just delete the current .mdf and .ldf as the DB is any which ways useless now.
attach the old .mdf and .ldf files and see if that works.... (as the chances are very less).... if not then I'm sorry to say that the DB is gone now.....
Rgds,
Pankaj
January 5, 2010 at 8:02 am
pankaj.baluni (1/5/2010)
if not then I'm sorry to say that the DB is gone now.....
That is not necessarily true. There are ways to repair a SQL database with varying amounts of data loss. Until they have been tried you cannot say that the DB is gone. Recommending to delete the mdf without being 1000% sure that it is completely irreparable is very poor advice.
I'm still waiting for the output of CheckDB to see exactly what's wrong. Sudhakara, can you run the CheckDB statement I gave you above and post the results?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply