Schedule backup with SQL 2005 Express

How do I schedule a backup with SQL Server 2005 Express. When I right-click a database and choose Tasks -> Back Up...
I don't get the option to schedule the backup. How do I do?

hi,

SQLExpress does not provide the SQL Server Agent, thus you can not schedule a "native" backup or whatever task via a SQL Server Agent job..

you have to rely on the native OS scheduler, writing your own command file including the actual BACKUP DATABASE... statement to be executed, or on other solutions even based on the OS scheduler, like http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, third party solutions like http://www.lazycoding.com/products.aspx or http://www.valesoftware.com/products-express-agent.php, http://www.codeproject.com/vb/net/SQLAgent.asp, or custom solutions based on the Service Broker as indicated in http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx

regards|||

Ok! Thanks.

Is SQL Server 2005 Express edition the only one that do not provide the SQL Server Agent?

|||

Thats right.

See the information from here:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

SQL Agent Job Scheduling Service

Jens K. Suessmeyer.

http://www.sqlserver2008.de

|||Excuse me, could you please tell me how can you backup your database use the Sql Express ?
I just want to use the osql -E order, but the system tell me that it fail to connect to the database, it may caused by the remote connections closed, but actually I allow the remote connection.
Is there another way to backup the database in the sql Express?
|||

The complete syntax for backup is proveded in Books Online, please search there for syntax help. If you are not being allowed to login, then you are either providing the wrong instance name or you are using an account that doesn't have permissions.

The most common error people make with SQL Express is that it is installed by default to an instance name, not the default instance, so you have to pass the entire instance name when connectiong:

osql -S <machine>\SQLEXPRESS -E ...

See if adding the instance name resolves your problem.

Mike

|||

If you are unfamiliar with the BACKUP syntax, you can use the UI command of SSMS(Express) and create a script which can be laterone copied to the AT job using the SQLCMD utility as Mike mentioned.

Jens K. Suessmeyer

http://www.sqlserver2005.de