I don't see any built in mechanism for scheduling nightly backups in SQL Server 2005. What tools are available to perform this task and how reliable are they?
The SQL Server Agent will do it for you.
There is even a user-friendly wizard available to create the necessary backup jobs (SQL Server 2005 Books Online: How to: Create a Maintenance Plan). In terms of reliability - I would expect this to be rock-solid.
It can execute multiple-step jobs at complex schedules and notify you by mail or other means or start processes depending on the success or failure of any job.
By default, the SQL Server Agent service is disabled when SQL Server 2005 or later is installed unless the user explicitly chooses to autostart the service.
I have done this using a Maintenance Plan. I have actually written a script to backup all the user databases. I have shared that script here http://dbalink.wordpress.com/2009/04/25/automated-sql-server-back-poor-mans-edition/
There are better solutions, though, than what I have done in my script. Just check the comments on my post.
I hope this helps ;-)
There's a few parts to this.
First, Maintenance Plans will build a set of actions that can back up your database, defragment your index, perform DBCC checks, and more. The problem is that maintenance plans suffer from a lack of flexibility and have some restrictions. I talk about it in my tutorial video on maintenance plans:
Instead, you can choose to write your own T-SQL scripts to do database backups. That'll give you more flexibility and power. We link to some good backup scripts off that video link too.
Second, no matter which method you choose (maintenance plans or custom T-SQL scripts), the jobs will be run on your schedule by the SQL Server Agent. The Agent is a job scheduler built into SQL Server.
I use the maintenance plans, they were easy to setup and so far haven't failed. I have them backing-up my databases to a SAN, you must supply the UNC name for the directory path eg \\archive\SQL\database\fullbackup.bak.
As I grow more with SQL server I will most likely move to scripts, as viewing Brent's video showed me it is better in the long run.
Network backup is possible with SQL Server Agent...
You just need to change an account wich used for start SQL Server Agent service to account with access to network share (and system service operations for sure) - e.g. any domain admin account...
But local disk backup and xp_commandshell it's really much more reliable. You can script result check/distribution to several servers and so on...