Modifying a SQL backup Script from Capita

[MrWu]

hi all,

 

wondering whether you folks can help

 

I have a script ran by Capita to back up the database weekly, it is taking up disk space and I would like the script just to backup daily and wipe the previous day backup. (since the daily one is backed up daily to another disk anyway, theres prob no need to keep mon-fri bak files on the SIMS server

 

I've asked Capita for a different script be they did not offer help..so I was hoping someone here can look at Capitas current script and suggest a modification? Cheers!

 

also is it safe to delete *.bak files directly on disk or need to go through SQL Studio?

 

 

 

 

@echo off

rem SIMS / FMS SQL 2012 Backup Script

rem © Capita Childrens Services 2013

 

 

rem Set these variables as required.

set BackupPath=E:\SIMSBackup

set SAPassword=?S1M123456?

set DBServer=localhost\sims2012

set DBAttachPath="E:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSSQL\Binn"

 

for /f "delims=" %%a in ('cscript //nologo "get weekday.vbs"') do (set day=%%a)

 

echo today is = %day%

 

 

SQLCMD -SLocalhost\SIMS2012 -USa -P%SAPassword% -Q"exec sims.sims.db_p_transfer_login"

 

 

echo Removing old %day% backup files if they exist...

if exist "%BackupPath%\%day%SIMS.BAK" del "%BackupPath%\%day%*.BAK"

 

echo Backing up SIMS...

%DBAttachpath%\DbAttach.exe /BACKUP /AUTO /SERVER=%DBServer% /DATABASE=SIMS /USER=sa /PASSWORD=%SAPassword% /PATH=%BackupPath%\%day%SIMS.BAK

 

 

echo Backing up SOLUS3...

%DBAttachpath%\DbAttach.exe /BACKUP /AUTO /SERVER=%DBServer% /DATABASE=Solus3_deployment_server /USER=sa /PASSWORD=%SAPassword% /PATH=%BackupPath%\%day%SOLUS.BAK

 

echo Backing up Discover...

%DBAttachpath%\DbAttach.exe /BACKUP /AUTO /SERVER=%DBServer% /DATABASE=Discover /USER=sa /PASSWORD=%SAPassword% /PATH=%BackupPath%\%day%Discover.BAK

 

"E:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSSQL\Binn\discover.backup.console.exe" /Operation=Backup /database=discover /Server=%DBServer% /username=sa /Password=%SAPassword% /BackupFilename=%BackupPath%\%day%discoverbk.dbk /Overwrite=True

 

 

 

echo Backup Complete.

 

 

exit

Edited November 15, 2016 by MrWu

[Banjo]

You can delete .bak files with explorer. Sometimes it's a good idea to have more than one days backup. E.g. Someone deletes all the assessment data and keeps quiet about it. A couple of days later someone else notices, then it's too late to restore. Its also a good idea to backup to another server in case of disc failure.

 

Why not change the backup path to a different server with plenty of disc space. Just map a drive to it and change the drive letter to point to it.

[MrWu]

That's a good idea...I assume you can do mapped drive via the script? (I remember you can't backup via mapped drives in SQL Studio..but will give this a try...thanks!)

[dry]

Would strongly recommend removing your SA password from the post you've written here (if that is it!)

 

Our backup scripts are scheduled tasks that run .bat files with the following contents:

 

if exist "BackupDirectory\MondaySIMS.bak" del "BackupDirectory\MondaySIMS.bak"
if exist "BackupDirectory\MondayFMS.bak" del "BackupDirectory\MondayFMS.bak"

"SIMSDBDirectory\MSSQL\Binn\DBAttach.exe" /BACKUP /AUTO /Server=SIMSservername\SQLInstance /DATABASE=SIMSDBName /USER=sa /PASSWORD=SAPassword /PATH=BackupDirectory\MondaySIMS.bak
"SIMSDBDirectory\MSSQL\Binn\DBAttach.exe" /BACKUP /AUTO /Server=FMSservername\SQLInstance /DATABASE=FMSDBName /USER=sa /PASSWORD=SAPassword /PATH=BackupDirectory\MondaySIMS.bak
EXIT

 

Scheduled task runs the above .bat file at x time for y day. .bat file copied (and amended suitably) for each day of the week.

[MikeW]

Here's ours if its any use - it keeps 7 days backups in an archive folder# Set Variables$server = 'SERVER\SQL'$user = 'sa'$password = 'PASSWORD'$date = (Get-Date).ToString("d-M-yyyy").Replace(",","-")# Clear Archive Folders Of Files Older Than 7 DaysGet-ChildItem –Path “Z:\Archive\Databases\SIMS” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-ItemGet-ChildItem –Path “Z:\Archive\Databases\FMS” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-ItemGet-ChildItem –Path “Z:\Archive\Databases\Discover” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-ItemGet-ChildItem –Path “Z:\Archive\Databases\SOLUS” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-ItemGet-ChildItem –Path “Z:\Archive\Folders” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-Item# Move Last Backup To The Relevant Archive folderMove-Item "Z:\Current\Databases\SIMS*" "Z:\Archive\Databases\SIMS"Move-Item "Z:\Current\Databases\FMS*" "Z:\Archive\Databases\FMS"Move-Item "Z:\Current\Databases\Discover*" "Z:\Archive\Databases\Discover"Move-Item "Z:\Current\Databases\SOLUS*" "Z:\Archive\Databases\SOLUS"Move-Item "Z:\Current\Folders\*.zip" "Z:\Archive\Folders"# Shrink Databases To Keep The File Size LowSet-Location -Path 'F:\Apps\SIMS\Setups\ShrinkDBLog'.\ShrinkDBLog.bat $server SIMS.\ShrinkDBLog.bat $server FMSSTM.\ShrinkDBLog.bat $server Discover.\ShrinkDBLog.bat $server solus3_deployment_server# Transfer Users From Master DatabaseOSQL -S"$server" -U $user -P $password -Q"exec sims.sims.db_p_transfer_login"OSQL -S"$server" -U $user -P $password -Q"exec fmsstm.sims.db_p_transfer_login"# Backup The Sims Database Into The 'Current' FolderSet-Location -Path 'C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn'.\DbAttach.exe /server=$server /database=SIMS /user="$user" /password="$password" /backup /auto /path="Z:\Current\Databases\SIMS-$date.bak"# Backup The Discover Database Into The Current Folder.\Discover.Backup.Console.exe /operation=backup /database=Discover /username="$user" /password="$password" /server=$server /backupfilename=Z:\Current\Databases\Discover-$date.dbk# Backup The FMS Database Into the 'Current' FolderSet-Location -Path 'C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn\FMSSQL'.\DbAttach.exe /server=$server /database=FMSSTM /user="$user" /password="$password" /backup /auto /path="Z:\Current\Databases\FMS-$date.bak"# Backup The SOLUS Database Into the 'Current' FolderSet-Location -Path 'C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn'.\DbAttach.exe /server=$server /database=solus3_deployment_server /user="$user" /password="$password" /backup /auto /path="Z:\Current\Databases\SOLUS-$date.bak"#Wait For 15 Minutes Whilst Database Backups CompletesStart-Sleep -s 900# Zip Files To Save Space And Then Delete OriginalsSet-Location -Path C:\Windows\System32$files = Get-Item "Z:\Current\Databases\*.bak" | Foreach-Object {$_ -replace ".bak", ""}foreach ($file in $files) { .\7za a -tzip $file'.zip' $file'.bak' }Remove-Item "Z:\Current\Databases\*.bak"# Zip SIMS And DOCSTORAGE Folders.\7za.exe a -tzip Z:\Current\Folders\DOCSTORAGE-$date.zip F:\DOCSTORAGE.\7za.exe a -tzip Z:\Current\Folders\SIMSFILES-$date.zip F:\Apps\SIMS -mx0 -xr!Solus3 -xr!Setups

[TwistedHelixis]

It is possible to delete them x days after creation using 2 lines of powershell script. Unfortunately I did not create the script and don't think I can share it.

[zag]

I just use this bit of software, does the job from a GUI and can upload to google drive or onedrive.

 

Also auto cleans up the backup, or even cloud only so you don't loose space.

 

https://sqlbackupandftp.com/

 

Only costs 25 quid.

Edited November 15, 2016 by zag

[Banjo]

Or you can pay and go offsite. Redstor Backup Pro | capita-sims.co.uk Edited November 15, 2016 by Banjo

[vikpaw]

Or search EduGeek for a utility called TidyBackups I think it's still usable and relevant.

 

I hope everyone has obfuscated any passwords included in scripts. If not please pm me or report your own post and one of the mod team can help.

[Knil92]

We use tidy backups to delete any backups older than 30 days, then we just use a batch script to run dbattach to backup the databases in auto mode. I think all in all its like 4 or 5 lines of script to backup and 1 line for the tidy backups