I wrote this bit of code a long while back when I inherited a school that had failing Sims.net backups as a way to take a nightly copy of their database to an onsite NAS. Customise it with your server and login details and set it to run on a schedule.

rem @ECHO OFF
CD\
C:
REM ———————————–
REM Set Environment Variables
REM ———————————–
SET SQLDIR=”D:\Program Files\Microsoft SQL Server\MSSQL13.SIMS2016\MSSQL\Binn”
SET SQLSERVER=server\SIMS2016
SET SIMSDB=SIMS
SET SIMSUSER=
SET SIMSPASS=
SET DBATTACH=”D:\Program Files\Microsoft SQL Server\MSSQL13.SIMS2016\MSSQL\Binn\DBAttach.exe”
SET BACKUPDIR=”D:\Program Files\Microsoft SQL Server\MSSQL13.SIMS2016\MSSQL\Backup”
SET NASSERVER=\\nas\simsbackup
SET SCRIPTDIR=C:\SimsBackup
SET NASDIR=Z:
SET LOGNAME=backup_log.txt
SET KEEPDAYS=-5
SET KEEPDAYSNAS=-30

net use %NASDIR% %NASSERVER% /PERSISTENT:YES

REM ———————————–
REM Housekeeping
REM ———————————–
DEL %SCRIPTDIR%\%LOGNAME% /F /Q
FORFILES /P %BACKUPDIR% /M *.bak /C “cmd /c del @path /F /Q” /D %KEEPDAYS% >>%SCRIPTDIR%\%LOGNAME%
FORFILES /P %NASDIR% /M *.bak /C “cmd /c del @path /F /Q” /D %KEEPDAYSNAS% >>%SCRIPTDIR%\%LOGNAME%

REM ———————————–
REM Do Backup
REM ———————————–
%DBATTACH% /user:%SIMSUSER% /password:%SIMSPASS% /SERVER:%SQLSERVER% /DATABASE:%SIMSDB% /BACKUP /AUTO

REM ———————————–
REM Move Backup to NAS
REM ———————————–

ROBOCOPY %BACKUPDIR% %NASSERVER% *.BAK /R:1 /W:1 >>%SCRIPTDIR%\%LOGNAME%
EXIT

Found priceless insights in this blog? Support the author’s creativity – buy them a coffee!

Leave a Reply

Your email address will not be published. Required fields are marked *