Cannot install adventureworks 2012 database - Oper

Question

I am trying to install AdventureWorks 2012 database in sql server 2012. I got the mdf file from this link - http://msftdbprodsamples.codeplex.com/releases/view/93587

Here is the name of the file I downloaded - AdventureWorks2012_Database.zip

I am doing all this on a windows 7 64 bit.

I get the error below:

Attach database failed for Server 'SuperPC\SQL2012'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Unable to open the physical file "C:\Databases\AdventureWorks2012_Data.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

Answer 1

I solved this by:

1. Attaching the file AdventureWorks2012_Data.

   1.1. Then the file AdventureWorks2012_log appears and does not exist.

2. Use the Remove button for eliminating the .log file.

3. Press OK.

Then the new database appears.

Answer 2

Thanks for previous suggestions. You don't have to move the database files to the SQL server directory or delete the database log file. Instead you simply give the Full Control permission to both .MDF and .LDF files for Authenticated Users. By doing so I successfully attach both AdventureWorks2012 and AdventureWorksLT2012 sample databases in SQL Server Management Studio (SSMS).

Answer 3

You don't have to move the .mdf file. Just right click the folder the database is in and change the security permissions. I gave authenticated users full control. Then I ran the TSQL script below to attach the database:

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\Adventureworks\AdventureWorks2012_Data.mdf'), 
    (FILENAME = 'C:\Adventureworks\AdventureWorks2012_Log.ldf') 
    FOR ATTACH; 

As you can see my database files are located in a folder called Adventureworks.

Answer 4

I solved this problem by downloading the Adventure works from this website

http://msftdbprodsamples.codeplex.com/downloads/get/723031

Note when downloading an adventure works zip folder, ensure the file is appended with _data e.g AdventureWorks2012_Data.zip as this file contains both the .mdf and .ldf files.

After it is downloaded, extract the folder contents to this url on your computer C:\Program Files\Microsoft SQL Server\MSSQL11.***\MSSQL\DATA. In SQl server, go to the Object Explorer and Right click on the Databases to attach the database. In the Attach dialog box, you should select Adve********.mdf. Everything you should be fine from here.

I hope this helps.

Answer 5

Steps to attach AdventureWorks2012 database into your local MSSQL database:

Solution 1: If you have enough space in C:\ Drive in Windows and want to create the database in C:\ Drive

1. Place the AdventureWorks2012_Data.mdf and AdventureWorks2012_log.ldf in the below location C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA

2.Right click on 'Databases' in the object explorer and select 'Attach'

3.In the Attach Databases dialog box, Click on 'Add' button in the databases to attach: field.

4. Navigate to the MDF file location C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf and click 'OK' in the bottom of the 'Attach Databases' dialog box.

5.Now the database 'AdventureWorks2012' is created in SQL Sever.

Solution 2: If you do not have enough space in C:\ Drive in Windows

1.Connect to your local server by selecting server name XXX\SQLEXPRESS and Authentication - 'Windows Authentication'

2.Right click on 'Databases' in the object explorer and select 'Attach'

3.In the Attach Databases dialog box, Click on 'Add' button in the databases to attach: field.

4. Navigate to the MDF file location for e.g. F:\AdventureWorks2012_Database\AdventureWorks2012_Data.mdf and click 'OK' in the bottom of the 'Attach Databases' dialog box.

5. This will generate the below error message: 'An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.'

6. SOLUTION is :

   i) Right click on the AdventureWorks2012_Data folder, select Properties.

   ii) click on 'Security' tab

   iii) Select Users(xxx\Users) in the 'Group or user names:' section

   iv) Click on 'Edit' button

   v) Select Users(xxx\Users) in the 'Group or user names:' in the popup,

   vi) Check the 'Full control' check box in the 'Permissions for Users' section.

   vii) Click ok & ok.

7. Now back to SQLsevers's Attach Databases screen, Click on 'Add' button in the databases to attach: field.

8. Navigate to the MDF file location e.g. F:\AdventureWorks2012_Database\AdventureWorks2012_Data.mdf and click 'OK' in the bottom of the 'Attach Databases' dialog box.

9. Now the database 'AdventureWorks2012' is created in SQL Sever.

Answer 6

The reason for the problem - Putting the database mdf and ldf files in a directory outside the "official" SQL server installation folder.

Solution -

http://tryingmicrosoft.com/error-while-attaching-a-database-to-sql-server-2008-r2/

Paste your .mdf file and ldf file in this directory - C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\DATA

Solutions that did NOT solve the problem -

1 - Unblocking the zipped files. Also checked that mdf and ldf files are not blocked. (Steps - right click zip file > properties > unblock)

2 - Run SSMS 2012 as administrator.

3 - Run SQL query of the form -

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'), 
    (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf') 
    FOR ATTACH;