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.
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.
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.
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.
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
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
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.
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
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.
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
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!
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