locked
Hi, RRS feed

  • Question

  • See " i have a 500 GB databse which has to be moved from one server to another server and that should be with the  very minimal say the least downtime  a company can afford to. which methods should we follow?

    the answer i know is

    1) copy database wizard,

    2) attach and detach

    3)backup and restore

    4) generate script wizard and excute in destination server.

    Which is method should we choose so that the downtime is very very minimal? 


    Thanks in advance.
    • Edited by rajkat85 Thursday, June 13, 2013 10:48 AM
    Thursday, June 13, 2013 10:47 AM

Answers

  • Here is a flow that describe how you achieve seconds of downtime and zero transaction loss using the backup and restore method:

    1. Make sure db is in full recovery
    2. Backup database
    3. Restore database
    4. Backup log
    5. Restore log
    6. Set db in single user (users has no access to database)
    7. Backup log
    8. Restore log
    9. Point app to the new SQL Server
    10. Set restored database to multi_user (users has access to database)

    The time consuming parts are 1-5, but the users can keep working against the database during these steps. Steps 1-6 can be done within a few seconds (if you have prepared and rehearsed the steps, of course). I.e., the downtime from the end-users perspective is only a matter of seconds. Exactly how long down-time you have depends on your skill (how well you have rehearsed this) and how high transaction load you have.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by rajkat85 Thursday, June 13, 2013 5:03 PM
    Thursday, June 13, 2013 2:01 PM

All replies

  • Min downtime

    I go for Attach Dettach


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, June 13, 2013 11:01 AM
  • Backup and restore where you also use log backups, so the downtime for your users is only during the last log backup and restore - likely a few seconds.

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 13, 2013 11:11 AM
  • i prefer backup and restore


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, June 13, 2013 11:19 AM
  • Backup and restore where you also use log backups, so the downtime for your users is only during the last log backup and restore - likely a few seconds.

    Tibor Karaszi, SQL Server MVP | web | blog

    Do you think Db created by backup and restore will be exact replica of Source DB,what if some transaction happen during restore operation of log(in destination) , on Source ,that will not be there in destination

    Please can u explain it wud be good learning..detach attach guarantees u exact replica of DB so i was considering that


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, June 13, 2013 11:23 AM
  • Hi Shanky,

    I see downtime in op's question. So it sounds that there will not be any user interaction or the db will be kept in single user mode mostly. If thats the case, then backup and restore is a good option as Tibor suggested.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, June 13, 2013 11:28 AM
    Answerer
  • Thanks Latheesh...even if backup restore does not guarantees all transaction in destination but its will be faster than Attach dettach..also user must take Full ,diff and trn log backups for lesser downtime

    Thanks again


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, June 13, 2013 11:45 AM
  • detach and attach best fro minimal downtime. backup and restoration the database will take more time than attach the database.

    Thursday, June 13, 2013 1:33 PM
  • No. Detach and attach can never beat backup and restore *if* you also use log backups. You can literally achieve seconds of downtime with databases that are 100GBs in size or even larger if you use the backup/restore method.  See my other post for explanation.

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 13, 2013 1:55 PM
  • Here is a flow that describe how you achieve seconds of downtime and zero transaction loss using the backup and restore method:

    1. Make sure db is in full recovery
    2. Backup database
    3. Restore database
    4. Backup log
    5. Restore log
    6. Set db in single user (users has no access to database)
    7. Backup log
    8. Restore log
    9. Point app to the new SQL Server
    10. Set restored database to multi_user (users has access to database)

    The time consuming parts are 1-5, but the users can keep working against the database during these steps. Steps 1-6 can be done within a few seconds (if you have prepared and rehearsed the steps, of course). I.e., the downtime from the end-users perspective is only a matter of seconds. Exactly how long down-time you have depends on your skill (how well you have rehearsed this) and how high transaction load you have.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by rajkat85 Thursday, June 13, 2013 5:03 PM
    Thursday, June 13, 2013 2:01 PM
  • Thanks alot Tibor,

    I have one more question to you,this was asked in one of my interview recently.

    If i have a series of T Log  backup's say for ex. from 10 am to 4 pm with an time interval of 30 mins  that is 10:30,11 am,11:30 and so on till 4 pm.now when i try to restore the same only a particular backup's fail to restore say around 3 pm,3:30 and 4 pm got failed. what are the possible reasons for restore failure and how to fix it?

    can anyone please expalin this?

    Thanks in advance.

    Thursday, June 13, 2013 5:07 PM
  • Nobody can answer that question since we aren't given any error message.

    It is like saying that the car makes strange noise, exactly how do I fix it.

    Ask the interviewer for an error message, that is the correct answer to the interview question.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 13, 2013 6:30 PM
  • Thanks for your  reply Tibor,

    How do we refresh  system databases or user databases in sql server ? can u please write down the steps to do that and under what circumstances we refresh databases and why should we refresh them? how often de we need to do that?

    thanks alot in advance.


    • Edited by rajkat85 Monday, June 17, 2013 5:45 AM
    Friday, June 14, 2013 4:34 AM