Your NAS should have a static IP, or it should be given an IP reservation through the DHCP service so that it always gets the same IP. It won’t be reliable otherwise. The error message you are receiving is likely a result of this, since the host IP of the NAS has changed, and thus the Drive Mapping is invalid. It states: cannot find the path specified.
Once that is done, it doesn’t really matter much whether you refer to the NAS path via UNC or via Mapped Drive.
UNC:
\\NASWD3TB01\Private\BACKUPS\POS\DBBU\
or
\\192.168.0.200\Private\BACKUPS\POS\DBBU\
Mapped Y: drive to \\NASWD3TB01\Private share:
Y:\BACKUPS\POS\DBBU\
Here is the BAT file that I use to run DB Backup. You will need to make modifications to the parameters set near the top. It also assumes your system Short Date/Time Format is YYYY-MM-DD and HH:mm:ss. This is required to build a proper date/time-stamped file name, for example:
SambaPOS4_2015-01-31_144737.bak
BAT file (fire this with a Start Process Action):
@echo off
cls
::
:: Set Path Information
::
::set pathSQL="D:\Programs\Microsoft SQL Server\120\Tools\Binn\osql.exe"
set pathSQL="D:\Programs\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\sqlcmd.exe"
set pathCOMP="C:\Program Files\WinRAR\Rar.exe"
set SERVERINSTANCE=localhost\SQLEXPRESS
set DBNAME=SambaPOS4
set pathBU=D:\Programs\POS\DBBU\
set pathNAS=\\NASWD3TB01\Private\BACKUPS\POS\DBBU\
::
:: Go to Backup Location
::
D:
CD %pathBU%
::
:: Set LOGFILE, ERRMSG, DateTime, BackupName
::
set LOGFILE=DBbackup.txt
set ERRMSG=
set dateTimeDB=%date%_%time:~0,2%%time:~3,2%%time:~6,2%
:: replace space with 0 :: @CALL set variable=%%variable: =0%%
@CALL set dateTimeDB=%%dateTimeDB: =0%%
set BackupName=%DBNAME%_%dateTimeDB%.bak
set CompressedName=%DBNAME%_%dateTimeDB%.rar
::
:: Start Logging
::
set dateTime=%date%_%time:~0,2%.%time:~3,2%.%time:~6,2%.%time:~9,2%
:: replace space with 0 :: @CALL set variable=%%variable: =0%%
@CALL set dateTime=%%dateTime: =0%%
@echo. >> %LOGFILE% 2>&1
@echo ******************************************************* >> %LOGFILE% 2>&1
@echo -- BAT BEG --------------------- %dateTime% >> %LOGFILE% 2>&1
::
:: Check parameters to ensure they are Ok
::
IF "%SERVERINSTANCE%" == "" set ERRMSG=%ERRMSG% ERROR:SERVERINSTANCE is BLANK
IF "%DBNAME%" == "" set ERRMSG=%ERRMSG% ERROR:DBNAME is BLANK
IF "%BackupName%" == "" set ERRMSG=%ERRMSG% ERROR:BackupName is BLANK
IF NOT EXIST %pathSQL% set ERRMSG=%ERRMSG% ERROR:pathSQL is Invalid
IF NOT EXIST "%pathBU%" set ERRMSG=%ERRMSG% ERROR:pathBU is Invalid
IF NOT EXIST %pathCOMP% set ERRMSG=%ERRMSG% ERROR:pathCOMP is Invalid
IF NOT EXIST "%pathNAS%" set ERRMSG=%ERRMSG% ERROR:pathNAS is Invalid
set errorlevel=
::
:: If there is a problem with the given parameters, %ERRMSG% will contain a value, and we abandon the script
::
IF "%ERRMSG%" NEQ "" GOTO ERRORPROCESS
::
:: If parameters Ok, we continue...
::
@echo.
@echo DB Backup starting soon...
timeout 10
@echo.
::
:: Run BACKUP Commands
::
@echo -- DATABASE BACKUP BEG ------- %dateTime% >> %LOGFILE% 2>&1
@echo Backing up database [%DBNAME%] to %pathBU%%BackupName%
:: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -E -Q "BACKUP DATABASE mydatabase TO DISK='C:\tmp\db.bak' WITH FORMAT"
@echo sqlcmd.exe -S %SERVERINSTANCE% -E -Q "BACKUP DATABASE [%DBNAME%] TO DISK='%pathBU%%BackupName%'" >> %LOGFILE% 2>&1
%pathSQL% -S %SERVERINSTANCE% -E -Q "BACKUP DATABASE [%DBNAME%] TO DISK='%pathBU%%BackupName%'" >> %LOGFILE% 2>&1
@set dateTime=%date%_%time:~0,2%.%time:~3,2%.%time:~6,2%.%time:~9,2%
@CALL set dateTime=%%dateTime: =0%%
@echo -- DATABASE BACKUP END ------- %dateTime% >> %LOGFILE% 2>&1
::
:: Run COMPRESS Commands
::
@echo -- COMPRESS BEG ------- %dateTime% >> %LOGFILE% 2>&1
@echo Compressing %BackupName%
@echo Compressing %BackupName% to %CompressedName% >> %LOGFILE% 2>&1
::rar a -ep -idcpd -m5 archivename filestoarchive
%pathCOMP% a -ep -idcp -m5 %pathBU%%CompressedName% %pathBU%%BackupName% >> %LOGFILE% 2>&1
@echo Deleting %BackupName%
@echo Deleting %pathBU%%BackupName% >> %LOGFILE% 2>&1
del %pathBU%%BackupName%
@set dateTime=%date%_%time:~0,2%.%time:~3,2%.%time:~6,2%.%time:~9,2%
@CALL set dateTime=%%dateTime: =0%%
@echo -- COMPRESS END ------- %dateTime% >> %LOGFILE% 2>&1
::
:: Run COPY Commands
::
@echo -- COPY BACKUP BEG ------- %dateTime% >> %LOGFILE% 2>&1
@echo Copying %CompressedName% to %pathNAS%
@echo Copying %pathBU%%CompressedName% to %pathNAS%%CompressedName% >> %LOGFILE% 2>&1
copy %pathBU%%CompressedName% %pathNAS%%CompressedName% >> %LOGFILE% 2>&1
@set dateTime=%date%_%time:~0,2%.%time:~3,2%.%time:~6,2%.%time:~9,2%
@CALL set dateTime=%%dateTime: =0%%
@echo -- COPY BACKUP END ------- %dateTime% >> %LOGFILE% 2>&1
::
:: We are almost done. Test for Errors.
::
IF [%ERRMSG%] == [] GOTO finished
:ERRORPROCESS
@echo %ERRMSG%
@echo %ERRMSG% >> %LOGFILE% 2>&1
@set dateTime=%date%_%time:~0,2%.%time:~3,2%.%time:~6,2%.%time:~9,2%
:: replace space with 0 :: @CALL set variable=%%variable: =0%%
@CALL set dateTime=%%dateTime: =0%%
@echo -- BAT END --------------------- %dateTime% >> %LOGFILE% 2>&1
:: Open the LOGFILE
@notepad %LOGFILE%
goto endoffile
:finished
@set dateTime=%date%_%time:~0,2%.%time:~3,2%.%time:~6,2%.%time:~9,2%
:: replace space with 0 :: @CALL set variable=%%variable: =0%%
@CALL set dateTime=%%dateTime: =0%%
@echo -- BAT END --------------------- %dateTime% >> %LOGFILE% 2>&1
:endoffile
@echo.
::
:: We are done. Uncomment the following lines if you wish.
::
::@notepad %LOGFILE%
@echo DB Backup Complete.
timeout 2
::pause
