August 28, 2002 at 5:16 am
How do you backup a database on a standby server using SQL2000 ?
I am planning an upgrade from SQL6.5 to SQL2000.
There is a 2 server environment with a primary and standby server.
The primary server is heavily used round the clock with frequent tran log dumps which are loaded onto the database on the standby server. The standby server is also used to run reports.
The database on the primary server is large (50 Gb) and activity slows down when the database dump is running. Because of this, the database on the standby server is dumped so activity on the primary server can continue. We could not afford to wait 2-3 hours for a dump on the primary server to complete. The database options set on the standby server are "read-only" and "no checkpoint on recovery". The database dump is required for disaster recovery purposes.
At SQL2000, I have restored the logs using "RESTORE log ... with standby..." but get an error 3036 when attempting to backup the database.
I also tried "RESTORE log ..... with norecovery ..." but got a different error when trying to backup the database.
Is my strategy wrong ? What are the alternatives I should be looking at ?
August 28, 2002 at 10:19 am
Not sure this makes sense. You get a restore error while the backup is running? If the backup is running, you will not be able to restore the system. Or shouldn't be.
Is the primary SQL 65?
Steve Jones
August 28, 2002 at 2:30 pm
Are you saying you are trying to backup a databse that is marked down. I don't believe you can perform a backup until you issue a recover on the database.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 29, 2002 at 4:13 am
I'll explain.
Currently, the 2 server environment is running SQL6.5. On the database on the Primary server, there are regular tran. log dumps around the clock. These are loaded onto the database on the Standby server. The database options set on the standby server are "read-only" and "no chkpt. on recovery". If there is a failure on the Primary server, users can be quickly switched to the Standby server after changing the database options.
Once a day, the tran. load schedule on the Standby server is held whilst the database on the Standby server is dumped to disk and then copied to tape. The Standby server is used as the dump needs to be in step with files from other systems. i.e. a "point in time" database dump is required as well as the other reasons I mentioned before (poor response times and 2-3 hour delay when database dump run on Primary server).
Having upgraded a test environment to SQL2000, I need to make some changes as, for example, "no chkpt on recovery" no longer exists. BOL suggests restoring tran. logs using "RESTORE log ... with standby...". I do this and its working fine. I can also run reports on the Standby server which is exactly what is needed. The next thing to test is the daily database dump on the Standby server. I try to run this it fails with an error 3036. So how do I get around this ?
Is it possible using SQL2000 to backup the database on a Standby server ? If not, what
alternative strategies would you suggest ?
Antares686 - not sure what you mean by "marked down". Could you explain please ?
August 29, 2002 at 5:23 am
Meaning it is not fully recovered. Your error is as follows.
quote:
Database '%ls' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed.
Meaning you must do a recover and bring the databse live in order to do a backup of the database.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply