Automating Database Backups with Microsoft SQL Server 2005 Express Edition
With the introduction of Microsoft’s SQL Server 2005 came the introduction of Express Edition. Replacing Microsoft’s MSDE from the SQL Server 2000 product line Express Edition removed the throttling limitation and provided an appropriate free SQL Server product for small to mid-sized websites.
The new Express Edition of SQL Server brought many of the new features of SQL Server 2005 into the arena for those looking for a stable database environment but with only the need for a single processor database server and smaller database sizes. But Microsoft chose to remove the SQL Agent from their new free version of SQL Server, consequently eliminating the ability to schedule jobs such as database backups.
Luckily for those looking to implement the power and stability of SQL Server 2005 Express Edition, backups of your database can be automated with very little work using Windows Task Scheduler and the new SQLCMD.EXE.
First you’ll need to download and install Microsoft’s SQL Server 2005 Express Edition and the SQL Server Management Studio Express-both which can be found here: http://msdn.microsoft.com/vstudio/express/sql/. (SQL Server Management Studio Express is not required for setting up the automated backups, but it is a good tool for managing your SQL Server 2005 Express Edition. The steps below can be easily done without any kind of GUI, but the commands to do so are outside of the scope of this blog.)
Once you have these products installed we’ll need to setup a login with the authorization to perform database backups. Open SQL Server Management Studio Express (SSMSE) and connect to your installation of SQL Server 2005 Express Edition (SQL Express). Select the security folder and then click “New à Login…” The New Login dialog box will appear and this will give you a chance to setup a login to use for you backup jobs. For login name use something like ‘backupadmin’, click “SQL Server authentication” and then type in a password. Because of the nature of this login, in most scenarios you will want to uncheck “Enforce password policy” which will automatically uncheck the other password policy options.
Under “Select a page,” go to the “User Mappings”. Select the database you will be backing up and click “Map”. The role selections at the bottom of the screen will now be active. Select the “db_backupoperator”. Now click “OK”. This user will be the user within SQL Server to execute the backup command.
Now we will need to setup a T-SQL script that will actually execute the database backup-luckily SSMSE makes this nice and simple for us. Select the database that you will be backing up and click “Tasks à Backup…” Select the appropriate backup options for you database and click “Ctrl+Shift+F” to save the options to a SQL server script file. You’ll want to save this file to a location on the server such as D:adminsqlSQLExpressBackups.sql. (Note: The machine user that SQL Express is running under, such as NETWORK SERVICE, needs read permissions to this file.)
Your SQL server script file should look something like this:
BACKUP DATABASE [DatabasetoBackup] TO DISK = N‘D:backupsqlDatabasetoBackup.bak’ WITH NOFORMAT, NOINIT, NAME = N‘Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
(Note: The machine user that SQL Express is running under needs write/modify permissions to the directory your database backup file will be located. D:backupsql in the example above.)
You can log into SQL Express using your ‘backupadmin’ user (that we created above) and try running the script that you’ve just create. If there are any permissions issues in SQL Express or on disk you will receive an error. Testing the script now can help us ensure our backups will run successfully.
Now all we have to do is setup the command line to execute our script using SQLCMD.EXE in the Windows Task Scheduler. Go through the setup wizard to setup a new Task-your command should look something like this:
C:Program FilesMicrosoft SQL Server90ToolsBinnSQLCMD.EXE -S [SERVER] -U bakupadmin -P [PASSWORD] -i D:backupsqlSQLExpressBackups.sql
Once your task has been setup you can run it immediately to make sure it will run successfully. That’s it! Allow the task scheduler to do its job and you’ll have a backup of your SQL Express database automatically created for you.