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 have seen that there are similar questions like this but I tried all of them and my problem still exist. I want to backup a db.

  1. I created a shared folder on NAS
  2. I can reach this shared folder from windows explorer on DB server like \\10.1.1.x\yedek_db. I can create and delete files in this folder.
  3. I checked all the services related to SQL Server. Previously they were running using network service account and I changed it to domain administrator (which has full access to the NAS folders) and restarted the services.
  4. I executed the following command on command line

    net use H: \\10.1.1.x\yedek_db
    

    to map the NAS folder to H:. After this I can reach that folder by typing h:.

  5. I created a backup device on SQL Server which is named NAS_YEDEK_DB. The drop and create script is below.

    IF  EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'NAS_YEDEK_DB')
    EXEC master.dbo.sp_dropdevice @logicalname = N'NAS_YEDEK_DB'
    GO
    
    EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'NAS_YEDEK_DB', @physicalname = N'H:\'
    GO
    
  6. I try to backup using this backup device by the following code:

    BACKUP DATABASE evrak 
    TO NAS_YEDEK_DB 
    WITH DESCRIPTION = 'Test DB Backup';
    

But I get this error :

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'NAS_YEDEK_DB(H:\)'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I will be very happy if I can solve this problem.

share|improve this question

migrated from stackoverflow.com May 25 '13 at 5:14

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

    
What OS are you using? Is that folder set to read/write or only read (folder permissions)? –  Marian May 25 '13 at 8:33

2 Answers 2

up vote 1 down vote accepted

Your syntax is wrong for adding a backup device.

When you create a backup device in SQL Server you are actually creating the "bak" file as a media set.

Along with the suggestion from Bob that you should be using the full UNC path, your syntax should be changed to something like this:

IF  EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'NAS_YEDEK_DB')
EXEC master.dbo.sp_dropdevice @logicalname = N'NAS_YEDEK_DB'
GO

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'NAS_YEDEK_DB', @physicalname = N'\\10.1.1.x\yedek_db\YEDEK_DB_backup.bak'
GO
share|improve this answer
    
Thank you Shawn my problem is solved :) –  Ferda-Ozdemir-Sonmez May 29 '13 at 7:42
    
I accepted your answer but it still looks like not answered. What should i do? –  Ferda-Ozdemir-Sonmez May 29 '13 at 9:26
    
Well if it solved your problem I am not to sure how it is not your answer. You would need to expand more on how it is not and then don't mark it as an answer. –  Shawn Melton May 29 '13 at 14:19

The likelihood is that the H: drive is unavailable to the SQL service account as it will be local to your administrator account.

Create the backup device using the UNC path rather than the drive letter mapping. i.e. \\10.1.1.x\yedek_db.

Alternatively you could try logging in as the SQL Service account and mapping the drive letter, but this is a less optimal approach.

share|improve this answer
    
I tried to do like you suggested I got ann error unluckily Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'NAS_YEDEK_DB(\\10.1.1.x\yedek_db)'. Operating system error 32(failed to retrieve text for this error. Reason: 15105). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. –  Ferda-Ozdemir-Sonmez May 25 '13 at 8:28
    
should that really be 10.1.1.x or should the x be a number? –  Bob Vale May 26 '13 at 14:28
    
I ran SQL Service using domain administrator account. I assume that H: drive should be available for domain administrator, isn't it? –  Ferda-Ozdemir-Sonmez May 27 '13 at 5:56
1  
Does the H: mapping survive a reboot? Using a drive mapping is really too volatile and you should use a unc path instead. 10.1.1.x does not look like a valid ip address are you sure this is correct? Additionally can administrator write and modify files to this location. Try saving a notepad document to test. –  Bob Vale May 27 '13 at 9:14

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.