Need to Restore .mdf and .ldf - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Need to Restore .mdf and .ldf

Author

Topic

sean.sullivan
Starting Member

7 Posts

Posted - 2009-02-09 : 16:48:34

I have a problem. I am working with our IT staff to restore about 8 to 10 SQL 2005 databases. Here is the problem. No backups. Had to undelete the files from the array, realize possible data loss. I have the MDF and LDF files, however they were not created by a clean shutdown. I have spent the last 8 hours trying everything on the Web I can find. The best result as of yet is I can get one in Emergency Mode, see the data but when I run the dbcc it goes to suspect. Can't clear it. Any ideas?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-02-10 : 09:52:02

Are you on 2000 or 2005?

If on 2005, follow the instructions at [url]http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx[/url]. If on 2000, get the database into emergency mode and then you'll need to manually do everything that EMERGENCY mode repair does in 2005 - rebuild the log using DBCC REBUILD_LOG and then do a full DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. Then start taking backups...

Let us know how you get on.

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-10 : 10:04:13

I would create a new database with the tables in it.
BCP the data out from these tables (in emergency mode) and bcp into the new database.

Have a look at
http://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.html
I take it you know that v2005 is alter database set emergency.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-02-10 : 10:24:08

Well, what you have on your web page is not the complete (2000-only) solution if the log contains uncommitted transactions - the database may be structurally inconsistent if you just access the database in emergency mode without cleaning any of the potential inconsistencies up.

For 2000, even if you're going to extract out, be very careful of extracting out data without rebuilding the log and running repair first. That's why I put emergency mode repair into SQL Server 2005.

Thanks

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

tech_tiger
Starting Member

3 Posts

Posted - 2009-02-12 : 02:25:48

Hello,
There is the last option of SQL data recovery which can be performed with data recovery tools. So google out and select the best one to repair corrupt files.Software's which are available on internet provides a fee download facility to its users for evaluating their softwares.

Good Luck!!!

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-12 : 06:23:33

quote:
Originally posted by paulrandal

Well, what you have on your web page is not the complete (2000-only) solution if the log contains uncommitted transactions - the database may be structurally inconsistent if you just access the database in emergency mode without cleaning any of the potential inconsistencies up.

For 2000, even if you're going to extract out, be very careful of extracting out data without rebuilding the log and running repair first. That's why I put emergency mode repair into SQL Server 2005.

Thanks

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

Yep - that was dealing mainly with complete losses of the tr log so no hope of applying any. The idea is to get as much data as you can - usually having to jump round corrupt data.
I've never had a problem with doing that but have had issues with trying to repair.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sean.sullivan
Starting Member

7 Posts

Posted - 2009-02-18 : 14:22:12

Thanks for the replys, had Microsoft on the phone, had to restore from an old backup. Still have one more database that needs work: Indicates the The header for the file 'database' is not a valid database file header for '***.mdf'. Page Audit property is invalid.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-02-18 : 14:37:46

You'll need to restore that database from a backup too - there's no other way around a corrupt file header page in the primary filegroup.

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

abhishek_kur1
Starting Member

1 Post

Posted - 2010-03-04 : 06:18:14

Hi please any body tell me that how can i restore the database using mdf ldf in sql

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-04 : 07:36:16

make a new post -- don't hijack an old one. Explain as much as you can whet the situation is. there are some great experts here. PaulRandal wrote the recovery / integrity tools for sqlserver for instance.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

addisionphilip
Starting Member

4 Posts

Posted - 2011-02-18 : 01:20:34

quote:
Originally posted by abhishek_kur1

Hi please any body tell me that how can i restore the database using mdf ldf in sql

This is not the right way to ask a question, you can generate a new thread to ask a question. If you will describe the scenario then its very helpful to reply. You can try sql recovery tool to restore your database.

rahulrajpal08
Starting Member

8 Posts

Posted - 2011-03-22 : 07:15:17

Read the below article to overcome the above problem:
unspammed

Database expert

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-22 : 08:20:38

Yet another piece of utter garbage that's an advertisement disguised as a technical article.

I'm not even going to go into the technical errors in that 'article', if I did my post would be longer than the article itself. Please ignore that article.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources