Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Attach MDF file with no LOG Expand / Collapse
Author
Message
Posted Sunday, April 2, 2006 6:44 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914

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
Post #270406
Posted Sunday, April 2, 2006 7:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 4:50 PM
Points: 324, Visits: 217
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.


Post #270409
Posted Thursday, June 8, 2006 1:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2008 9:34 PM
Points: 5, Visits: 3

see here:

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

Post #285826
Posted Tuesday, September 5, 2006 2:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2008 9:34 PM
Points: 5, Visits: 3

If this post helped someone please post about it.

 

Post #306392
Posted Wednesday, October 11, 2006 7:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 13, 2007 6:10 AM
Points: 23, Visits: 1
Make sure the .mdf files are not read only.
Post #314624
Posted Wednesday, October 24, 2007 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2007 8:55 AM
Points: 2, 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
Post #414463
Posted Wednesday, October 24, 2007 8:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2007 8:55 AM
Points: 2, 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.
Post #414469
Posted Wednesday, June 4, 2008 5:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 6:20 AM
Points: 4, Visits: 113
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.
Post #511269
Posted Wednesday, June 4, 2008 11:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #511617
Posted Monday, May 5, 2014 1:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 2:23 AM
Points: 45, 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
Post #1567419
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse