Reputation: 31
First of all "Northwind" and "AdventureWorksDW2008" databases attached without problem, but "AdventureWorks2008" fails with the following error.
//Msg 5120, Level 16, State 105, Line 1
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.)".
Msg 5105, Level 16, State 14, Line 1
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.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorks2008'. CREATE DATABASE is aborted.
I did not use the default database instance "MSSQLSERVER" during install.
So where is it finding this path "C:...\MSSQL10.MSSQLSERVER...\Documents"?
Upvotes: 3
Views: 7876
Reputation: 1
In my case the problem had to do with the file I was using and the version of SQL. I have installed SQLServer2008R2 and the file I was using was AdventureWorks2008_Data.mdf. I downloaded the one from here http://msftdbprodsamples.codeplex.com/releases/view/59211 (AdventureWorks2008R2_Data.mdf) and all is fine now.
Upvotes: 0
Reputation: 2197
Solved it by below ways:
I fixed it As the .mdf and .ldf files where on different drives, it was giving some wierd error so i attached only mdf files and the server created .ldf files on the same drive as mdf. this way i could attach it but then i took the db offline copied the log from D:\ to E:\ and then brought the db online. it worked.
and sometimes you can :
try login with Windows Authentication and
Start SQL Server manager as Administrator (right-click on the program, choose "Start as administrator")
Upvotes: 1
Reputation: 31
It was appearing to work, but becoming Suspect after SQL service restart. SQL Log said:
Message FILESTREAM's file system log record 'placeholder.txt' under log folder '\?\C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents\$FSLOG' is corrupted.
Combining various suggestions found above, I got it to work (and survive service-restart without going Suspect) by:
Grant SQL service account FULL permissions to the MDF & LDF files and the Documents folder BEFORE copying them into DATA.
Delete C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents\$FSLOG\placeholder.txt (R2 BOL hints at this, but directs it at the wrong level).
CREATE DATABASE [AdventureWorks2008] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008_Data.mdf' ), ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008_Log.ldf' ) , FILEGROUP [PRIMARY] CONTAINS FILESTREAM DEFAULT ( NAME = N'Documents', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents' ) FOR ATTACH_REBUILD_LOG
Upvotes: 1
Reputation: 11
just like SQL Guy! i enabled FILESTREAM from configuration manager=>services=>yourinstance=>right-click=>properties=>advanced and then i you can remove the necessity of the log file from the attachement menu ( right click on database in sql => attach...). and it worked!
Upvotes: 1
Reputation: 1
This worked for me:
Software used-SQL Server 2008 Express R2, Windows Vista OS, Adventureworks2008R2.mdf
Program files(X86)\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
folder (or similar location
for your operating system)Upvotes: 0
Reputation: 11
This is my solution for SQL2008R2 :
Upvotes: 1
Reputation: 1
This is the only way I could get it to work. (Install without filestream) Go to Download 'AdventureWorks 2008R2 OLTP Script'
Unzip and open the instawdb.sql script. Remember to set the variables at the top of the script. Run the script. This should bulk load all the data as well. Good luck!
Upvotes: 0
Reputation: 46
First of all you need to enable the FILESTREAM usage:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
After that you can create the DB:
USE [master]
GO
CREATE DATABASE [AdventureWorks2008] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\AdventureWorks2008_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\AdventureWorks2008_Log.ldf' ),
FILEGROUP [PRIMARY] CONTAINS FILESTREAM DEFAULT
( NAME = N'Documents', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLTRAININGKIT\MSSQL\DATA\Documents' )
FOR ATTACH
GO
Change the TRAININGKIT instance name for your instance name and check the paths after that run with sysadmin privileges.
Upvotes: 1
Reputation: 21
go to sql server configuration manager sql server services SQL Server(MSSQLSERVER) change the "log on as" to "local system"
Upvotes: 2
Reputation: 1
Seems like many are having this issues, including me, and including the guy who logged this bug with MS Connect:
http://connect.microsoft.com/SQLServer/feedback/details/567193/adventureworks-2008-fails-to-attach
Upvotes: 0