Post reply

Backup & Restore time

  • January 5, 2018 at 5:30 am

    #402803

    I know this is not easy to answer as it depends on kind of infrastructure organisation has. I am working on migration project and we have 1 TB database (5 databases) which needs to be backed up and restored on to new server. How long does it take to do this (approximate time)? I was told it will take at least 2 days (more than 48 hours) to do this. is this reasonable?

  • January 5, 2018 at 5:49 am

    #1974649

    It depends completely on the IO subsystem, how fast it can read and write the DB and, if there's a network copy involved, how long that will take.

    48 hours for what, to back up, copy and restore 5 databases that are each 1TB?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 5, 2018 at 5:51 am

    #1974650

    I think network copy is taking very long time. it seems 80% of the time is involved in copy those databases. All together it is 1 TB. 2 big databases and others are very small. 400, 400, 100, 50, 50.

  • January 5, 2018 at 6:30 am

    #1974665

    Test if it's faster to move the databases by hand rather than over the network (copy to external drive, take to server room). Assuming not cloud, that is.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 5, 2018 at 7:17 am

    #1974679

    Thanks for the suggestion - will try this.

  • January 5, 2018 at 7:21 am

    #1974680

    Do you have Instant File Initialization enabled on the new server?  If not, you may find that a large proportion of the time is spent zeroing out the data files before a byte of data is even restored.

    John

  • January 5, 2018 at 12:30 pm

    #1974749

    If these databases are being backed up regularly, from job history you can tell how long the backup part would take. For network copy, a simple test will give you a ballpark number. For huge files I prefer to use FTP as simple file copy in Windows will consume lots of memory (file system cache), which is a no-no on database servers.

    And of course, backup with compression will save lots of file transfer time.

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

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