2

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?

mozzer
  • 45
  • 7

3 Answers3

2

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.

Shane
  • 1,869
  • 4
  • 20
  • 34
2

If you are not very familiar with sql server i would recommend you to first of all be clear and sure about what type of backup you want and need. Pinal Dave a big guru in SQL Server has some nice articles, please check this http://blog.sqlauthority.com/2009/07/14/sql-server-backup-timeline-and-understanding-of-database-restore-process-in-full-recovery-model/

To backup a database, after you're sure about the backup type, you may want to follow the following instructions, which are very well ilustrated: _http://www.linglom.com/2008/01/12/how-to-backup-and-restore-database-on-microsoft-sql-server-2005/

HTH!

byte_slave
  • 356
  • 1
  • 4
1

There are a few ways to schedule a backup of SQL Server. Many utilize the SQL Server Agent. You can create a Maintenance Plan, which will in turn create a SQL Job for the backup. You can also create a SQL job directly with a backup script in it. I use a backup script found on this website for all of my production databases.

http://ola.hallengren.com/Documentation.html#DatabaseBackup

jgardner04
  • 298
  • 2
  • 10