0

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?

RatDaddy
  • 495
  • 1
  • 4
  • 8
Red Swan
  • 15,157
  • 43
  • 156
  • 238

1 Answers1

2

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.
McGarnagle
  • 101,349
  • 31
  • 229
  • 260
  • Ah, that's very good. Thanks dbaseman. A little note. The right syntax for the backup command could be obtained easily using the Script button (however I don't know it the express version has this functionality) +1 – Steve May 18 '12 at 07:57
  • SQL Server 2008 Express *does* come with SQL Server Agent. – Andrew Morton May 18 '12 at 10:35
  • @AndrewMorton that's interesting- can you provide a link? – McGarnagle May 18 '12 at 17:30
  • @dbaseman Strangely, I can provide a reference that says I'm wrong: http://msdn.microsoft.com/en-us/library/ms365248%28v=sql.100%29.aspx and yet here I am looking at the services on two computers with SQL Server 2008 R2 Express and it is there with a path to the executable at "c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\SQLAGENT.EXE" -i SQLEXPRESS . – Andrew Morton May 18 '12 at 17:46
  • Further to that, contrary to what The Rambling DBA found: http://jmkehayias.blogspot.co.uk/2009/01/sql-server-2008-express-sql-agent-won.html I can start the SQL Server Agent service, but no appropriate tab appears in SSMS. I don't know enough to attempt to invoke it by script. Edit: No, I can't start it. It appears to start with no errors, but it stops almost immediately. So your answer will be the necessary one. – Andrew Morton May 18 '12 at 17:54
  • @AndrewMorton The Rambling DBA link is the one I was looking at too, when Googled it. Seems like MS tried to include it with questionable success. You get what you pay for I suppose. – McGarnagle May 18 '12 at 19:06