Attach MDF without 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Attach MDF without LDF

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2007-06-18 : 12:18:56

When I execute this code

EXEC sp_attach_single_file_db @dbname = 'dbABC', 
   @physname = 'D:\Microsoft SQL Server\MSSQL\Data\dbABC.mdf'

I get the following error messages

quote:
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'dbABC'. CREATE DATABASE is aborted.
Log file 'D:\Microsoft SQL Server\MSSQL\Data\dbABC_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

...There was no space on disk and the log file of this DB was too big
So the Network Administrator told me that if I
detach the DB
delete the log
and re-attach the mdf the EM would show a warning, indicating the log file did not exists and it would create an empty one

I did it once and it worked

Today I found the same situation and it didn't work

, I tried attaching the DB through the QA but it din't work anyway. The last backup I have is about 3 weeks old (beacause is a test DB) and I need the updates I made to a huge SP last week...

I have google and have tried some things I found but none of them have worked

A candle loses nothing by lighting another candle

Kristen
Test

22859 Posts

Posted - 2007-06-18 : 13:38:51

Dunno if it will help but there is some info here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=single%20file%20attach,sp_attach_single_file_db

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2007-06-18 : 14:44:11

Thanx Kristen

But I'm more less in the same spot

I will ask for the restore of the phisical files (mdf & ldf)
and retry the attach procedure

if it doesn't work I'll go with plan b restore last backup and start coding all over again

. Furtunately I was able to recover some papers from the recicling bin where I have almost all the code of the SP I'm looking for.

A candle loses nothing by lighting another candle

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 03:31:34

"if it doesn't work I'll go with plan b restore last backup"

Might be worth doing that, then delete the LDF file and replace the MDF file with the one you have salvaged.

(actually, don't Delete/Overwrite, use Rename instead to give you a second-chance!)

For your Single File Attach does the drive/folder where the ORIGINAL database and Log were mounted exist? Are you putting the MDF file in the same folder as the original? All that can help rule out other Gotchas!

Kristen

BashTheGeek
Starting Member

1 Post

Posted - 2007-06-25 : 07:36:16

If you want to attach a MDF without LDF you can follow the steps below
It is tested and working fine

1. Create a new database with the same name and same MDF and LDF files

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

3. Start SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.

Basheer Elevanchalil
http://www.kaamiltech.com

userX
Starting Member

1 Post

Posted - 2007-07-09 : 16:57:06

quote:
Originally posted by BashTheGeek

If you want to attach a MDF without LDF you can follow the steps below
It is tested and working fine

1. Create a new database with the same name and same MDF and LDF files

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

3. Start SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.

Basheer Elevanchalil
http://www.kaamiltech.com

Thanks!! Bansheer!, you don't have any idea how helpful your post was!!.. last week we have a problem like that in Production, the sql server loop itself and doesn't gave us the chance to keep server up...it said that server is hanging...

But i followed you instructions and everything went fine!! Now server is up with a new transaction file, and we're backing up everyday...

Thanks! again!!

Greetings from Mexico!!

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-08-30 : 13:25:08

I did this and it worked as well. Why is this?
1-sp_detach_db database
2-delete the ldf
3-sp_attach_db database
d:\.....mdf

=============================
http://www.sqlserverstudy.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-31 : 00:13:19

When detach db, sql will not leave any open transaction in the db. Therefore, you can reattach it without log file.

Manivannan S
Starting Member

1 Post

Posted - 2011-06-18 : 03:02:15

Please see this link

http://www.sqlprof.com/blogs/sqlserver/archive/2008/04/17/how-to-attach-a-database-without-a-transaction-log-file-ldf.aspx

Mani

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-06-18 : 04:01:45

Please note: 4 year old thread, and that blog post assumes the DB was cleanly shut down

--
Gail Shaw
SQL Server MVP

carolparker
Starting Member

1 Post

Posted - 2012-12-26 : 03:31:39

Hello, if you are getting error message while attemting to open the SQL server database files means that may your SQL database files have gone under corruption issue and you need to fix these issues to resolve various corruptions to make easily accessible to you. For this, first you need to try the inbuilt command that is DBCC CHECKDB that is used to check the logical and physical consistency of all the components of SQL database and can resolve minor corruptions.

But, if in case SQL database if still inaccessible, the the use of third party database recovery software must work to fix the SQL corruption and recover your corrupted components of the database safely. You can try the demo version of MDF Repair tool for free that can efficiently resolve all kinds of issues related to SQL database.
For more info, you can visit : unspammed

Hope, you will recover all of your files safely.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources