log shipping question

xdai · June 22, 2009 4:44PM

HI, we are using sql back up 5.4. Just set up the log shipping between two servers. After the database backup restored to the dest server, should the server be able to view on sql server 2000 Enterprise Manager as read only state, and look into the properties? thanks

petey · June 22, 2009 10:29PM

Yes, you should be able to access the restored database in read-only mode if you had selected the 'Read only (RESTORE WITH STANDBY)' option on the 'Destination database restore options' page of the log shipping wizard.

xdai · June 23, 2009 12:09PM

i got the email notified that the restore completed successfully with norecovery, and the first t-log backup have copied to remote server successfully, but the database still in loading state, it is a very small db, after compress backup only 46M. is this normal? thanks

xdai · June 23, 2009 12:14PM

also, for the restore t-log job, after you create it, how do you edit it? is that you need to have sql backup pro to be able to edit it?

xdai · June 23, 2009 12:26PM

if the network share folder is the same as the dest server t-log restore location, would that a problem? thanks

xdai · June 23, 2009 1:07PM

i use non-operational (restore with norecovery), so it will be in restoring state? and won't be able to display the database structure, etc. and view the data ? But on sql server, if you use restore with norecovery, it will make the standby server dispaly as read-only state, is this a difference? thanks

petey · June 23, 2009 10:36PM

Let's address your original question first.

If you set up SQL Backup log shipping using the non-operational option for the secondary database, then the database won't be available for read-only access.

The same behaviour applies to SQL Server. Try the following using Query Analyzer/Management Studio:

CREATE DATABASE RGTEST
BACKUP DATABASE RGTEST TO DISK = 'c:\rgtest.bak'
DROP DATABASE RGTEST
RESTORE DATABASE RGTEST FROM DISK = 'c:\rgtest.bak' WITH NORECOVERY

What is the state of the database as displayed in Query Analyzer/Management Studio?

Thanks.

xdai · June 24, 2009 12:30PM

i use redgate backup a database, then restore it with the second option (on the step 3 recovery completion state): non - opertional, after restore, the database is in read-only state, and i am able to view the database completely when i check from sql server EM.

if i use command like this

restore database xxx from disk = 'n:\MSSQL\Backup\xxx.bak' with norecovery, replace.

we are still able to see the database is in read-only state, but if i ran the command you sent me, it is showing loading state.

why is that?

xdai · June 24, 2009 12:49PM

normally when we do the restore database, we will create a copy job and load job (using sql server job agent) right after, so the db is ready to load the new t-log. would that have the effect on the state of the db?

petey · June 24, 2009 11:07PM

i use redgate backup a database, then restore it with the second option (on the step 3 recovery completion state): non - opertional, after restore, the database is in read-only state, and i am able to view the database completely when i check from sql server EM.

I cannot reproduce this scenario using SQL Backup 5.4. Can you run Profiler to trace everything that's running after the restore, that may affect the state of the restored database?

if i use command like this

restore database xxx from disk = 'n:\MSSQL\Backup\xxx.bak' with norecovery, replace.

we are still able to see the database is in read-only state, but if i ran the command you sent me, it is showing loading state.

Again, I would suggest using Profiler to trace subsequent statements that may affect the database state. What you are describing is not possible, assuming no software or user errors.

Thanks.

log shipping question

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk