backing up SQL7

[TerryWK]

I am planning a new backup routine for an SQL7 server and am confused by the options available for backing up. Do I backup all the dbs shown in the list and what about filegroups and Full or Diff?

Thanks

Terry

 

[SQLBill]

I'm using SQL Advanced Server 2000 and BE 6.5.

I backup the databases and not the filegroups. I got that from Veritas' website.

A full backup is just that....fully backs up your database. A differential backs up any CHANGES made since the last full backup.
A transaction log backup backs up the transaction log.

So, how do you want to be able to restore your database? To a given point in time? To whenever you last did a full back up?

Doing transaction log and differential backups allows you to restore to a point in time.

Example:
Do a transaction log backup every 8 hours. A differential every night (except friday) and a full back up every friday night.

If your system goes down Tuesday at 9 AM, to restore it you will restore the full back up from friday, the differential from Monday night and any transaction log backups you did on Tuesday.

-SQLBill

 

[Zelandakh]

Terry and SQLBill,

I was going to turn it into an FAQ once, but time marches on...

Using SQL 6.5 onwards, (this works on any version), use the SQL file grooming to knock the database to disk. Stick it in the mssql\backups folder. Then use BE to back up this TEXT file.

1. You don't need SQL agent.
2. BE backs up faster.
3. Database gets corrupt, restore from disk not tape.
4. Server gets hosed, you got it on tape.

Problem with SQL backups is that the restore doesn't always work. With this method it does.

 

[paulovey]

Hi,

Just to add a bit to the above. If you are running the SQL agent in Backup Exec (BE).

You need to run a full backup within the SQL maint plans, otherwise you get a failed status in the BE backup log, with something to do with "tablespace" i cant remember the exact wording.

Also its best to backup all DBs within SQL.

But take in account you cant backup the transaction log on the Master, Db.

I'm not sure on v7, but on v2000, if you have the option to
"Fix minor problems" enabled on the master db maint plan it will fail.

Paul

 

[polymath5]

Just use SQL to backup to disk and then backup those files to tape. Much faster to restore from Disk. Zelandakh is exactly correct. Scrap the SQL agent and go with the much faster straight text backup.