Automatic SQL server 2005 backup (Express)

I was looking to do an automatic SQL Server 2005 backup using SQL Server Express.  SQL Server Express 2005 does not offer a built in way to backup automatically. So after doing a fair amount of testing and searching I finally figured out how it could be set up. The solution I use first of makes a local backup copy of the database. After that I use my normal backup software to save it onto a tape or other external device for offsite storage. (Note: I recommend to all my customers that they backup their data nightly onto rotating backup ‘tapes’ one for at least each day of the week, and then at least once a month take one of those to an offsite location).

Setting up an automatic backup, Step by Step

So first off, to make the local backup of the database you will need to have SQL 2005 Express Management Studio Express installed. Assuming you have that installed go ahead and start it and connect to your SQL 2005 Server database.

Next you will need to set up an SQL Server 2005 Backup Device.

1. Click the + next to the Server Objects and you should see Backup Device.
2. Right Click and select New Backup Device.
3. Type in a Device Name (it can be anything you want but make a note of it for later reference)
4. Select the radio box for File and browse for a location to make a backup copy of the database to.
5. Click OK

That’s it for that part. Repeat this for all the databases you wish to backup.
Next we need to schedule a time for that backup to run. For this we can use the Windows Scheduler.

1. Start the Windows Scheduler found in Start-> All Programs-> Accessories-> System Tools-> Scheduled Tasks.
2. Double Click Add Scheduled Task
3. Click Next
4. Select any program from the list. I usually chose the calculator. It doesn’t matter because we are going to override this in a minute anyway.
5. Click Next
6. Name the task. I usually name it something like NightlyDBBackup
7. Select Weekly if you are going to backup M-F. Chose Daily if you are going to backup every night including weekend.s
8. Set the Time you want it to run. Remember this will briefly stop the SQL Server from running to run it at a time of day when it will be the least impactful.
9. If you selected Weekly check off the nights you want it to backup.
10. Click Next
11. Fill in the Username and password for the user who has proper security rights to stop SQL Server, and has security access to the folder the database is to be backed up to. (Administrator almost always does)
12. Click Next
13. Check the Open Advanced Properties button
14. Click Next
15. This will open the task where we can now change the program from the calculator to the backup process.
16. In the Run box delete whatever was there and replace it with the following
17. sqlcmd -S localhostSQLExpress -E -Q “BACKUP DATABASE MyDatabase TO MyDataSQLBackup WITH INIT”

NOTES:

• use localhost if SQL 2005 Server database is on the local computer, otherwise use the name of the machine is it on.
• use SQLExpress if that is the name of the DB part of the servername. (Both the localhostSQLExpress name can be checked when you start SQL Express Management Studio Express because it asks upon startup each time what database you want to connect to)
• Change MyDatabase to the name of the actual database you wish to backup
• Change MyDataSQLBackup to the name of the Backup Device you created in step 3.
• use WITH INIT to so that the backup is not Appended. If you would prefer to Append to the backup each night (I do not recommend that, you will quickly start filling your hard drive and backup device) leave out the WITH INIT.

Test it and now your ready to go. You now have an automatic SQL Server 2005 backup process.  Just set your backup software to backup your data at some point after this event is scheduled. I usually allow about 15 minutes, though in reality the DB backup usually runs in under 2 minutes.