Attach Sample Database - Adventureworks in SQL Server 2012

By:   |   Comments (32)   |   Related: > Testing


Problem

The sample database AdventureWorks plays an important role while performing test operations and learning about new features. So after the installation of SQL Server 2012 and searching for the sample database AdventureWorks I found that it contains only the "mdf" file to download.  This tip gives you the steps you need to follow to install the sample database with only the data (mdf) file.

Solution

First of all, download the AdventureWorks for SQL Server 2012 from this Microsoft link: Download Adventure Works for SQL Server 2012 (Notice that the download only contains the mdf file).

Attach Using SSMS

I tried to attach the database using SQL Server Management Studio as follows:

Right Click on Databases > Attach and click Add... > select the AdventureWorks mdf file. 

sql server attach database ssms

Notice the message above in front of log file "Not Found". However if I click on OK to proceed with the attach database I get the following error.

ssms attach database error

By using SQL Server Management Studio we cannot simply attach the sample database "AdventureWorks" without the log file.  What we need to do is to delete the log file from the attach database.

To do this, select the log file which is showing as "Not Found" as seen below.

sql server attach database files

Select the log file and remove the log file by clicking on the Remove button.  The attach now only contains the MDF as shown below. Click OK to attach the database and this should work.

ssms remove delete log file for attach
attached database in sql server

Using T-SQL to Attach the Database

To attach a database without the log file we need to use the Attach_Rebuild_Log option.

The ATTACH_REBUILD_LOG specifies that the database is created by attaching an existing set of operating system files. If one or more transaction log files are missing, the log file is rebuilt using this command. The ATTACH_REBUILD_LOG automatically creates a new, 1-MB log file and this file is placed in the default log file location.

So the command would be:

CREATE DATABASE AdventureWorks2012_Data
ON (FILENAME = N'E:\SQLData\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG 
Go

using attach rebuild log to attach database in sql server

As the screenshot above shows SQL Server created a new log file for the database.

In order to use the ATTACH_REBUILD_LOG this requires the following:

  • A clean shutdown of the database
  • All data files (MDF and NDF) must be available

Verify Logical and Physical Integrity of Database

Once we have the database attached, we can check the logical and physical integrity of all the objects within the database by executing a DBCC CHECKDB.

DBCC CHECKDB ('AdventureWorks2012_Data')
GO
Next Steps
  • Download the AdvertureWorks database and use it to test some of the new features in SQL Server


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, July 21, 2020 - 8:40:24 AM - Hugo Back To Top (86164)

Thank you for this simple yet powerful tip ! It took me hours to realize this '-_-


Monday, August 5, 2019 - 2:31:06 PM - Jeremy Kadlec Back To Top (81962)

Srikanth,

The URL has been updated.  Thank you for letting us know.

Thank you,
Jeremy Kadlec
Community Co-Leader


Saturday, August 3, 2019 - 4:03:58 AM - Srikanth g Back To Top (81948)

I am unable to download the Advenutreworks 2012 mdf file. Please provide the correct link.


Thursday, September 14, 2017 - 2:36:16 PM - ajit Back To Top (66289)

 

 thanks for sharing................


Thursday, March 2, 2017 - 1:59:46 AM - Pedja Back To Top (46906)

 

 Thanks for sharing.It's working


Saturday, October 15, 2016 - 3:54:57 AM - Dres Back To Top (43568)

 Thank you for the step-by-step... it worked!


Saturday, October 8, 2016 - 11:04:10 AM - kumar sunny Back To Top (43513)

 

 thnaks sir...its absolutely working

 


Thursday, September 15, 2016 - 3:34:26 AM - Uchendu Korieocha Back To Top (43326)

 Thanks for this brief tutorial and the good comments below. i was able to attach the AdventureWorksDW_2012 database by running SSMS as an administrator. this works perfect.

 

 


Monday, June 20, 2016 - 3:44:03 AM - Daniel Waduka Back To Top (41725)

 

Thank you very much. I was really stuck at this point.


Tuesday, May 31, 2016 - 8:23:13 AM - Marvin Back To Top (41581)

You can also run SSMS as an administrator, and try to attach the .mdf file adain it should work.

 

Regards

 


Friday, April 8, 2016 - 8:24:09 AM - Zsolt Back To Top (41165)

 Thanks Rajendra for sharing. It's working on Microsoft SQL Server Express (64-bit) Ver:12.0.2269.0


Friday, April 8, 2016 - 4:49:42 AM - Gideon Back To Top (41162)

 This really did solve the issue. Thanks.

 


Wednesday, March 23, 2016 - 2:44:32 AM - viswa Back To Top (41036)

Thanks Rajendra for sharing

 

It is working in Microsoft Sql server 2012 But where as in MS SQL Server 2005 iam not able to add the .MDF File.

Is there any alternate procedure.

 

 


Saturday, October 24, 2015 - 4:33:34 AM - Paul McKillop Back To Top (38961)

Thanks Rajendra. Asimple, straightforward explanation at last.


Thursday, September 10, 2015 - 11:15:17 AM - Sheila Back To Top (38637)

 

Thank you for this well described method for integrating AdventureWorks into the SQL Server Management program. 

 


Tuesday, June 23, 2015 - 3:17:38 AM - Prashant Gupta Back To Top (37985)

Thanks for sharing


Friday, April 3, 2015 - 11:33:44 PM - RobertQ Back To Top (36825)

Thanks for the tip! it did solve my problem.


Thursday, January 15, 2015 - 1:08:21 AM - rosemary Back To Top (35947)

This tip was very useful me. Thanks!!!

Rosemary


Monday, January 12, 2015 - 2:20:06 AM - rajendra Back To Top (35898)

what is the error you are getting?


Saturday, January 10, 2015 - 5:17:32 AM - ali Back To Top (35892)

hi,

I have installed the SQL Server 2012, when i am attaching the sample database, there is a log file and even if remove the log file the database is not going to be attached, can you pelase help me to solve this problem.

thanks

ali


Tuesday, October 7, 2014 - 12:55:54 PM - koti Back To Top (34868)

Hi friends,

 when I attached adventureworks database 2012 the below error occured.please suggest right path

 

An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details.


Thursday, October 2, 2014 - 5:46:42 AM - Arni Aravind Back To Top (34817)

Hi Dave ,

Thank you for the post. SOLVED my pblm.


Thursday, August 7, 2014 - 5:27:45 PM - Bishop Back To Top (34046)

To Peter:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\AdventureWorks2012_Data_log.ldf'.

Right click C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA

Go to Security tab. Add your account and make sure EVERYTHING is checked:

Full Control

Modify

Read & Execute

List folder


Tuesday, July 1, 2014 - 3:33:45 PM - Dante Quaglietta Back To Top (32501)

Hi, your tip work ! Thank you very much


Thursday, June 12, 2014 - 9:03:18 AM - Dmitriy Back To Top (32213)

Thanks so much! You're the best! I could not figure it out for two days! You're the savior!


Thursday, February 20, 2014 - 10:59:27 AM - Peter Back To Top (29514)

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\AdventureWorks2012_Data_log.ldf'.
Could not open new database 'AdventureWorks2012'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect. (Microsoft SQL Server, Error: 5123)

 

I received the error above and cannot get the database to attach. I am logged in as an administrator and have ensured I has rights to everything. I am trying to teach myself SQL Server 2012 but can't even get past this first step. Any help would be much appreciated. Thank you!


Friday, July 5, 2013 - 4:42:42 AM - Tebo Back To Top (25719)

Hi

 

This wa huge help to me....:)


Thursday, May 2, 2013 - 9:07:35 PM - Michael Pomeroy Back To Top (23694)

Thank you for the info on how to connect the sample database. I was working with a book and was totally lost until I found you info,

Very clear and simple.

 -Thanks


Friday, March 22, 2013 - 12:16:56 PM - mohsen Back To Top (22962)

Thanks. very infprormative and useful. It helped me alot.A simple solution but very critical.


Wednesday, January 23, 2013 - 1:01:36 PM - Joyce C Back To Top (21655)

Just wanted to say thanks for the information regarding Attaching Sample Database - Adventureworks in SQL Server 2012

It was a real time saver.


Wednesday, December 5, 2012 - 3:27:45 AM - Mohsen Back To Top (20753)

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect.

 

New log file 'D:\AdventureWorks2012_log.ldf' was created.

 

Converting database 'AdventureWorks2012' from version 705 to the current version 706.

 

Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706.

 

Hi

what the meaning of above message , appear when i run your suggested script

thaks


Monday, May 14, 2012 - 9:02:31 AM - Maneesh Bhatt Back To Top (17449)

Nice information ..Thanks















get free sql tips
agree to terms