Log ship issue. Restored database and it''s still loading....
-
I'm running a customized log shipping method.
Here's the setup:
SQL2000 standard edition - 4GB RAM - 4x1.9Ghz Processors Xeon MP - 220GB HD
Step-by-step setup:
Set the database to full recovery.
Performed a full backup from production. 17 GB
Created a maintenance plan to locally backup transaction logs every 15 minutes and delete every 6 hours. Also created a separate backup plan 12:00am daily
Restored the database on the stand by server using this script.
RESTORE DATABASE MYDATABASE
FROM DISK = '\\SERVER1\TEST.BAK '
WITH NORECOVERY, STATS = 1,
MOVE ' ' TO ' ' ETC.
Created custom stored procedures to store path files, planID, source and destination servers.
Created a folder on the STANDBY server for log copy and load purposes.
Again the database is 17GB and the transaction logs average to about 40MB.
My question:
It's been 2 days and the STANDBY server says its still "loading" when I am expecting to see "read only"
I'd like to figure out how to by-pass the "loading" portion. When I try to click properties it says that its in the middle of a restore. I just need help completing the last portion of the log shipping process. Also do I have to specify "WITH MOVE" when restoring the database on the STANDBY server? Can't I just do a simple database restore using this?
RESTORE DATABASE MYDATABASE
FROM DISK = '\\SERVER1\TEST.BAK '
WITH NORECOVERY, STATS = 1,
-
The "WITH NORECOVERY" clause means that the database will stay in a restoring state so you can restore multiple backups and maintain transaction consistency. Without NORECOVERY, SQL Server would rollback any uncommitted transactions. The database will stay in this "loading" state until you perform a restore "WITH RECOVERY" which is usually done after the primary has failed.
If you're restoring over an existing database, then yes you can omit the "WITH MOVE" clause.
--------------------
Colt 45 - the original point and click interface
-
If you want to use the log-shipped database as read-only, you need to use the STANDBY=<file> option in your RESTORE statements (in place of NORECOVERY). This allows it to recover partial transactions after a RESTORE LOG to produce a readable database, but at the next RESTORE LOG it can undo what it recovered (restore the previous partial transactions) before continuing with the new log activity.
The standby file can be a constant literal, it can reuse the same file over and over for every RESTORE LOG (for the same database). Each log-shipped database will require a separate standby file.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply