After a recent hard drive failure (which annoyingly happened to contain our backups) I thought it wise to take a backup of the backups on to a different drive, and our new hard drive arrived today.
I didn’t want to spend any more money than I had to, and there’s other (and probably better) ways of doing this, but it works for us.
The batch file
For this part, we’re going to assume the following:
- Winrar is installed in C:\Program Files\Winrar
- Your timestamp is going to be in UK/24hour format (e.g. 2016-04-09_1903.rar)
- My SQL files are located in L:\SQL Backups\
- I want to place my archive in E:\SQL\
cd C:\Program Files\WinRAR
For /f "tokens=1-3 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%b-%%a)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
winrar a -rr10p -afrar -or -IBCK E:\SQL\%mydate%_%mytime%.rar "L:\SQL Backups\*.sql"
Scheduling the task
Now that we’ve got our batch file, we need to set a scheduled task for it, if you don’t know how. Google.
Trouble with running a batch file is you’ll get a black command prompt pop up every time it runs. Pretty annoying.
Here’s how to schedule a task with a hidden window with a snip of vbs.
Set WinScriptHost = CreateObject("WScript.Shell")
WinScriptHost.Run Chr(34) & "C:\SQL\SQLbackup.bat" & Chr(34), 0
Set WinScriptHost = Nothing
The only thing this assumes is your batch file is C:\SQL\SQLbackup.bat. Save this file as a .vbs somewhere and point your task to that. No more pesky prompts
So there we have it, not complicated, not glamorous, but it works for us, and maybe a starting point for you.
As for rotating the original SQL files. That’s handled by forfiles. Create a basic task, with forfiles as the program and the appropriate switches. In our case it looks like:
forfiles -p "L:\SQL Backups" -s -m *.sql /D -31 /C "cmd /c del @file"
Basically anything in L:\SQL Backups\ with an .sql extension older than 31 days will be deleted (You have been warned!)
Why don’t I just increase the retention at this point? I needed the files copying anyway, and archiving them just seemed the best way to keep them tidy!