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?
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?
ASKER
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.
I always connect as administrator.
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
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
ASKER
Thanks for the reply. I'll try your suggestion tomorrow at the office.
ASKER
When I try to create a new database I can only see the C-drive.
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
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
ASKER
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.
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'
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'
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
No worries at all and your most welcome
its good you got it working.
its good you got it working.
it might not have access on other drives in this server