Attach MDF file with no LOG

Question

Attach MDF file with no LOG

Viewing 5 posts - 16 through 19 (of 19 total)

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

max.patel SSC Enthusiast Points: 100 More actions · Answer 1

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 SSC Enthusiast Points: 100 More actions · Answer 2

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 SSC Veteran Points: 216 More actions · Answer 3

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 One Orange Chip Points: 29544 More actions · Answer 4

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 SSCommitted Points: 1783 More actions · Answer 5

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