how to create a scheduler to make an SQL Server DB back up?

Question

I have created a database in SQL Server 2008 Express Edition. Can I write a program in VB.net express to take a daily backup at a specified time? What code do I need to write?

Answer 1

It's probably overkill to write a VB.NET application for this. You'd need to write a Windows Service or something similar to run on an infinite loop, and figure out the code to take a database backup.

Since it's express, of course there's no SQL Server Agent to run scheduled jobs. However, you can do the same thing by scheduling a job from the operating system. Here's a good article on how to accomplish this: http://www.fmsinc.com/free/NewTips/SQL/SQLServerExpressDatabase/Automated_Backup.asp. Quote from the article:

• Create a new backup device using the wizard (expand Server Objects => New Backup Device).
• Right click on the new backup device and select the database that you wish to backup.
• Select the backup type (most often this is “Full”).
• Choose a name for the job. Insert a description if you would like.
• Click the Add button to add a Device Destination (this is where you would like the backup .bak output file to be placed).
• Click Ok. The backup device is now setup. Repeat steps 3-7 for each database that you wish to backup.
• Create a batch file using the following syntax (this is used to execute the backup device). A syntax example is below (remember to save this file with a “BAT” file extension):

sqlcmd -S MYSERVER\OFFICESERVERS -E -Q "BACKUP DATABASE MASTER TO TEST"

..where “MYSERVER” is the name of the SQL Server machine.
..where “OFFICESERVERS” is the name of the SQL Server.
..where “Test” is the name of the backup job.
..where “MASTER” is the name of the database.

• Finally, open the Microsoft Windows Scheduler and assign the batch file created above to a scheduled event. Set the event to run at the point you wish. For more information on the Microsoft Windows Scheduler and its useful functionality, please perform a search of the web using your preferred search engine or consult your Microsoft Windows documentation.