Restoring db

Question

Restoring db

SkyBVI

SSCoach

Points: 15128
More actions
August 11, 2010 at 12:52 pm

#140312

Hi,
Is it able to restore a database from just mdf file.
I dont have the ldf file..
Wat can I do?
Thanks,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 9 posts - 1 through 8 (of 8 total)

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

blackbird Ten Centuries Points: 1127 More actions · Answer 1

The MDF is the data file, not a backup file. I'm guessing you're trying to see if you can reattach the database with just the MDF file?

Take a look here:

http://msdn.microsoft.com/en-us/library/ms174385.aspx

If this works, a new LDF file should be created when you re-attach the database.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You might be able to attach it. If the database was shut down cleanly, then in the process of attaching, SQL will create a new ldf. If the database was not shut down cleanly, the attach will fail.

No chance of getting an actual backup?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 3

@ blackbird

Ya,i want to reattach the database with just MDF file.

@ gilamonster

That server was made to shut down and the images of the databases were taken, now wen we are extracting from the images, we can find only the mdf files.

Regards
Sushant Kumar
MCTS,MCP

homebrew01 SSC Guru Points: 55544 More actions · Answer 4

Did you take a full backup before shutting down ? That's always a good idea to do before making changes to databases, servers, configuration etc ... Then you could do a restore which will include the .ldf

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You can try to attach it, you'll just have to remove the reference to the ldf file in the attach dialog. If the image was made while SQL was running it probably won't attach. If that happens, see this blog post for a method of hacking and repairing the DB - http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

msn29 SSC Veteran Points: 229 More actions · Answer 6

try this.

EXEC sp_attach_single_file_db @dbname = '', --- your database name

@physname = '' --- the location of the .mdf file

sqldba4u Hall of Fame Points: 3414 More actions · Answer 7

sqldba4u

Hall of Fame

Points: 3414

August 12, 2010 at 3:51 am

#1205885

Ya, it works.

SkyBVI SSCoach Points: 15128 More actions · Answer 8

Ya, it worked.

Thanks all

Sushant

Regards
Sushant Kumar
MCTS,MCP