Sql-server – Backup SQL Server To NAS

backupsql server

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.

Best Answer

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
Related Question