How to best backup a 4.6c system / msSQL 2000

[Former Member]

Hi, I have a client with msSQL 2000. Currently trying to use log shipping for a "hot spare" and also backup the database to tape. As expected, using backup outside of the log shipping database plan results in 2 sets of un-usable backups. Is the best plan to use log shipping and backup using the log shipping plan and then backup the resulting backup chunk files to tape? What other pit-falls will we encounter if relying on msSQL backup API and resulting TLOG checkpoint issues?

Any insight is welcome!

-Thanks

Adam Oakeley

[Former Member]

Hi Adam,

We currently use a Quest Litespeed for MS SQL Backups (Full and Diff) to disk and tape in combination with standard MS SQL 2000 log shipping and are very happy with this combination.

I'm not sure exactly what you mean by "using backup outside of the log shipping database plan results in 2 sets of un-usable backups". Log shipping to standby database does not invalidate backups in any way that I am aware of. The main limitation I am aware of with MS SQL 2000 is that no transaction log backups are possible during a database backup. This means that your standby database can become out of sync with the primary for the duration of the backup. Is this what you are referring to? If so, the only way I know to avoid this is to upgrade to MS SQL 2005 which does not have the same limitation (provided your R/3 support package and kernel release support it).

For reference our backup schedule is as follows:

Full backup - Weekly

Diff Backup - Daily

Transaction Log Backup - every 15 minutes

Log shipping copy job - every 15 minutes

Log shipping restore job - every 15 minutes

We successfully restored our production system (over a test system) this weekend on this configuration.

Another limitation of MS SQL 200 we discovered recently is that tasks scheduled via the DB Maintenance Plan (sqlmaint.exe) e.g. DBCC CHECKDB, DB Optimization job, Transaction Log backup for log shipping) can only be executed in serial and not in parallel. We have observed this leading to beahviour like the transaction log backup job 'hanging' for the duration of a DBCC CHECKDB or DB Optimization job. This can be a long time (24 hours+) on a large database.

Have discussed with Micorsoft and best workaround is to schedule DBCC CHECKDB etc as standalone jobs rather than via maintenance plan. This leads to no conflict between the jobs. The limitation does not exist in MS SQL 2005.

[Former Member]

Also check the 'MSSQL Backups?' thread on this forum for more discussion of MS SQL backups.