SQL Server 2005 Standard & Partitions

[25degc]

With SQL Server 2005 Standard Edition it seems I am unable to move the database location from the default c: drive to a different partition e.g. d:\. Is this a feature of Standard Edition - do I need Enterprise to be able to do this? Or is there a setting somewhere that I can change?

TIA

 

[mrdenny]

With any edition of SQL you can put databases on a local drive that is attached to the server.

How are you trying to move the database?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[25degc]

Thanks for your response.

I have detached the database, moved the physical files on to d:\ and then tried to attach, but the attach dialog box does not allow me to browse any partition other than c:.

 

[mrdenny]

Is the D drive a local drive or a network share?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[25degc]

Local. I have now resolved - although I could read/write to d:\ through explorer, it seemed that any app couldn't access it. I reformatted the drive, and all is OK.

Thanks for you help though.

 

[yetanotherjo]

Seeing as you've solved your problem 25degc, I'll highjack this as I have a similar problem

We have a local partition which MSDE can see (it backs up to it actually), but SQL Express (named instances of both) can't see. There's basically C:, D:, E: and Express can see only C: and D:. IT man assures me they're all set up the same way. Any ideas anyone?

 

[mrdenny]

I would assume that the E drive is FAT32 not NTFS. That was probably 25degc's problem as well.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yetanotherjo]

Hi MrDenny. Thanks for taking this one on. Apparently "All drives on the machine are healthy NTFS partitions!" (indignant IT man). Next question? (sorry, I know just enough about the IT side of things to understand baby-speak explanations and get into all sorts of trouble).

 

[mrdenny]

Log into the SQL Express instances. Run xp_fixeddrives. This will query the system and see if it can see the drive at all.

If that can see the E drive try running this.

exec xp_subdirs 'e:\'

This will pull the drive structor of the e drive.

My next guess would be that what ever NT account the SQL Express servers are running under is different that the account that the MSDE server is running under.

You'll want to make sure that the SQL Account has full control of the E drive.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yetanotherjo]

xp_fixeddrives returns only the 2 drives, the folders are all visible using xp_subdirs.

The accounts should be the same - same server, same user logged in for install. I give up...nothing is ever easy in this place. Maybe something's gone screwy with user permissions.

 

[mrdenny]

The GUI uses xp_fixeddrives to find the drives that it needs to display in the GUI. If you run xp_fixeddrives in the MSDE instance I assume that it can see all the drives?

As far as I know there is no way via permissions to hide a drive completly.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yetanotherjo]

Ok, I've wasted far too much time on but it's driving my nuts and the IT guy is accussing me of just being stubborn but....

This is the error I get running a restore (or backup) using a loggin which has full admin rights to the server - I checked that the user could write/delete files from the offending folder in Explorer:

"Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'E:\dbbackup\EasternGroup_Database2K5_20060624.BAK'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally."

By the by, yes, MSDE can see all 3 drives using with xp_fixeddrives.

Thought (I don't often have these!) - I think this might not be Express sp1 they're running, but the original version. I'll get them to install that and see how we go.

 

[mrdenny]

That means that the account that is running the SQL Server Express service doesn't have access to the file. It's an NTFS issue on the E drive.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yetanotherjo]

That's what I figured. I had assumed the SQL Server Express service was using the same account as was logged onto the server machine though (which has full access to read/write/delete files in Explorer) - is it possibly using something else and how do I check what it is actually using?

Sorry for taking up so much of your time on this but I'm loathe to migrate the database until I can solve this. I know it'll come back and bite me if I don't.

 

[kmkeshav]

I was facing the similar problem and solved it by granting full control to the group SQLServer2005MSSQLUser$<machine-name>$SQLExpress for the folder/drive that contains the database.

-Keshav / IT Consultant

 

[mrdenny]

You can see what account is running the services in the services control pannel or by using the SQL Server Configuration Manager.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yetanotherjo]

Thanks guys. I had to leave this for a while, will persevere another day.

 

[yetanotherjo]

Update: If I set the startup login to Local Server instead of Network Service, xp_fixeddrives returns all drives. If anyone can explain this to me, please do. Network Services was the default on installation (I've just installed sp1).

I can open a bak file on the offending drive now too but haven't done any other testing - my eyes are falling out of my head and my brain went to bed about 1/2 hour ago...the rest of me is about to follow.

 

[mrdenny]

That's strange that it was running under the Network Service account. That's not a default account that SQL would run under. That account probably didn't have access ot the drive would be my best guess.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com