One of the limitations of SQL Server Express is that you can’t automate backups in it via the SQL Server Agent 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.
Automating backups in SQL Server Express really isn’t that hard, but to save someone else the trouble of having to figure out how to do it I’ve posted the below solution that I’ve used in the past. It’s a quick-n-dirty script, which I’m sure can be improved, but it works well. 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)
*Update 07/07/2015 – If you’d rather use Ola Hallengren’s solution, see my post on Using Ola Hallengren’s SQL Server Maintenance Solution with SQL Server Express Edition.
|
1 2 3 4 5 6 |
declare @sql varchar(2000) 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' execute sp_executesql @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 SQL Server Express 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 this particular case, let’s only 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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
On Error Resume Next 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.)
|
1 2 3 4 5 6 7 |
REM Run TSQL Script to backup databases 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 2008 R2 Express Edition databases.
Thanks! Just what I was looking for.