7

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?

1
  • You can simply use any third-party tool to backup SQL Server automatically according to your schedule. For example, you can use SQLBackupAndFTP ( sqlbackupandftp.com ) or SqlBak ( sqlbak.com ). Jun 13, 2017 at 14:40

7 Answers 7

20

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.

3
  • 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. May 5, 2009 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, 2009 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. Jun 1, 2009 at 9:12
1

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

1

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.

0

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.

0

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

1
  • 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) Jun 1, 2009 at 9:14
0

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.

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .