Skip to Content
author's profile photo Former Member
Former Member

How to best backup a 4.6c system / msSQL 2000

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!


Adam Oakeley

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Oct 22, 2006 at 04:59 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.