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

Solution 1:

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.

Solution 2:

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 ;-)

Solution 3:

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.