Automating backups for SQL Server Express
I like SQL Server. It's always been my RDBMS of choice, and has pretty much been the standard at most places I've worked. But here at my current gig we use Oracle, and we recently purchased a third party application that requires SQL Server. Since this application wasn't going to be working with a ton of data, and it's probably the only thing we'll use SQL Server for, we opted to go with SQL Server Express instead of actually purchasing a full-blown edition.
One of the limitations of SQL Server Express is that you can't automate backups in it like you can the other versions of SQL Server. You have to manually write the backup scripts and then schedule them to run in Windows Scheduled Tasks. This really isn't a big deal, but it is annoying. So, as you can imagine, as soon as we got our databases up an running, the first thing I had to do was create some backup scripts.
Automating backups in SQL Server Express really wasn't that hard, but to save someone else the trouble of having to figure out how to do it I've posted my solution below. For the sake of this example, I'm going to be directing everything to a "DatabaseBackups" directory, which has two directories in it. "Scripts" for all of the scripts we're going to write, and "Daily" for all of the daily backup files to be stored.
First, here's the actual SQL script that backs up the database. We're going to save it as dailyBackupScript.sql. (NOTE: Replace the text "YourDBhere" with your database name, and specify the desired location of your backup directory)
select @sql = 'BACKUP DATABASE [YourDBhere] TO DISK = ''C:\DatabaseBackups\Daily\YourDBhere_' +convert(varchar, getdate(), 110)+ '.bak'' WITH RETAINDAYS = 30, NOFORMAT, NOINIT, NAME = N''YourDBhere-Daily Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
exec (@sql)
GO
You can actually run the above code in SQL Server Management Studio (with the correct database name, of course) and it will back up your database.
Now, the next thing we need to do is make sure that our backup files get deleted after a certain amount of time. In my particular case, I only wanted to keep 30 days worth of backup files on the server. Below is a VB script that will go through our backup directory and delete any .BAK files that are over 30 days old. It will also keep a log file of everything it deletes. We're going to save this as deleteDailyBAK.vbs. (NOTE: Replace the "sFolder" value below with whatever directory you specified for your backups in dailyBackupScript.sql)
Dim fso, folder, files, sFolder, sFolderTarget
Set fso = CreateObject("Scripting.FileSystemObject")
'location of the database backup files
sFolder = "C:\DatabaseBackups\Daily\"
Set folder = fso.GetFolder(sFolder)
Set files = folder.Files
'used for writing to textfile - generate report on database backups deleted
Const ForAppending = 8
'you need to create a folder named "scripts" for ease of file management &
'a file inside it named "LOG.txt" for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & "\LOG.txt", ForAppending)
objFile.Write "================================================================" & VBCRLF & VBCRLF
objFile.Write " DATABASE BACKUP FILE REPORT " & VBCRLF
objFile.Write " DATE: " & FormatDateTime(Now(),1) & "" & VBCRLF
objFile.Write " TIME: " & FormatDateTime(Now(),3) & "" & VBCRLF & VBCRLF
objFile.Write "================================================================" & VBCRLF
'iterate thru each of the files in the database backup folder
For Each itemFiles In files
'retrieve complete path of file for the DeleteFile method and to extract
'file extension using the GetExtensionName method
a=sFolder & itemFiles.Name
'retrieve file extension
b = fso.GetExtensionName(a)
'check if the file extension is BAK
If uCase(b)="BAK" Then
'check if the database backups are older than 30 days
If DateDiff("d",itemFiles.DateCreated,Now()) >= 30 Then
'Delete any old BACKUP files to clean up folder
fso.DeleteFile a
objFile.WriteLine "BACKUP FILE DELETED: " & a
End If
End If
Next
objFile.WriteLine "================================================================" & VBCRLF & VBCRLF
objFile.Close
Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing
And finally, we need to create a cmd file that calls both processes together. This is the file that we'll actually schedule to run every day. We're going to save this as dailyBackup.cmd (NOTE: Replace the directory paths listed below with whatever directory you specified for your backup scripts. You will also need to replace "SQLEXPRESS" with your database server and instance names.)
sqlcmd -S SQLEXPRESS -E -i"C:\DatabaseBackups\scripts\dailyBackupScript.sql"
REM Run database backup cleanup script
C:\DatabaseBackups\scripts\deleteDailyBAK.vbs
And that's it. Just schedule dailyBackup.cmd to run in Windows Scheduled Tasks for each day, and you've got daily automated backups of your SQL Server Express databases.
http://www.sqlbackupandftp.com/