The thing that I wanted (I don't ask for much!) was for cumulative, sequential backup volumes to be created on a daily basis and then pushed out to a remote FTP server which I know is backed up offsite as well (a double backup if you like)
So, what's the principle? Simple really, I run a scheduled task on the Window VPS each night when the database is quiet (normally after 9pm GMT) which is just a .bat file which does the following:
- Exports a backup of the named database (via a .sql command file)
- Moves it into a tmp directory
- Creates a directory for today's date
- Moves the temp file into that
- Dynamically writes an FTP script
- Executes the FTP command
- Tidies up the tmp files
Sounds easy enough, but the internal functions had me thinking a little bit. Let's walk through the process of the files needed:
SQL file export of whole database
Create a text file called export.sql and added this command into it
BACKUP DATABASE [nameofdatabase] TO DISK = N'(pathtothedatabase\nameofdatabase.bak' WITH NOFORMAT, NOINIT, NAME = N' EasyToRememberBackupName', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
nameofdatabase = yeah, the name of your database
pathtothedatabase = full directory structure where you want the backup to be saved to (e.g. D:\SQLBackups\Name\TMP
The TMP bit you need to create to save the initial .bak file into from the SQL dump
EasyToRememberBackupName = This is nothing more than a convenient name to call the backup and won't be used anywhere else anyway, but will be referenced if you restore a database from this in the future. We'll call it SQLbackup.bak for this exercise
Save that file into a dedicated directory (which will be referred to as VolumeRunningTheScriptFrom in the next stage) and create a new empty text file called backup.bat
Now the really fun bit, we've nicked a bit of internal DOS commands coupled it with SQL commands and chucked in a bit of FTP command line code as well to create this beast:
sqlcmd -S .\SQLSERVERLOCALNAME -i "VolumeRunningTheScriptFrom\export.sql"
set string=%date:~-7,2%%date:~-10,2%%date:~-4,4%
echo %string%
md VolumeRunningTheScriptFrom\%string%
VolumeRunningTheScriptFrom\%string%
cd..
move VolumeRunningTheScriptFrom\tmp\SQLbackup.bak VolumeRunningTheScriptFrom\%string%\SQLbackup.bak
cd VolumeRunningTheScriptFrom\%string%\
move VolumeRunningTheScriptFrom\%string%\SQLbackup.bak VolumeRunningTheScriptFrom\%string%\BACKUP_%string%.bak
cd "VolumeRunningTheScriptFrom"
@echo off
del script.txt
echo open FTPserveraddress>>script.txt
echo user FTPuseraccount>>script.txt
echo FTPpassword>>script.txt
echo cd RemoteDirectory>>script.txt
echo bin>>script.txt
echo put VolumeRunningTheScriptFrom\%string%\BACKUP_%string%.bak>>script.txt
echo bye>>script.txt
echo exit>>script.txt
echo cls>>script.txt
ftp -n -s:"VolumeRunningTheScriptFrom\script.txt"
Now that's a mixed bag of code to walk through, let's do it a line at a time:
sqlcmd -S .\SQLSERVERLOCALNAME -i "VolumeRunningTheScriptFrom\export.sql"
From the command line tell the local SQL engine to open the Local SQL Server (in this case SQLSERVERLOCALNAME they typically run as SQLEXPRESS or SQL2008EXPRESS whatever name you gave it when it was set up) and then the FULL path to the .sql file created earlier to execute.
set string=%date:~-7,2%%date:~-10,2%%date:~-4,4%
echo %string%
OK so using a lovely old DOS date function, we are setting a string to be today's date, so if it was 23/11/2013 then the date string would be
23112013
which will make the sequential nature of the backup a whole lot easier to name for uniqueness
md VolumeRunningTheScriptFrom\%string%
VolumeRunningTheScriptFrom\%string%
cd..
Let's make a directory within your backup folder to the value of the date string (23112013) and check its ok, then skip back up to the root of where we are running the script from
move VolumeRunningTheScriptFrom\TMP\SQLbackup.bak VolumeRunningTheScriptFrom\%string%\SQLbackup.bak
OK, so now we want to move the temporary SQL dump from the TMP folder into the newly created datestamp folder
cd VolumeRunningTheScriptFrom\%string%\
move VolumeRunningTheScriptFrom\%string%\SQLbackup.bak VolumeRunningTheScriptFrom\%string%\BACKUP_%string%.bak
So now we want to rename the backup to be specifically renamed to the datestamp. In my case we were running a number of backups into the offsite directory so we wanted to prefix the file with the database name, in this example BACKUP_
So we've basically moved back into the new backup folder for 23112013 and renamed ("move" in DOS terms) the SQLbackup.bak file to a new filename of BACKUP_23112013.bak
cd "VolumeRunningTheScriptFrom"
@echo off
Move back up the folder to the root
del script.txt
Now the funky FTP script to handle the date-specific file. The issue I had with this was that we wanted to keep FTP transferring files from the system from a scheduled task running each evening, but in the past we used a static FTP text file to call from, this was no use when using date-sequential strings, so we are basically creating the script.txt file on the fly, a cunning way of making sure we reference the correct backup for FTP offsite.
First thing though, delete yesterday's script!!
echo open FTPserveraddress>>script.txt
echo user FTPuseraccount>>script.txt
echo FTPpassword>>script.txt
echo cd RemoteDirectory>>script.txt
echo bin>>script.txt
echo put VolumeRunningTheScriptFrom\%string%\BACKUP_%string%.bak>>script.txt
echo bye>>script.txt
echo exit>>script.txt
echo cls>>script.txt
Now we have written all the values into the script.txt file, all that's left is for us to call the script via the system FTP command, silently...
ftp -n -s:"VolumeRunningTheScriptFrom\script.txt"
So, that's the basics in place for the backup, all that's needed now is to schedule this batch file through the Task Scheduler and we're good to run.
I always just run the batch file at the beginning to check it and there's a nice satisfying DOS window which runs through the 0-100% back up process and the FTP transfer which informs you that the files are being pushed offsite.
Please drop me a note if you want the source files, mark{at}1stclassmedia.co.uk
