I'm attempting to attach a copy of
msdb to my workstation SQL Server 2012 Developer Edition on Windows 8.1 in an attempt to mimic the problem detailed in this question where the OP is attempting to recreate missing jobs out of a foreign msdb.
I performed the following actions:
use master; backup database msdb to disk='C:\sqlserver\test.bak'; go restore database msdbtest from disk='c:\sqlserver\test.bak' with move 'MSDBData' to 'C:\SQLServer\Data\msdbtest_data.mdf' , move 'MSDBLog' TO 'C:\SQLServer\Logs\msdbtest_log.ldf'; go
I then stopped the SQL Server Service, made a copy of
C:\SQLServer\Logs\msdbtest_log.mdf, and restarted SQL Server.
I then performed the following:
drop database msdbtest; go create database msdbtest on ( name='msdbtest_data' ,filename='c:\sqlserver\data\msdbtest_data.mdf' ) log on ( name='msdbtest_log' , filename='c:\sqlserver\log\msdbtest_log.ldf' ) for attach;
This results in:
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "c:\sqlserver\data\msdbtest_data.mdf". Operating system error 5: "5(Access is denied.)".
SQL Server is configured with the default out-of-the box settings for the service account:
Security effective access for the SQL Server service account on
I am at a total loss; I think the error being returned is misleading me. Perhaps I misunderstand the purpose of
CREATE DATABASE ... FOR ATTACH?
As far as I can tell, SQL Server should certainly have access to the file. The folder
C:\SQLServer\Data is used by all the other user databases currently attached to the instance.
SELECT @@VERSION returns:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )