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 …