How to create database from .mdf file only

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to create database from .mdf file only

12 posts, Page 1 of 2

1 2 »»

How to create database from .mdf file only

Rate Topic

Display Mode

Topic Options

Author

Message

gmamata7

Posted Tuesday, January 10, 2012 12:55 PM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 01, 2012 10:26 AM
Points: 613, Visits: 1,819

Hi,

I have installed SQL Server 2012 and downloaded sanple database from the below link, which has AdventureWorks2008R2_Data.mdf for download.

http://msftdbprodsamples.codeplex.com/releases/view/55330

Now, how to restore AdventureWorks database from AdventureWorks2008R2_Data.mdf file?

Thanks

Post #1233485

Chris Shaw

Posted Tuesday, January 10, 2012 1:21 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 1:20 PM
Points: 17, Visits: 83

Can you use Create Database for attach with rebuild log?

Post #1233494

karkala

Posted Wednesday, January 11, 2012 1:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, March 17, 2012 8:23 PM
Points: 8, Visits: 4

I think you can attach the db by right clicking on the database folder.

Post #1234351

Chris Shaw

Posted Wednesday, January 11, 2012 5:05 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 1:20 PM
Points: 17, Visits: 83

I don't think you can rebuild the log when using the UI, however this is off the top of my head, and well age is not doing my memory well.

Post #1234477

Perry Whittle

Posted Thursday, January 12, 2012 1:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 5,201, Visits: 11,152

Chris Shaw (1/11/2012)

I don't think you can rebuild the log when using the UI, however this is off the top of my head, and well age is not doing my memory well.

You can, not specifying a log will rebuild it

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

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

Post #1234556

roasdasdb 89asdasdasd013

Posted Thursday, January 12, 2012 3:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 12, 2012 7:08 AM
Points: 2, Visits: 131

Try

CREATE DATABASE Database1 ON
(FILENAME = N'C:\Temp\Database1.mdf')
FOR ATTACH_REBUILD_LOG

Rob

Post #1234617

sharmaamard

Posted Thursday, January 12, 2012 3:56 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, July 03, 2012 10:12 PM
Points: 62, Visits: 122

You can attached database mdf file without ldf file, see example given below:-

sp_attach_single_file_db @dbname='Amber',@physname='S:\AMBERDATAFILE\Amber.mdf'

http://msdn.microsoft.com/en-us/library/ms174385.aspx

Post #1234624

crazy4sql

Posted Thursday, January 12, 2012 3:58 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 876, Visits: 3,731

Now, how to restore AdventureWorks database from AdventureWorks2008R2_Data.mdf file?

What if you create a blank file and name it as AdventureWorks2008R2_log.ldf and then try to attach the mdf and ldf file.

Not too sure, but can give a try.

----------
Ashish

Post #1234628

GilaMonster

Posted Thursday, January 12, 2012 4:00 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 37,679, Visits: 29,934

sharmaamard (1/12/2012)

You can attached database mdf file without ldf file, see example given below:-

sp_attach_single_file_db @dbname='Amber',@physname='S:\AMBERDATAFILE\Amber.mdf'

http://msdn.microsoft.com/en-us/library/ms174385.aspx

From that link:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL). Do not use this procedure on a replicated database.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1234631

GilaMonster

Posted Thursday, January 12, 2012 4:01 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 37,679, Visits: 29,934

crazy4sql (1/12/2012)

What if you create a blank file and name it as AdventureWorks2008R2_log.ldf and then try to attach the mdf and ldf file.

You'll get an error saying that the ldf file is not a valid database file and the attach will fail.

CREATE DATABASE ... FOR ATTACH_REBUILD_LOG is the answer providing the DB was shut down cleanly before the ldf file removed.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1234632

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions