Post reply

Switching Database state

  • March 24, 2005 at 2:37 pm

    #166519

    Hi Folks,

    Other than issuing a RESTORE DATABASE FROM devicename WITH NORECOVERY/STANDBY, or BACKUP LOG dbname TO devicename WITH NORECOVERY/STANDBY, is there any other method for switching a database from active mode to STANDBY/NORECOVERY?

    Thanks kindly in advance,

    David (wavie davie)

     

  • March 24, 2005 at 3:52 pm

    #548874

    You can take it offline, or put it in read only or single user mode, but I think thats not what you're looking for.

    I assume you have restored a full backup with recovery and now wish to apply transaction log backups.  I don't think thats going to be possible.  As far as SQL is concerned, the database is recovered, and is ready to accept new transactions which would make additional restorations impossible. 

    Steve

  • March 24, 2005 at 4:01 pm

    #548877

    Steve,

    Thanks for your reply.  It is more a case of someone wanting to swtich an active database to standby mode so that a differential backup may be applied.  The only two ways to do this, that I am aware of, are what I described in the original post.  The person asked me to see if anyone else in SQL Server land might know of a 3rd option.

     

    Thanks again,

    David

  • March 24, 2005 at 5:11 pm

    #548885

    David,

    The answer would be the same whether transaction log backup or differential.  Either way, SQL must ensure that there was no possibility of updates between restores, so the only way to apply diffs or tlogs is to restore the applicable full with NO RECOVERY.

    Steve

  • March 24, 2005 at 5:24 pm

    #548887

    Steve,

    Agreed.  That's what I was attempting to explain to this person, but I don't think they quite understand how databases work regarding consistency of log files, etc.

     

    Thanks again and have a great Easter,

    David 🙂

     

  • March 24, 2005 at 5:29 pm

    #548889

    David,

    Just ask them what you should do if you restore the full backup, wait a while allowing him/her to update his/her data, then place the database in STANDBY, and restore a differential backup, overwriting the update he/she has just made?

    NP and you have a great Easter as well!

    Steve

  • March 1, 2006 at 8:55 am

    #624019

    Hi everybody,

    We have a DR site about 1000km away from our primary site, so full backups of the databases can' be shipped on WAN (size @ 13gb). Hence I want to take backup on standby server. We have configured log shipping. Is it possible to stop log shipping for some time, open standby database in normal mode, take a backup, again make it standby & start log shipping. Please advice on the situation.

  • March 1, 2006 at 9:11 am

    #624025

    Please start a new thread....don't 'tag' your question onto someone else's thread. Especially one over a year old.

    -SQLBill

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

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