Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

7 Answers 7

up vote 20 down vote accepted

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.

share|improve this answer
1  
An interesting thing to note is that SQL cannot backup to network drives and will only perform the backup on the local computer. (or tape). I never really understood this decision. In order to have a reliable backup you have to copy those .BAK files to another box. –  Martín Marconcini May 5 '09 at 11:12
3  
@Martin: Because it's more reliable. You can always copy/move the backup to a network share immediately afterwards with xp_commandshell. –  Portman May 5 '09 at 15:10
2  
@Martin It can back up to a network drive using UNC providing the service account has network access rights. You just need to manually enter the path instead of picking from a dialog. –  Dave Dustin Jun 1 '09 at 9:12
    
Thanks, I didn't know that one.+1 –  Martín Marconcini Jun 5 '09 at 19:13
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
Providing the service account has network access rights, backup to a network drive will work just fine. Even including mapped drives, providing they were created by logging in locally with the service account and doing the mapping (mapped drives are profile/account specific) –  Dave Dustin Jun 1 '09 at 9:14
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.