Backing up Capita Sims.net using cmd and dbattach
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