Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've noticed strange issue after this weekend. Maybe someone could explain this situation. I've got SQL Server 2008 placed on Windows Server 2008 with read/write permission to catalog ( let say Workgroup ) on network drive ( it is used to make backups )

To make catalog Workgroup visible for SQL Server, I've used this syntax:

  xp_cmdshell 'net use Y: \\network-disk-server\Workgroup [password here] /[user here] /persistent:no'

And everything was fine. I could make backups on Workgroup. But after Sunday SQL Server 2008 couldn't see mapped drive anymore. After making net use syntax again, I get error:

Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed. Disconnect all previous connections to the server or shared resource and try again.

So I think that this mapped drive still exists, but somehow it is not seen by SQL Server anymore.

I am using Domain user service with read/write permissions to workgroup folder and sysadmin fixed role on MSSQL.

share|improve this question
3  
Why backup to a mapped drive when you can just backup to a UNC path directly? –  Eric Humphrey - lotsahelp Aug 7 '12 at 14:27
    
Because UNC path won't be seen from SSMS interface, when I will right click on DB, and chose backup/restore. It is nessesary to give easy access to backups for workers, who don't know SQL stmt very well. –  Norbert Ozdoba Aug 8 '12 at 11:57
add comment

migrated from stackoverflow.com Aug 7 '12 at 14:18

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 0 down vote accepted

I've got explanation ( tricky ). Maybe someone could get use of it:

Administrator might set maximum number of connections from one account to external resources ( in this example - drive ). When the number of connections is reached, You don't have access to it ( and from SSMS it is not visible ).

Recommendation:

Check, using for example sp_who, do You have any sleeping processes on Database Engine Service Account. That requests could hang on external resources, and block access for other requests.

share|improve this answer
add comment

To Mapped drive for Microsoft SQL Server 2008, you can use the following script. Make changes as per your requirement. Hope it will help.

use master go

EXEC SP_ADDUMPDEVICE 'DISK' , 'YourBackupDeviceName' , 'D:\SQLBACKUPS\YourDb.BAK'

GO

select * from sys.backup_devices

go

BACKUP DATABASE YourDB TO YourBackupDeviceName WITH DESCRIPTION = 'DB Full Backup' --WITH Format

GO

You can restore the Database by simply access the path.

RESTORE DATABASE YOurDb FROM DISK='D:\SQLBACKUPS\YourDb.BAK' WITH REPLACE

GO

Or You can use the mapped device name for accessing the backup

RESTORE DATABASE JPCHAUHAN FROM YourBackupDeviceName WITH REPLACE

GO

To drop the backup device you can choose the following sp.

EXEC SP_DROPDEVICE 'YourBackupDeviceName';

go

share|improve this answer
    
Thank You for Your answer. Unfortunately backup device is obsolete feature, and it won't solve problem ( security issue ). –  Norbert Ozdoba Aug 8 '12 at 11:58
add comment

I have this problem often as I use a similar method in some environments. My situation is a bit more twisted than most but what I find useful is to do a

  xp_cmdshell 'net use Y: /delete /y'

Try it after that. If it still doesn't work, open up a powershell/dos window and type:

net use

If you see the Y: drive, delete it.

Do the same thing this time opening up powershell or dos by right clicking on the icon and select 'run as administrator. Do a net use again. This time you'll see it under a different context. Delete it here as well. Wait a few minutes and then try again.

If it doesn't work you've got a similar problem as I do.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.