SQL Server 2008 Express - See only C: drive for backups ans restores

Hi Experts,

We have several servers with SQL Server 2008 Express on it. All servers have more than one hard disk. The C disk is used for the OS, the other disks are data disk. So I create the backup devices for our databases on one of the data disks.
On one server I can't do this, because the data disks are not visible when I try to back up or restore a database.
When I select the database I want to backup and right click and select Task \ Backup, I can only add or select destinations on the C disk. The other disks are not visible. The same happens when I want to restore a backup from another disk.
What am I doing wrong?
NoraWilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AmmarRCommented:
check the permissions of the account that is assigned to the SQL Server service account.

it might not have access on other drives in this server
NoraWilAuthor Commented:
Thanks for the reply. I don't know if I understood what you're advising. I checked the security tab of the hard disks in the server. All values are the same for all discs. There's nothing special mentionned for SQL Server. There are only values for Adminsitrator (full access), System (full access) and Domain Users (Restricted access).
I always connect as administrator.
AmmarRCommented:
Are all other applications able to see the other Data drives except sql server.

i mean isnt there any kind of special security applied on these disks by your domain admins.

---

do this test

1. try creating a new database and in the screen click on path, and check do you see the other drives or not.


path.png
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

NoraWilAuthor Commented:
Thanks for the reply. I'll try your suggestion tomorrow at the office.
NoraWilAuthor Commented:
When I try to create a new database I can only see the C-drive.
AmmarRCommented:
Can you see other drives with other applications

i mean is it only SQL thats unable to see the other drives.

try using any other application and check are they able to see other drives
NoraWilAuthor Commented:
Yes of course, this is a file server with several hard disks. All of them can be and are being used. SQL server is the only application I use that shows only one hard disk. I'll send some screen dumps tomorow when I'm back at the office.
AmmarRCommented:
hi

can you try the following

BACKUP DATABASE DBName to disk='D:\yourDB.bak'
-- this should back up and store your database in D drive

try it with a small DB, and lets see , is the problem with SQL or just with SQL management studio
-----------

try this as well and see what results you get

exec master..xp_cmdshell 'dir c:'  
exec  master..xp_cmdshell 'dir d:'  
exec master..xp_cmdshell 'dir e:'  

and see what results you get in each statment,

you might need to enable xp_cmdShell

exec sp_configure 'xp_cmdshell', '1'

but re disable it after testing

exec sp_configure 'xp_cmdshell', '0'
AmmarRCommented:
are you using Cluster in sql

because i see many people are discussing the same problem, when using cluster

check this

http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/26c0efcd-88c4-4f8e-be9e-de4ad182374d/

solution
http://msdn.microsoft.com/en-us/library/ms177447.aspx
NoraWilAuthor Commented:
Hi,
Thanks for the good cares.
We don't use clusters.
I tried your suggestions. There are the results:

I gave the following commands:
backup database rambo to disk ='E:\rambo.bak'
go
exec sp_configure 'xp_cmdshell', '1'
go
exec master..xp_cmdshell 'dir c:'
go
exec master..xp_cmdshell 'dir d:'
go
exec master..xp_cmdshell 'dir e:'
go

These are the results:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'E:\rambo.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 1815).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

I tried E: and not D:, because D: is a DVD drive.

AmmarRCommented:
NoraWil:

i think we are back to the first comment i posted

you need to know the Account that your sql server is using and check its permission.

see the image below in my case i am using Local system,

i am sure you are using a different account may be the NETWORK SERVICE account or a domain account..

what ever account you are using you need to make sure it has read and write permissions on the E: drive
 
sql-service.png

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NoraWilAuthor Commented:
Sorry AmmarR,
You were right from the start. I checked the permissions at the wrong location. SQL server ran as a Network System. I changed it to Local System an now I can see the other drives.
Thank you very much for the help.
AmmarRCommented:
No worries at all and your most welcome

its good you got it working.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Productivity Apps

From novice to tech pro — start learning today.