SQL Express is pretty awesome as a light weight database server, and when Microsoft released it using the same database engine as the full version they did the world a huge favour. I have seen SQL Express in use on a lot of Virtual Private Servers & development boxes, but in most cases users don’t backup their databases regularly because of the missing scheduled jobs functionality that doesn’t ship in the Express version of Microsoft SQL Server. Like most limitations there is an easy way around this.
Open SQL Management Studio and copy the following SQL script i created earlier into a new query window.
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Doug Rathbone -- Description: Backup Database Stored Proc -- Param1: dbName -- Param2: backupType F=full, D=differential, L=log -- ============================================= create PROCEDURE [dbo].[sp_BackupDb] @dbName sysname, @backupTypeToRun CHAR(1) AS BEGIN SET NOCOUNT ON; DECLARE @sqlCommand NVARCHAR(1000) DECLARE @dateTime NVARCHAR(20) SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','-') +'-' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') DECLARE @databaseFileName NVARCHAR(200) SET @databaseFileName = replace(@dbName,']','') SET @databaseFileName = replace(@databaseFileName,'[','') IF @backupTypeToRun = 'F' SET @sqlCommand = 'BACKUP DATABASE ' + @dbName + ' TO DISK = ''C:\DbBackups\' + @databaseFileName + '_Full_' + @dateTime + '.BAK''' IF @backupTypeToRun = 'D' SET @sqlCommand = 'BACKUP DATABASE ' + @dbName + ' TO DISK = ''C:\DbBackups\' + @databaseFileName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL' IF @backupTypeToRun = 'L' SET @sqlCommand = 'BACKUP LOG ' + @dbName + ' TO DISK = ''C:\DbBackups\' + @databaseFileName + '_Log_' + @dateTime + '.TRN''' EXECUTE sp_executesql @sqlCommand END
The above script creates a new stored procedure called Sp_BackupDb (hate on me for my use of bad stored procedure naming another day!) that runs against your database of choice, and depending on what backup type specified it does either a full (using the F parameter), difference (using the D parameter) or log backup (using the L paramter) to a folder C:\DbBackups (feel free to change this to your choice of backup folder).
Run the above script to create the stored procedure
Create a Directory called C:\BackupScripts
Within this new directory create a new SQL script file and name it BackupDatabases.sql
Copy the following script into this SQL script file and add all the database names you want to backup regularly. remember you have the option of using the different backup types i specified F, D, L
sp_BackupDatabase 'master', 'F' GO sp_BackupDb 'model', 'F' GO sp_BackupDb 'msdb', 'F' GO sp_BackupDb '[myDatabase1]', 'F' GO sp_BackupDb '[myDatabase2]', 'F' GO QUIT
Open the Task Scheduler and select Create Basic Task from the right hand menu.
Enter a task name that you want to call your backup i.e. “Daily Database backup” and click Next
Specify the frequency that you would like the backup to run and click Next
Then enter the start date and time etc. that you want the backup to run (i.e. mine is at midnight every day) and click Next
Specify that you want to Start a program and click Next
Enter the location of your SQLCMD.EXE runtime – depending on your version of SQL Express:
- SQL Express 2005
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE
- SQL Express 2008
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
Enter the arguments as being:
-S .\sqlexpress -E -i C:\DbScripts\BackupDatabases.sql
This relates directly to:
- -S (this specifies the instance name for SQL Server - if you are running this locally this is by default .\sqlexpress)
- -E (this specifies that you want to make a trusted connection)
- -i (this specifies the input command file which in our case is C:\BackupScripts\BackupDatabases.sql (this is the file that we created above with the command steps)
Check the box marked Open the Properties dialog when i click Finish and then Click Finish
Now click on the button marked Change User or Group next to the security options area of the dialog. This allows us to specify what user the task will run as.
Enter the word SYSTEM and click OK
Click OK again, and you’re done!
Feel free to run your task manually by right clicking the task and selecting Run
Enjoy having your databases regularly backed up!