Attaching db on secondary drive.

Question

Attaching db on secondary drive.

chexwebson

SSC Veteran

Points: 215
More actions
August 10, 2005 at 10:53 am

#87091

I just moved to the June CTP and was re-attaching my dbs which are located on C: and D: The ones on C:, no problem, however when I try to attach those on D:, I get an error.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\Microsoft SQL Server\MSSQL\Data\FBC.mdf'. (Microsoft SQL Server, Error: 5123)
I took one of the smaller dbs on D and moved it to C and it attached no problem. Any thoughts? BTW, this worked fine in the April CTP.

cd
August 15, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Is SQL Server running under the same account as it did before you installed the new version?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

chexwebson SSC Veteran Points: 215 More actions · Answer 2

chexwebson

SSC Veteran

Points: 215

August 15, 2005 at 8:18 am

#582168

Yes, everything is setup as before.

cd

Tom3w Default port Points: 1417 More actions · Answer 3

but may be related. No solution yet.

Getting very similar situation when trying to make a new database.

SQL Server 2005 just installed (after uninstalled 2000 but left some shared files). Machine has two drives, C: and E:, both plenty of space & user has Full Control of both drives from Windows' perspective.

USE master;

GO

DBCC

TRACEON(1807) -- no help - tried per http://www.yafla.com/dforbes/2005/12/01.html

GO

IF

DB_ID (N'SalesEdb') IS NOT NULL

DROP DATABASE SalesEdb;

GO

-- Get the SQL Server data path

DECLARE

@data_path nvarchar(256);

SET

@data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf',

LOWER(physical_name)) - 1)

FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1);

-- got C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

SET

@data_path = 'E:\MEDIFILES\'

-- execute the CREATE DATABASE statement

EXECUTE

('CREATE DATABASE SalesEdb

ON
( NAME = Sales_dat,
FILENAME = '''

+ @data_path + 'saledat.mdf'',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = '''

+ @data_path + 'salelog.ldf'',

SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )'

);

GO

1802, Level 16, State 4, Line 1

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Msg 5123, Level 16, State 1, Line 1

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\MEDIFILES\saledat.mdf'.

Works for any path on C: drive. Fails for any path on E: drive.

Let me know if you have any insight into this or the original situation in this topic. Thanx.

Tom3w Default port Points: 1417 More actions · Answer 4

Solved CREATE DATABASE on other drive. Permissions issue.

Windows Start -> Administrative Tools -> Component Services -> Services (local) -> MSSQLServer, right clicked Properties.

LogOn was `Local System account`, changed to `This account` with proper Domain\User having sysadmin privileges.

Exited from Managment Studio, went back in with Windows authorization as that user.

CREATE DATABASE then worked for the E: drive.

Note: during install, under Components to Install there is an Advanced option which allows browse to set dbs default to a different drive.