An automated backup regime for a small SQL Server database

I am making this post to show how to make a simple automated backup regime for a relatively small SQL Server (I am using SQL Server 2008 R2 Express Edition) database. I am doing so, because when I was looking for some inspiration for doing this myself I found it quite hard to get all the information together in one place. I hope this is of use to someone.

The database itself is supporting a small business and will typically have transactions that alter it only during the day. The database itself is currently about 150MB big and a full backup takes less than a minute. It replaces an Access database which is being backed up daily. As a result of that, I decided not to bother with differential backups, and instead take a full backup daily. But I believe we can do better (than with Access) with respect to data security, so I am using the full backup mode on the database and taking transaction log backups. For convenience, I take one just before business starts for the day at 8:00am, and repeat the process every 4 hours (at Midday, 4:00pm and at 8:00pm) until business is shut. Because this is the Express Edition of SQL Server I am scheduling the backups using the standard Windows task scheduler.

SQL Server backup regime allows to put multiple backups inside a single file if so desired, but I couldn’t really see the point. If I have a single file for each backup with a naming convention that tells me what time the backup was taken (and what type it is) then managing the files manually (copying, archiving into longer term storage etc etc) is so much easier. I decided therefore to use the following convention when naming files.

The daily backup is named PASLyyyymmdd_DB.bak where yyyy represents the year, mm the month and dd the day that the backup was taken. Similarly the transaction log backup is named PASLyyyymmddThh_LOG.bak where the yyyy,mm and dd markers mean the same as for the daily backup, but in addition there is a hh marker that represents the hour (in 24 hour clock terms) that the backup was taken.

I decided that I would keep backups around for 5 days, giving plenty of time for other scheduled (or otherwise) tasks to select an appropriate set for longer term storage.

The database files themselves are stored on the D: drive on the server, so I decided to make the backups on the C: drive. This gives some protection against disk failure, but not against total machine failure. You should use your own judgement as to where to locate your own backups. [Locations of backups are set as parameters in the BAT files controlling them]

I make use of the sqlcmd utility that comes with SQL Server to execute Transact-SQL statements to actually perform the backups, but these are encased in standard Windows BAT files to set up parameters etc. It is the BAT file that is added to the task scheduler. A special SQL Server Login called a_backup has been given db_backupoperator privileges in the database.

Firstly the daily backup BAT file

:: Daily Backup of SQLSERVER databases

:: AKC 30 Apr 2011

::



:: Set environment variables

SET SQLCMDPASSWORD=xxxxxx
SET BACKUPDIR=C:\backups\db\

SET SCRIPTDIR=D:\Public\DB\batch_scripts\



:: Issue backup commands from a sql script

SQLCMD -U a_backup -S SERVER\SQLEXPRESS -i %SCRIPTDIR%daily_backup.sql



:: Tidy Up Old Backup Files (keep for 5 days)



FORFILES /P %BACKUPDIR% /S /M "*.bak" /D -5 /C "cmd /c del @path"

and the related daily backup script

DECLARE @thistime nvarchar(25);

DECLARE @filename nvarchar(255);

SET @thistime = CONVERT(nvarchar,GETDATE(),126);

SET @filename = "$(BACKUPDIR)" + N'PASL' + SUBSTRING(@thistime,1,10) + N'_DB.bak';



BACKUP DATABASE DB_live

    TO DISK = @FILENAME

    WITH INIT;

GO

Notice the use of the FORFILES command in the BAT file. You need to check the version of this utility command that your server has – it has been configured to delete each file with an extension *.bak older than 5 days. You may need to alter the mask (‘*.bak’) if you have other files you wish to preserve in your backup directory

Here are the similar files for the transaction log

:: Transaction Log Backups of SQLSERVER databases

:: AKC 30 Apr 2011

:: Run at reasonably spread out times of the day 



:: Set environment variables

SET SQLCMDPASSWORD=xxxxxx
SET BACKUPDIR=C:\backups\db\
SET SCRIPTDIR=D:\Public\DB\batch_scripts\



:: Issue backup commands from a sql script

SQLCMD  -U a_backup -S SERVER\SQLEXPRESS -i %SCRIPTDIR%tlog_backup.sql

DECLARE @thistime nvarchar(25);

DECLARE @filename nvarchar(255);

SET @thistime = CONVERT(nvarchar,GETDATE(),126);

SET @filename = "$(BACKUPDIR)" + N'PASL' + SUBSTRING(@thistime,1,10) + SUBSTRING(@thistime,11,3) + N'_LOG.bak';



BACKUP LOG DB_live

    TO DISK = @FILENAME

    WITH INIT;

GO

All that remains now is to schedule the two BAT files in the Windows Task Scheduler. For the full backup, schedule the batch file to run daily and set the start time to whatever you wish (I used 4:00am). For the transaction log backup set to run daily, with a start time of 8:00am. Then set it to repeat every 4 hours up to 13 hours duration

Note: This seemed easy with the task scheduler on Windows Server 2003, I am not sure if it works as easily with Windows 7, there was not a drop down box for 4 hour although it is possible to type in 4 hours directly into the drop down area. I have typed it in in Windows 7, but not tested it works. The alternative is to just schedule multiple daily tasks which don’t repeat, but start them 8:00am, Midday, 4:00pm and 8:00pm respectively

Author: Alan

I am Alan Chandler.

2 thoughts on “An automated backup regime for a small SQL Server database”

  1. Nice One ! Very Well Put. Is there a way to compress the backup file so that we can save diskspace and probably the time to transfer the backup files to a long term storage

Leave a Reply

Your email address will not be published. Required fields are marked *