About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Wednesday, May 16, 2012

Attach Database without Log File


If you only have the data file for a database, it is possible to attach it with the “ATTACH_REBUILD_LOG” option which will create a log file.

I found this was a necessary method when attempting to install the AdventureWorks databases for SQL 2012.  The .mdf files can be downloaded from the CodePlex site, but no log files are given.  

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\Paul\Sql\Data\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG

CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'C:\Paul\Sql\Data\AdventureWorksDW2012_Data.mdf')
FOR ATTACH_REBUILD_LOG

Thanks to Dave Lassiter for this tip.


Posted by at

1 comment:

  1. RajMay 16, 2013 at 9:23 PM

    Above tips work successfully when database was cleanly shutdown.

    You can still attach your database by following methods whn database was not cleanly shutdown:

    1. Create a same size database
    2. Now shutdown the SQL server
    3. Change the old mdf file with new one
    4. Start the SQL server, your database may go in suspect mode
    5. Now change your database status from suspect mode to emergency mode by ALTER Database command
    5. Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. It will rebuild the log and run full repair

    Note: You may loss some amount of data but your database will be available again.

    Source : Link

    ReplyDelete

Subscribe to: Post Comments (Atom)