Skip to content

Installing the Adventure Works 2012 sample database

19/03/2014

After installing SQL Server 2012, it is a good idea to download and add the sample database to your SQL Server instance.

To get the datafile, browse to the Microsoft SQL Server Samples page on CodePlex and take the AdventureWorks2012 Data File. Once the download has completed, it is advisable to copy the data file to your DATA folder, in my case, this is located here: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL_2012\MSSQL\DATA.

Some users have noted that if you’re using Windows 7 or 8 then you need to start SSMS as Administrator, I’m using Windows 7 and have had no problem running it up normally but it’s worth bearing in mind if you do hit an error.

Also, you may encounter an “Access is denied” message when attempting to access the datafile – this is possibly the level of permissions on the data file itself. To check and rectify this, browse to the file using Windows Explorer and right-click the datafile, select Properties, Security tab then Edit. For OWNER RIGHTS check that Full control is ticked, if not, tick it and then hit Apply and OK through the remaining dialog windows.

With SSMS open, connect to your database server and click New Query from the toolbar.  Next, add the database by running the following SQL (amend the filename to reflect the location of the file on your system):

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL_2012\MSSQL\DATA\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG;

The following message is displayed (this could vary if you’re not using SQL Server 2012):

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 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL_2012\MSSQL\DATA\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.

The file activation failure is simply saying that the log file could not be found, you can see on the second line that SQL has created the required log file for you.

The third and fourth lines show that SQL Server is upgrading the database from version 705 (SQL 2012 RC0) to version 706 (SQL 2012 RC1/RTM).

For more information on the versions of the MDF file and SQL Server product version, please see Pranavs blog post “How to determine version of Local Sql Instance and your database“.

So now, having followed the above guidance, you can start to following through some of the Microsoft examples and try stuff out fro yourself.

Advertisements

From → SQL Server

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: