Simple Backup Solution for SQL Server Express

So last week I was asked by one of our developers, if I can help them in setting up some sort of automated backup system which actually takes backup of database to a shared network drive at given time and also we need to retain past X day’s (or 3 day’s) backup copy just in case if we want to rollback. And the database instance was SQL Server 2008 Express !! yes, we are developing our product which is using SQL Server as a database but for client to not to have license of server, we are using express edition of SQL Server. Even though they have SQL Agent, they can’t actually be used unless you have one of PAID version of SQL Server is installed !!!

Actually having SQL Agent is not crucial as it is by function just a scheduled task in Windows with batch functionality that is useful for SQL Server (atleast I think like that)… So, to compensate for lack of SQL Agent we can combine SQLCMD, Windows Batch File and Task Scheduler to get the job done. Though it sounds way too complex, it is actually dead simple.

First I googled for instruction on how can I create VB script which will actually delete files from specific location and with specific timestamp. And I found this VB script from SQL Server Central (but not sure). I honestly can’t recall the original source of the script. But the script is something like following, it is very simple and concise. User just need to set path of file (in my case backup location) and # of days  (in my case 3). So this script will simply delete EVERYTHING in that defined folder that is older than # of days we have defined !!

'This Simple Script deletes ANY file in the assigned folder
' We can change Retention Period to any number, which is the threshold to keep older backup files
' We can also change location of backup file as well


iDaysOld = 2 'File Retention Period - 1 day
strPath = "\\backupstorage\DatabaseBackups\"  'Backup File Location

Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objFolder = objFSO.GetFolder(strPath) 
Set colSubfolders = objFolder.Subfolders 
Set colFiles = objFolder.Files 

For Each objFile in colFiles 
   If objFile.DateLastModified < (Date() - iDaysOld) Then 
       'MsgBox "Dir: " & objFolder.Name & vbCrLf & "File: " & objFile.Name
       objFile.Delete 
   End If 
Next 


For Each objSubfolder in colSubfolders 
   Set colFiles = objSubfolder.Files 
   For Each objFile in colFiles 
       If objFile.DateLastModified < (Date() - iDaysOld) Then 
          'MsgBox "Dir: " & objSubfolder.Name & vbCrLf & "File: " & objFile.Name
           objFile.Delete 
       End If 
   Next 
Next

As a second step, I need to create simple database backup script. I think you know SQL Server then you surely know how to take backup of database using script. My script was very simple, it just takes full backup of database and adds current date (and month) in name of file so I can keep track of them and then store the backup at network location.

/*

Database backup script
Dt: 3/4/11
Gaurang

*/

declare @dateEnd varchar(20)
declare @location varchar(300)
select @dateEnd= (CONVERT(varchar(2),DATEPART(mm,getdate()))+ '_' 
                    + CONVERT(varchar(2),DATEPART(dd,getdate())) )

select @location = '\\backupstorage\DatabaseBackups\'


declare @DBExp varchar(500)

select @DBExp = 'BACKUP DATABASE [DBExp] TO  DISK = '''+ @location + 'DBExp_'+@dateEnd+'.bak'' 
WITH NOFORMAT, NOINIT, NAME = ''DBExp-Full Database Backup'' ,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

Exec (@DBExp)

And in next step I just combined both of the scripts in a windows batch file.

REM Run TSQL Script to backup databases
sqlcmd -S DatabaseServer\DBExp -U sa -P sa -i"BACKUP.sql"

REM Run database backup cleanup script
deleteOldBackups.vbs

Finally I used windows task scheduler to schedule this job to run every night at 3 AM and voila, I have SQL Server Agent equivalent daily backup job that satisfies following requirement of our development team

A) We take daily backup of our development database

B) Job runs at every night

C) We have backup of past 3 days

Of course as you can see, there is a lot room for improvement. For example, I am not doing any sort of backup validation which is crucial part of ANY DATABASE backup system. Which basically validates that the backup we have take is GOOD . Also, I can add some sort of notification in event of failed backup !! Which is again important to have because right now we are just ASSUMING that our system will work fine all the time (but since it is just a dev. environment We (I) really don’t care much).

May be in time I will improve this little script collection or may be not.

It’s Just a Thought …