Sql-server – Backup to unc path in workgroup environment

powershellsql server

I am having a problem backing up the database to the network path using dbatools.io Backup-DBAdatabase command.

This is a workgroup environment. The SQL Server runs under the NT Service\MSSQLSERVER account. The location I am trying to backup is a standalone NAS drive.

I mapped the network folder to the SQL Server machine but when I backup to that drive it fails with

WARNING: [14:25:08][Backup-DbaDatabase] Backup Failed |
System.Data.SqlClient.SqlError: Cannot open backup device
'K:\BackupTest\15Jan2021_12_34\full\sdsd_180_infras_b18c6_202101151425.bak'.
Operating system error 3(The system cannot find the path specified.).

If I use the UNC path I get

WARNING: [14:24:04][Backup-DbaDatabase] Backup Failed |
System.Data.SqlClient.SqlError: Cannot open backup device
'\\XX.XX.XX.XX\backup\BackupTest\15Jan2021_12_34\full\XX_180_infras_b18c6_202101151424.bak'.
Operating system error 1326(The user name or password is incorrect.).

I am aware the issue is due to the SQL Service account's lack of permission on the NAS folder not sure how can resolve it.

I really appreciate your help

Best Answer

the problem are not coming from the dba tools but from your network.

You can't mount a share on a local path. That path (K) can't be seen by sql server.

The unc path works with the user that is running the sql server service, in your case the computer account; but you can't authorise the compunter account in your nas under workgroup.

The only option i can suggest you to test is to change the sql server user to a local windows user and then create a user in the nas that match it in the username and password. Then you can try with the UNC path; but i'm not sure if it will work.

Related Solutions

Sql-server – Restoring database always throwing exception

If you want to restore a *.bak file onto a machine e.g. TESTSERVER, then that *.bak file needs to be

on a local drive of TESTSERVER, or:
on a share in the network that TESTSERVER can reach (a "public" share)

So if you have the *.bak file on your local PC MyPC, you can try to do a restore from \\MyPC\C$\ShareFolder\Backupfile.bak and this might or might not work.

But you cannot use C:\ShareFolder\Backupfile.bak since that will go to the server's C:\ drive and look for that file - and thus you get the error message and it cannot be found.

After all: your SQL Server instance is running on TESTSERVER - so if that process there executes a restore from the C:\ drive, that'll be the C:\ drive on that server machine - not your local PC! If that server process on TESTSERVER can reach your machine, then you might use the UNC path notation to your local disk - so that might work.

Sql-server – Backup failure in SQL Server 2005

Well, suppose you have a command to execute your backup like this

BACKUP DATABASE [naass] 
TO  DISK = N'C:\program files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\Naass.bak'' 
WITH NOFORMAT, INIT,  
NAME = N'naas-Complete Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

this will fail if the process that executes this command doesn't have the permissions to write in the mentioned folder. And Windows 7 (for very good security reasons) keeps the C:\program files folders and its subfolders write locked also to an administrator account.

You could mess with the default permissions and give to your folder above write permissions for your account but of course this is not the recommended way.
Much better, simply change the script to work on a different directory on your disks.

BACKUP DATABASE [naass] 
TO  DISK = N'D:\BackupSQL\naass.bak' 
WITH NOFORMAT, INIT,  
NAME = N'naas-Complete Database Backup'
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Of course this new directory should have the correct permissions.

Best Answer

Related Solutions

Sql-server – Restoring database always throwing exception

Sql-server – Backup failure in SQL Server 2005

Related Question