Competition: What’s your favorite Redgate tool? Enter now.

log shipping question

xdaixdai Posts: 98
edited June 24, 2009 11:07PM in SQL Backup Previous Versions
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

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • 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?
  • if the network share folder is the same as the dest server t-log restore location, would that a problem? thanks
  • 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
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • 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?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.