How to backup MSSQL to a password protected network folder?

Question

Database administration

How to backup MSSQL to a password protected network folder? There are backup plans. There is a NAS, there is a folder on it, access to the folder is by login and password.

Answer 1

How does SQL tell the username and password to access a folder?

As far as I understand you are trying to do through maintenance plan. And you instead do a cmd-script through the task scheduler. In a cmd script, you can easily register a network drive connection with a password,

net use p: \\192.168.1.100\backup [email protected]$$w0rd /USER:vpupkin

and then the database backup command.

set DATABASENAME=ИмяБазыДанных
set DATESTAMP=%DATE:~-4%-%DATE:~3,2%-%DATE:~0,2%-%time:~0,2%-%time:~3,2%
if "%time:~0,1%" == " " set DATESTAMP=%DATE:~-4%-%DATE:~3,2%-%DATE:~0,2%-0%time:~1,1%-%time:~3,2%
set BACKUPFILENAME=p:\%DATABASENAME%-%DATESTAMP%.bak
set OSQL="C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.EXE"
%OSQL% -E -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK='%BACKUPFILENAME%' WITH FORMAT"

Answer 2

connect as a network drive and run. You can connect the script for the duration of the backup, and then turn it off.