I am having a hard time setting up a backup plan for my sql server databases. Right now I create a copy of each database on another machine on the network once a day. Once a week I upload a MSAccess version to a remote site. Obviously there are huge amounts of potential data loss if a crash of some sort would occur locally.
My new plan is looking like this:
1) Run a complete database backup once a day
2) Run Transaction Log backups every hour
My questions are as follows:
1) What role does a "Backup Device" play in this process (I currently do everything through the Enterprise Manager interface)?
2) I am having a hard time scheduling mydatabase_complete.bak and mydatabase_trans.TRN at all. I get several errors.
3) I would like to run these backups on another network machine but am not seeing how to access "My Network Places" from the "Backup Database" utility in Enterprise Manager.
Any help in the form of answers or pointing me towards a useful resource for this process would be much appreciated!
1)A backup device is a logical name for a physical backup location that can be either on disk (a disk device) or tape (a tape device).
Rather than than having to specify the full pathname to the physical device, you can refrer to it by the Backup Device name.
2)What errors are you getting? Assuming that you have created a Job for both your Full and Transaction Log backups, you can simply set the schedule you want on the 'Schedules' tab. If you are using SQL Server 2005 you can pick from a list of existing schedules.
3)If you want to backup to a share on a network, you will need to specify the location using the full UNC path. You will also need to make sure that the MSSQLServer service is running under a Domain account rather than LocalSystem and that the Domain account has sufficient access to the share (i.e. Write permission).
I would advise you to take a look at BOL (Books On Line) to find out more about Backup/Restore.