Post reply

Standby mode & database automatically updating

  • May 26, 2004 at 3:06 pm

    #180369

    Hi!

    Does anyone know how to put a database in StandBy Mode for SQL Server 2000.  I am trying to configure log shipping, but the secondary database needs to be in standby mode.  I have no idea how to do that.

    I am trying to have a fail over machine and I believe log shipping is the best way to have the machine update the secondary database automatically.  Any advise or recommendation will help! 

    Thanks!

  • May 27, 2004 at 5:23 am

    #507975

    Here's what we use for a similar purpose...

     RESTORE DATABASE '<database name>'

     FROM DISK = '<backup name>'

     WITH STANDBY = '<uncommitted transaction file>'

    contains the contents of data pages before uncommitted transactions affecting those pages were rolled back

     

    This makes it available in the normal fashion -

     RESTORE DATABASE '<database name>'

     WITH Recovery 

     

  • May 27, 2004 at 7:04 am

    #507996

    Know of anyway to do this without having to restore the database?  The database is existing and its huge...just didn't know if there was a way around having to do a recovery...

    thanks again!

  • May 27, 2004 at 7:08 am

    #507997

    The "with recovery" doesn't actually do a restore - it makes the database available for use.  When the database is in stand-by mode, it's read-only and "with recovery" sets it back to normal use.

  • May 27, 2004 at 7:15 am

    #507999

    But this statement will restore the database correct?

    RESTORE DATABASE '<database name>'

     FROM DISK = '<backup name>'

     WITH STANDBY = '<uncommitted transaction file>'

     

    Do you also know once I get the log shipping accomplished and we have it scheduled to run automatically, lets say every 15 minutes...then will the database have to remain in Stand by mode all the time? 

    Sorry if these are ignorant questions, but I'm new to the whole SQL Server arena. 

    Thanks a million for your help!

  • May 27, 2004 at 8:18 am

    #508026

    What is the purpose of "STANDBY Mode"?  If it is merely the read only aspect of that, then couldn't you use

    sp_dboption to set the read only mode of a database to true?

    ie exec sp_dboption [dbname], 'read only', 'true' 

    and then exec sp_dboption [dbname], 'read only', 'false' to resume normal operation

    Steve

  • May 27, 2004 at 9:40 am

    #508044

    With log shipping, you have to keep your standby database in "standby" mode so that further log backups can be restored.  If you recover it completely, the backup sequence is broken and you wouldn't be able to apply anymore log backups until a full database backup is applied.

    Jasmina, this article on sql-server-performance.com is a good guide on how to set up log shipping:

    http://www.sql-server-performance.com/sql_server_log_shipping.asp

    Greg

     

    Greg

  • May 27, 2004 at 9:59 am

    #508053

    Thanks for the help!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply