DBA Tips Archive for SQL Server


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Jeff Hunter, Sr. Database Administrator



To facilitate the SQL Server 2012 articles and scripts on the iDevelopment.info website, all of the examples are based on the AdventureWorks for SQL Server 2012 sample databases. These sample databases are part of the AdventureWorks Community Sample Databases. This guide presents the steps required to download and install the AdventureWorks2012 and AdventureWorksDW2012 sample databases found in the AdventureWorks Community Sample Databases package for SQL Server 2012. The example SQL Server 2012 instance used in this guide is running on Windows Server 2012.

Starting with SQL Server 2005, the Northwind and Pubs sample databases are not installed by default due to security concerns. These sample databases have been replaced with AdventureWorks. Users are required to download and manually install the AdventureWorks sample databases after successfully setting up SQL Server. All sample databases can be downloaded from Microsoft's SQL Server sample website at http://msftdbprodsamples.codeplex.com/.

The following sample databases are available as part of the AdventureWorks Community Sample Databases for SQL Server 2012:

About CodePlex

SQL Server code samples and sample databases are hosted on CodePlex. CodePlex is Microsoft's free open source project hosting site. Users can create projects to share with the world, collaborate with others on their projects, and download open source software. The portal page for SQL Server on Codeplex catalogs samples included with Product Releases, samples from the community, and independent SQL Server Community projects.

Visit the SQL Server Samples and Community Projects page on CodePlex at: http://codeplex.com/SqlServerSamples.

CodePlex is hosted by Microsoft. Microsoft does not control, review, revise, endorse or distribute the third party projects on the CodePlex site. Microsoft hosts the CodePlex site solely as a web storage site as a service to the developer community.

For more information, read the CodePlex Terms of Use.

Install SQL Server 2012 Sample Databases

As mentioned at the beginning of this article, the Adventure Works for SQL Server 2012 sample databases must be manually downloaded and installed. All sample databases can be downloaded from Microsoft's SQL Server sample website at http://msftdbprodsamples.codeplex.com/.

Download Sample Databases

For the purpose of this guide, download the following sample databases for SQL Server 2012 to one of your SQL Server data file folders.

AdventureWorks2012 Data File

Mar 14, 2012 (193536K)

AdventureWorksDW2012 Data File

Mar 12, 2012 (206080K)

In this example, I downloaded the data files to G:\MSSQL\Data\.

C:\> copy C:\Users\SQLServerAdmin\Downloads\AdventureWorks*.mdf G:\MSSQL\Data\ C:\Users\SQLServerAdmin\Downloads\AdventureWorks2012_Data.mdf C:\Users\SQLServerAdmin\Downloads\AdventureWorksDW2012_Data.mdf 2 file(s) copied. C:\> dir G:\MSSQL\Data\ Volume in drive G is User Databases Volume Serial Number is 3257-1417 Directory of G:\MSSQL\Data 03/23/2013 07:03 PM <DIR> . 03/23/2013 07:03 PM <DIR> .. 03/23/2013 07:02 PM 198,180,864 AdventureWorks2012_Data.mdf 03/23/2013 07:02 PM 211,025,920 AdventureWorksDW2012_Data.mdf 03/23/2013 01:10 PM 5,308,416 ReportServer.mdf 03/23/2013 01:10 PM 4,259,840 ReportServerTempDB.mdf 4 File(s) 418,775,040 bytes 2 Dir(s) 16,657,014,784 bytes free

Other sample databases and tutorials included with Adventure Works for SQL Server 2012 can be found at http://msftdbprodsamples.codeplex.com/releases/view/55330.

Install Sample Databases

Perform the following steps to install the Adventure Works for SQL Server 2012 sample databases downloaded in the previous section.

From SQL Server Management Studio, open a New Query window or use SQLCMD to execute the code below to attach the sample databases to your SQL Server 2012 instance. Notice that the download file for each database only included the data file (MDF) and not the log file (LDF). In order to build a new log file, use the ATTACH_REBUILD_LOG option when attaching the databases.

USE [master] GO CREATE DATABASE [AdventureWorks2012] ON (FILENAME = N'G:\MSSQL\Data\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG GO 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 'L:\MSSQL\Log\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. CREATE DATABASE [AdventureWorksDW2012] ON (FILENAME = N'G:\MSSQL\Data\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG GO File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect. New log file 'L:\MSSQL\Log\AdventureWorksDW2012_log.ldf' was created. SELECT DB_NAME(database_id) AS "Database Name" , type_desc AS "File Type" , name AS "Logical File Name" , physical_name AS "Physical File" , state_desc AS "State" FROM sys.master_files WHERE database_id IN ( DB_ID('AdventureWorks2012') , DB_ID('AdventureWorksDW2012') ); Go Database Name File Type Logical File Name Physical File State --------------------- ---------- --------------------------- ---------------------------------------------------- ------- AdventureWorks2012 ROWS AdventureWorks2012_Data G:\MSSQL\Data\AdventureWorks2012_Data.mdf ONLINE AdventureWorks2012 LOG AdventureWorks2012_Log L:\MSSQL\Log\AdventureWorks2012_log.ldf ONLINE AdventureWorksDW2012 ROWS AdventureWorksDW2012_Data G:\MSSQL\Data\AdventureWorksDW2012_Data.mdf ONLINE AdventureWorksDW2012 LOG AdventureWorksDW2012_Log L:\MSSQL\Log\AdventureWorksDW2012_log.ldf ONLINE

You can safely ignore the following warnings when attaching the sample databases:

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 23-Mar-2013 20:58:01 EDT
Page Count: 22529