Backup strategy for SQL databases

[ObiwanSte]

Hi,

Im new to SQL so please bear with me.

We have a sql 2000 server with an internal DDS tape drive. It is a backup device in sql. This is what the backup strategy I am looking for. (all to the dds tape)

1. Full backup of all sql databases (overwriting tape)
2. Incremental backup of all databases (log files) (appended to tape)
3. Incremental backup of all databases (log files) (appended to tape)
4. Incremental backup of all databases (log files) (appended to tape) (spit tape out for next tape)

I understand it can be done, but have no idea of how to make it happen.

Any help would be appreciated.

Thanks

Steve

 

[SQLBill]

With SQL Server is the Books OnLine (Start>Programs>SQL Server>Books OnLine) and is refered to as the BOL. If you go to the BOL, use the Index tab and enter BACKUP DATABASE you will get a lot of good information.

Basically, you need to use the BACKUP DATABASE command and your first backup will use the option WITH INIT. That will overwrite the backup file. The following backups will require two options WITH NOINIT, DIFFERENTIAL. Those options will cause the backup to append to the file and it will only do a DIFFERENTIAL (incremental) backup.

Once you decide what commands you need to run, create a Job to run the commands. Easiest way is using Enterprise Manager, drill down to Management, SQL Server Agent, Jobs.

You also need to look into backing up your log files (BACKUP LOG).

-SQLBill

 

[ObiwanSte]

Cheers Bill.

Steve

 

[pweegar]

You wil also propably want to set up maint. plans that will back up your db's, the master db, and run re-indexing and compacting. Again look at BOL on how to do it.

To set up a maint. plan, open the Database tab under the instance of SQL Server, right click on your db, then choose maint. plan and a wizard will start.