[thelist] MS SQL Server - Automated backup

[Anthony Baratta]

At 09:11 AM 9/25/2002, Michael K. Ahn wrote:
>Can anyone point me in the right direction with setting up an automated
>backup process for one or more MS SQL Server databases?  I would like to
>back it up on external media (ie tape drive), but backing up to a file
>on a hard drive is also a possibility.

Using Enterprise Manager, setup a new backup device. I would recommend that
you set that as a file versus directly to tape. Then create a backup job
using that new device. During the setup of the backup job, you can schedule
the job to run at predefined intervals. I setup all my backups to run nightly.

* Make sure you define the backup to overwrite, versus append.
* Make sure that you set MS SQL to clear the transaction logs after the
backup completes.

On high traffic sites, many people do a full backup nightly and then every
hour or two or three, run a transaction only backup. This way they get a
running change log of the DB throughout the day that can be applied to the
nightly backup in the event a restore is needed.

You'll then have a file or series of files that can be rolled to tape or
another hard drive for archival purposes.

Good Luck.
--
Anthony Baratta
President
Keyboard Jockeys

"Conformity is the refuge of the unimaginative."