Restore DB

  • ramyours2003

    SSChampion

    Points: 12129

    what is the procedure to restore a database of size 2 TB with a  minimal down time ?

    • This topic was modified 6 months, 2 weeks ago by  Dave Convery.
  • Thom A

    SSC Guru

    Points: 98447

    In what sense? Are you restoring over an existing database, or creating a new one. If the latter, there is no downtime, as such. If an existing one, I assume that the existing database won't be updated during the restore period, as it would be lost. If so, you could restore the database under a different name, then, once the restore is complete, rename the old database and then rename the new one (to the old one's name).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • ramyours2003

    SSChampion

    Points: 12129

    we need to restore to  a new  db on other servers , generally  how can we  achieve in a shorter time  not configuring  any high availability  techniques

  • Andrey

    Say Hey Kid

    Points: 691

  • Andrey

    Say Hey Kid

    Points: 691

  • Jeff Moden

    SSC Guru

    Points: 995102

    Then, restore all the main BAK and all the existing TRN files to the new server but make sure that ALL of those restores, including the last one, ALL use the NORECOVERY option.  When you're ready, take a "Tail Log Backup" on the original.  That will set the original to "Recovering" status and no one will be able to make any changes.  Restore the backup for for the tail log on the new server and use the RECOVERY option.  Total down time (for the restore, anyway) will be measured in short minutes if you prepare by making sure that you read up on it and have your scripts ready.

    I've done this many times for many databases where I work.  We did more than 64 databases of decent size and 1 of them was 2TB and another was 1TB.  Total downtime was about 20 minutes.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • RonKyle

    SSC-Dedicated

    Points: 31462

    I found that striping the database backup significantly improved both the backup and restore times.  That is the multiple files that is referred to in the article, but doesn't provide much information.

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

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