SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Attach MDF file with no LOG


Attach MDF file with no LOG

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11917 Visits: 2730

It's been quite while since I wrote this, but it may help:

http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
buddy__a
buddy__a
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 257
I don't think this procedure would have help in my situation. I had a database server crash (while doing backups) and the log was corrupted. Tried to do a detach and it detached but with an error so niether of the sp_attach_db procs would work. I would up setting the database to emergency mode and coying all the objects to a new database. However, all transactions for 10 hours before teh crash were gone. I would have expected all transactions to have been flushed to disk by then.



Rufat
Rufat
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 3

see here:

http://www.se-pro.com/faq/sql/faq1.html


Rufat
Rufat
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 3

If this post helped someone please post about it.


ryan mcalister
ryan mcalister
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 1
Make sure the .mdf files are not read only.
max.patel
max.patel
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 3
Balmukund Lakhani (3/31/2006)


There is no easy way of doing it.

I am sure that database was not cleanly shutdown so you won't be able to use only mdf file to recover the database (sp_attach_single_file_db)

Do below steps.

== I detached ran for pubs database files==

DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 3) Since Log File will not be linked to the new MDF File you have to run DBCC REBUILD_LOG

dbcc rebuild_log('dbname','New Log File NAme')

THIS IS UNDOCUMENTED COMMAND.

database will come into Single User mode

Run dbcc checkdb and if there are problems then you need to restore from backup

HTH

max.patel
max.patel
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 3
I am trying to mount a database (SQL Server 2005) from a set of data files (an mdf file, a number of ndf files and an ldf file) from the same database on another server.

I have tried using the attach utility from the SQL Server Management Studio, specifying the mdf file in the top pane which automatically lists the ndf and ldf files in the lower.

I keep getting an error 'Could not open new database 'continuum'. CREATE DATABASE is aborted. The physical file name "h:\......\......continuum_log.ldf" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down.'

Can someone please provide step/tips on where I am going wrong or how to create a new log.
zavhrye
zavhrye
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 135
An easier way would be using the Management studio and in the attach database window highlight the log file and then remove it. Press ok after the database is attached in new log file.
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3711 Visits: 1717
That only works if the database was cleanly shut down - i.e. the transaction log doesn't have any transactions that need to be rolled back. In this case the database was not cleanly shutdown so your advice won't work.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Elliswhite
Elliswhite
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 54
Using SSMS is the simplest way to attach MDF file without LDF file. I mentioned some steps in which you can execute this task.

Firstly open the SSMS and then choose databases an object explorer item
Then right click on it and choose attach option
A window appears on the screen click on add then suddenly browse the MDF file
As on selecting MDF file a confirmation window opens confirm it. In this window the in downward side two files are list i.e. MDF and LDF file. Select the LDF file and remove it. Then click on OK
MDF file successfully attached you can verify in the databases list.

SSMS Expert
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search