.ldf deleted and now cant attach database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » .ldf deleted and now cant attach database

12 posts, Page 2 of 2

««1 2

.ldf deleted and now cant attach database

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Thursday, January 12, 2012 5:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 33,108, Visits: 27,030

It's a long shot and it doesn't always work but I've had some success in the past with such a thing.

1. Make sure the database in question is detached and that the MDF file is in a safe place. "Safe place" means NOT on the drive that you normally store your MDF files, in this case.

2. Create a new database with the same name as the database in question. Note that the logical and physical file names should match the old ones.

3. Properly detach the new database.

4. Copy (not move) the old MDF file from Step 1 over the new MDF file created in Steps 2 and 3.

5. Try to reattach the new database.

It doesn't always work but the answer is always "No" unless you "ask".

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1234671

Perry Whittle

Posted Thursday, January 12, 2012 5:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 5,242, Visits: 11,256

GilaMonster (1/11/2012)

Perry Whittle (1/11/2012)

To re attach a database with a missing log file just use either

sp_attach_single_file_db

CREATE DATABASE .... FOR ATTACH_REBUILD_LOG

Which only works if the database was shut down cleanly before the log was deleted, not in all cases.

But it's logically the first place to start, why go to all the effort of hacking the file in if a simple attach may suffice.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1234689

« Prev Topic | Next Topic »

12 posts, Page 2 of 2

««1 2

Permissions