0

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

1

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.

1
  • This is wrong, the user for SQL Server was not the $computer account (SYSTEM), it was NT Service\MSSQLSERVER – Charlieface Jan 17 at 2:58
0

Sorted it. Mapped the network share to the workspace of the SQL server using the code below

sp_configure 'show advanced options', '1'
RECONFIGURE
sp_configure 'xp_cmdshell', '1' 
RECONFIGURE
set @cmd='net use P: \\XXX.XXZ.XYZ.010\backup\ Password /user:XXX.XXZ.XYZ.010\NASAccount';
exec master..xp_cmdshell @cmd, no_output;

Then you should be able to backup to P:\

Every time the SQL Service restart the following script needs execting.

set @cmd='net use P: \\XXX.XXZ.XYZ.010\backup\ Password /user:XXX.XXZ.XYZ.010\NASAccount';
exec master..xp_cmdshell @cmd, no_output;
1
  • net use ... /persistent:yes should sort you out without needing to run it every startup. Keeping xp_cmdshell open is risky – Charlieface Jan 17 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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