SQL server backup to CIFS share - syntax issue ?

Alexandre Takacs
Alexandre Takacs used Ask the Experts™
on
I am trying to perform an SQL server "built-in" backup (part of a maintenance plan that was working so far) to a file share.

I understand that I have to use UNC in my file path and that the user running the SQL service should have access to the share - as such I have changed the DSQL service user to a domain user.

Checking my logs I see

Failed:(-1073548784) Executing the query "BACKUP DATABASE [model] TO  DISK = N'\\\\172.16.200...." failed with the following error: "Cannot open backup device '\\\\172.16.200.253\\Backup\\SQLDB\\model\\model_backup_2019_11_25_220003_1049249.bak'. Operating system error 67(The network name cannot be found.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Open in new window


Seems that it is actually an issue in finding the network share. This is how I have defined my destination :
server management
Isn't that the correct way of doing it ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Try mapping the network path to a mapped drive and use the Mapped drive path..
https://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-in-ssms/

Author

Commented:
hmm problem is that the mapped path will not be available to the user running the backup (unless I do a scripted map before I run it). Is that the best practice ?
Database Analyst
Commented:
From the error you posted above "..."BACKUP DATABASE [model] TO  DISK = N'\\\\172.16.200...."..." it looks like the path to the UNC share has two extra \\ in the string and please check your script for that.
Aside that...SQL Uses its own AD startup account to perform these backups no matter the user that's logged on and run the backups or even SQL jobs running under SQL Agent account will still use SQL Startup account so mapping a drive tor that SQL Startup AD login should be sufficient but this is definitely not mandatory needed assuming that SQL startup AD login has "FULL CONTROL" on that UNC path/CIFS share.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> problem is that the mapped path will not be available to the user running the backup

As mentioned by lcohan, just ensure that the startup account of SQL Server Agent service is having access to the share folder.
And use net use option if SSMS can't see the shared folder while configuring backups

Author

Commented:
Thanks - mapped the drive within the SQL service account and all works fine now

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial