Creating a Backup Plan

Question

Post reply

Creating a Backup Plan

bobbabuoy

Old Hand

Points: 378
More actions
August 13, 2006 at 10:57 am

#114600

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!

Thanks in advance~

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Norwich Newbie Points: 1 More actions · Answer 1

1) what do you mean by "what role does a backup device play?"?

2)Post your errors

3)One way of getting around the enterprise Manager network backup is to create the backup locally and use NTBackUp to copy that backup to a machine over the network. It's has the benefit of having a local and remote backup.

Declare @signature varchar(40)
Set @signature = 'The Revolution won't be Televised!',

Norwich

Norene Malaney SSCommitted Points: 1896 More actions · Answer 2

I am not sure if this is what you were asking by "what role does a backup device play?" but here goes ...

Backing up to a "backup device" registered in SQL EM allows you to:

1) Refer to the "backup device name" rather than having to specify the full backup file name in your backup command

2) Allows you to easily view the contents of your backup device through SQL EM. (O/W - you have to do a "RESTORE FILELISTONLY" on your backup file).

Hope that helps.

Have a good day,

Norene Malaney

bobbabuoy Old Hand Points: 378 More actions · Answer 3

So a Backup Device is much like a DSN to direct traffic to a specific database?

The "what role does a Backup Device play" was designed to say, is it better to schedule backups with or without a device? It appears now that it is a little better to use one.

So I have to have a seperate backup device for each type of backup (complete, transaction log, etc), right?

If I have more difficulty I will post my errors.

How do I use NTBackup?

Thanks!!!

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 4

NTBackup comes with a help. If you do not use it already, I suggest you just use xcopy. You can just easily add a step toa a SQLServer agent job with a batch file that does the copying. Just make sure that the login that runs the job has permission to write on the other machine.

But you can backup directly to another machine if you start sqlserver with a domain user that has permissions on the other machine.

Joachim.