A lot of people use Sql Server express for the reason of it’s generous licensing. One of the drawbacks of Sql Server express is that it comes without the Sql Agent – which is the batch-running part of Sql Server. Sql Agent is used to run jobs to do all sorts of things with Sql Server, and one of the most common jobs is to run a backup schedule. And, as everyone knows, you need to have a daily backup schedule on your database, or one day, you’re going to pay for it.
This blog post will cover how to setup a backup procedure which will backup databases, zip them up, copy them to a location, ftp them to a backup server for long term storage, and clean up after itself. I’ve done this without purchasing a single piece of backup software, and relied on in-built tools or shareware.
It’s targeted towards people who are running their own web server, whether it’s co-located, hosted or a VPS. If the thought of command line utilities, scheduled tasks and file permissions stresses you, then I’d suggest the purchase of a nice backup system to ease your troubles.
If you’re on a shared hosting plan, all you need is a signature in stone that your host can provide you with last nights backup at short notice. If you are on a hosting plan, it’s worthwhile testing them out randomly by asking for a database backup, just to check out if they can lay their hands on one. The time you need it is not the time to hear ‘sorry, don’t have one’.
This setup covers a pretty common scenario where the web server doesn’t have a lot of disk space (common with VPS machines) and is not connected by an internal network to any other computers (hence the FTP part). The first part of the script runs on the server, and the second part runs on a different machine. The second machine could be another server, it could be your workstation, as long as it will be running when the script is scheduled to run. All of the various scripts are included in the attached zip file, but you’ll need to modify them, as I have taken out all the private information.
Disclaimer : by all means copy what I have done, but you’re on your own when it comes to setting this up and installing it. I don’t make any promises that it will work for you, or that it is the most secure, or most robust, or most perfect way. It’s a simple setup and not suitable for mission critical apps. In other words : if you lose data or get hacked, the responsibility is yours.
First, I’ll cover what each piece does, then I’ll cover how to install it.
Step 1 : Setup
Download the code : Database_Backup_Scripts.zip
Background
The Sql Server backups will be done by running a Sql query against the database to do the backups. This is actually what the native Sql Agent in the ‘Full’ Sql Server does anyway, only you don’t have a Guid or pretty Wizard to help you. The rest of the actions are done by batch commands run against either tools or utilities on your server.
How To Install
On your server with Sql Server, create a directory called c:\db\backups and c:\db\scripts. This server must have FTP enabled on it, or be able to copy to a server with FTP enabled (a common scenario with a web server also masquerading as a database server, or web server and sql server in the same DMZ). Create a directory which has permissions for an FTP user to read from : C:\inetpub\ftp.
On your server used to store the backups, create a directory called d:\db\ (note, this can be c:\db, but I’ve made it d:\db to make it easy to tell apart)
Note down, and test the FTP user/password that you will use to copy the database backups from the server.
Extract the contents of the zip file somewhere where you can easily obtain them.
Step 2 : Running a database backup
Background
All Sql Server databases can be backed up by running a Sql script, which calls the in-built stored procedures to backup the files.
I also created a specific user called ‘dbbackup’ on the server to run the backups. With this user, I went through and granted the ‘db_backupoperator’ role membership through the ‘User Mapping’ screen. This can’t be scripted because it depends on the individual databases.
I found a neat little stored procedure script from this page : Using T-Sql to Backup and restore Sql Server databases
I added some details to this script, which includes the creation of a specific user to run the scripts.
How to Install
First, either create your database backup user on your Sql Server, or select a user and grant them the ‘db_backupoperator’ role on each database you’d like to backup.
Open up a Sql query window in your target server, and select the ‘master’ database. Open the ‘Create_Backup_Proc.sql’ file from the zip package. If you have a different user, change the ‘dbbackup’ user permission to your chosen user (use find/replace).
Execute the script, which will create the stored procedure. Test out the procedure to see if it works, by running this script in a Query window:
exec sp_backup_databases 'c:\db\backups'
Note that this backs up to the aforementioned c:\db\backups location. I prefer a simple backup location, rather than the convoluted default that Sql server provides. The backup location for a Sql Server database must always be on the local server. You can’t back up to a remote machine.
Check your c:\db\backups location : you should have a databasename.bak file for each database in your list. If not, troubleshoot this until your script is working.
Step 3: Setting up your backup, database copy and zip into a batch file
Background
As the process is designed to use FTP to transfer the backup(s) to another computer, the smaller the file, the better. One of my database backups runs to over 400 mb, and this would take too long and use up too much bandwidth to be useful. Therefore, you need to compress the backups into a single file for easy transfer.
For this, I’m using the PkZip command line utility. Now, this is an old shareware application that dates back over 10 years, and I already had a copy. You’ll have to source your own (I can’t distribute it) and make sure that you comply with the licensing terms. If it helps, the install for mine is called ‘pkzc400s.msi’. You could also use any other command-line compression utility if you have one.
Once the backup zip file is created, it will need the FTP user read permissions in order to be downloaded. This is achieved by running the cacls utility to grant the required permission.
All of the steps are run through a batch file, which runs the Sql to do the backups, runs the zip utility to put the backups into one zip file, then sets the permissions on the file and copies it to the FTP folder.
How to Install
Copy the ‘Run_Backups.cmd’ file to the C:\db\script location on your database server. Open the file in notepad and set the server name (replace dbserver), username (replace dbbackup if different user) and password (replace password) .
If you have a different command line zip utility, change the command for the Pkzipc call. It should still create a zip file called c:\db\backups\db_backups.zip.
Update the FTP folder location to where your FTP logon can read the file.
Update the FTP user in the cacls command (search for ftp_user). You may need to prefix the server name or domain name if applicable.
Set up a scheduled task to run the batch file on a daily basis. You can use this command line, or go through the scheduled task application on your server.
schtasks /create /sc Daily /st 00:00:00 /tn DbBackups /tr "c:\db\scripts\run_backups.cmd"
Note the above command line creates a daily task which runs at midnight, and runs the run_backups.cmd file.
Test out your backup by brining up a command line window can calling : c:\db\scripts\run_backups.cmd. Check the .txt log files created to see if all steps work correctly, and do an inspection on the created zip file to make sure it contains the database backups. Try using an FTP program to download the file to make sure you have both the location and permission right for the FTP step.
Step 4 : Setting up your backup server
Background
In my case, I used an internet connected machine which has an external Terabyte disk on it. I use this for backups of system drives, data, email archives, and for backups of live websites. This machine is used to contact the database server and download the backups via FTP.
Again, this is achieved through a scheduled task which runs the individual tasks, including copying the file via FTP, and cleaning up the directory of backups older than a certain date.
How to Install
Copy the contents of the Backup_server_Scripts to your d:\db\ directory. First, open up the ‘Archive_Backups.cmd’ file and change the Z:\backup\database directory. This is the local path where you want to archive your database backups. This might be an external drive, a Network Attached Storage device, or anywhere with lots of disk space and low risk of it getting deleted or lost.
Also, review how long you would like to keep the file for. The last task in the batch file runs the delfiles.vbs script, which deletes any files older than a specified number of days. By default it is set to 10 days, to allow for ‘oops’ moments. You can change the value to whatever number of days you like. This should be calculated as a function of disk space, backup size, and likely time period before you realise a mistake has been made.
Open the Ftp_Backups.txt file. This contains a series of batched commands to provide to the native Windows FTP program. The edits are (line by line)
ftp.yoursite.com –> replace with your ftp server address
ftp_user –> replace with your ftp username
password –> replace with your password for the ftp user
cd ftp –> replace ftp with the directory where your file gets copied to. If it’s in the root ftp directory, delete this line altogether. If it’s more than one level down, adjust as necessary.
I got a lot of help from this resource: Ftp Script Examples
Save the changes to both files, and then test out the process by running the file in a command line window by typing in ‘d:\db\archive_backups.cmd’. You should see the results of this run, and your database backups copied to your archiving location. For an extra test, copy an old file into that location and see if the cleanup script deletes it. Note, that your windows firewall (or other firewall) may block the FTP port – you may have to adjust this. Check the log files created to ascertain if this is the case.
You will note that the archive task does not overwrite the same file every day, but rather copies it with the date and time, like this : 2010-01-22-Fri_db_Backups.zip. I use the YYYY-MM-DD format because it is easy to order the files by filename and have them in chronological order. I got help with the batch file date renaming from this article on creating a Batch file to append date to file name. Note that this code may be dependent on local date/time format settings- you might have to modify it to get the result you want.
Lastly, setup a scheduled task on your workstation to run the archive_backups.cmd file. This can be done with:
schtasks /create /sc Daily /st 01:00:00 /tn DbBackupsArchive /tr "d:\db\archive_backups.cmd"
Note that this runs 1 hour later than the database, backup, to leave ample time for the first backup to run and finish.
Modifying for your own purposes.
There are many ways you can expand this basic setup. The first, most obvious one is to save bandwidth by only running the ‘archive’ run once a week, so you only get the latest backup. This gives you a two-step backup – if you just have a local deletion/corruption of the database, then you’ve always got the last night’s backup on the server (in the zip file) to restore from. If you have a total server loss, the most you’re going to lose is 7 days worth of data. You can adjust this as you like, for example, running every second day. It’s a tradeoff between potential data loss and bandwidth, and depends on the individual site involved. For many sites, the bandwidth will be small and it’s worth it to do every day. If you have a relatively static site, consider your needs there. If you have an active site with forum posts and user updates happening every day, you may need to do a daily/half daily backup.
Note that you can also combine other user data into this process – it doesn’t just have to be database files (though the database is most important for a CMS application like DotNetNuke). If you have other user-created files (uploaded images, etc) you could combine the zip process in a similar way.
Results?
Please let me know via the comments if you find faults with the attached code, or see glaring flaws, or have suggestions on making the process better. It’s my intention for this to provide a starting point for people putting together their own customised backup process and getting a better understanding of the tools available to achieve this.