Sql-server – How to you schedule a database backup on SQL Server 2005

sql serversql-server-2005ssms

Not really familiar with MS SQL Server, but I did figure out how to manually backup a database locally:

In SQL Server 2005 ( SP2 ) using MS SQL Server Management Studio :

I navigate to

Server->Databases->System Databases->master

Right click on master then:

Tasks->Back Up->Back up to:

I can then backup the master ( as well as one other db ) to a RAID 5 drive array. I am using Retrospect for my backup agent, and I have it copy the folder that the backups are in once a night to a tape drive.

At this point I have to manually do the backup in SQL Server, as I am not sure how to schedule the backup to run? I just want it to run once every evening. Can this be done in Microsoft SQL Server Management Studio, or does this have to be scripted somehow?

Best Answer

This can be done easily with most versions of SQL Server. For SQL Server 2005 start by opening SQL Server Management Studio. In your database instance drill down through the Management folder and then right-click Maintenance Plans then choose New Maintenance Plan...

From here add a Database Backup Task. Edit the task to set which databases to back up and set the schedule for how often it will run. I'd recommend adding a Maintenance Cleanup task as well. This will automatically delete backups after a certain amount of time.

That should cover you for a pretty basic backup schedule. Note that this will not work for SQL Server 2005 Express.