Backup SQL Server To NAS

Question

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.

Answer 1

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

Answer 2

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.