How do I automatically run nightly backups for Microsoft SQL Server 2005?

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.

MSDN:

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:

http://sqlserverpedia.com/wiki/Database_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.

Which edition of SQL 2005 do you have? If you have the Express edition (the free edition) it does not come with SQL agent.

If you do have Express, you'll have to be a bit more creative. One option is using the Windows scheduler to call sqlcmd with a backup script.

If you don't have the agent, use the Scheduler to call a script like this:

BACKUP DATABASE MyDatabase TO DISK='C:\MyDatabase.bak'; 

Or similar…

Remember that C:\ cannot be a network drive it must be a locally connected drive. (No, mapped drives doesn't work either).

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...