Question

In-efficient on Fri, 02 May 2014 20:30:24



Dear Helper:

I am trying to study to take the Exam 70-433; however, upon attaching AdventureWorks2008 database, the db fails. I exhausted every possibility there is online before I submitted this question: I changed the service from 'Network Service' to 'Local System', I enabled FILESTREAM, I moved the file placeholder.txt file one folder up... I read all the forums and I tried all the solutions with a hope that they will work for me but none of the solutions worked for me. It generates the same error every time I try to attach it even if I deleted the existing db. All the other databases: AdventureWorksDW2008 and Northwind attached successfully. I am very curious what made this db, AdventureWorks2008 , not to successfully attache. I tried to attach on and Enterprise SQL Server 2008 and 2008R2 but it did not attach neither on 2008 nor on R2. Bellow is the error generated as I was trying to attach the AdventureWorks2008 database.

Thank you very much for your anticipated assistance. 

Daniel. 

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'ServerName\InstanceName'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&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:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents". Operating system error 2: "2(The system cannot find the file specified.)".
A file activation error occurred. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Could not open new database 'AdventureWorks2008'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5120)

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

------------------------------
BUTTONS:

OK
------------------------------


Sponsored



Replies

In-efficient on Fri, 02 May 2014 20:49:53


Thank you for anyone who was trying to respond to my question. I just ran the script bellow and it seemed to solve the problem. 

Thanks.

USE [master]
GO
CREATE DATABASE [AdventureWorks2008] ON
( FILENAME = N'C:\DATA\AdventureWorks2008_Data01.mdf' ),
( FILENAME = N'C:\LOG\AdventureWorks2008_Log01.ldf' ),
 FILEGROUP Documents CONTAINS FILESTREAM (NAME = Documents, FILENAME=N'C:\DATA\Documents')
 FOR ATTACH
GO 

Kalman Toth on Fri, 02 May 2014 21:00:16


Download page for: AdventureWorks Databases – 2012, 2008R2 and 2008

Just follow the instruction for installation.

In your case, I don't understand why you had to create a database explicitly.

In-efficient on Sat, 03 May 2014 03:50:46


Hi Kalman,

The AW2008 db will not attach at all. I spent almost a week to attach the db using GUI but it kept on failing. The last resort I had was to ask you guys a question. I did follow the instructions and made sure the physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents' was in the correct place and I do not know why it kept on failing. I had AW2008 installed and dropped the db since AW2008 in the Exam 70-433 has some new tables, I wanted to study without going creating the tables myself.  

Thanks for the help.

Daniel.