3

So I have a database (on a SQL Server 2008 R2 SP2 instance) that is 2TB uncompressed, with a .bak file that is 500GB compressed. As I have been toying with the fastest way to restore this database after a series of tests, I've tried a couple of things:

  • Restoring after dropping the database
  • Restoring with Replace

When I restore after dropping the database, the fastest I can get a restore going is 4 hours. This is when I put the .bak on a RAID 10 logical drive, with the data files being written to a separate RAID 10 logical drive on the same server. The log files go to yet another RAID 0 on the same server.

However, if I restore with Replace, the restore process only takes 56 minutes (similar setup). Did I find some sort of turbo button? This is so fast that I am worried.

Improve this question
2
  • @Sean - is there a reason you asked the same question twice?
    – Hannah Vernon ♦
    Commented Oct 30, 2013 at 4:20
  • 1
    I completely forgot about that, although that was a more theoretical question and I asked this one because I thought something had gone wrong (due to how fast it was). I can merge the information between the two once I get some time though. Thanks for linking them!
    – Sean Long
    Commented Oct 30, 2013 at 15:05

1 Answer 1

Reset to default
3

The RESTORE DATABASE ... WITH (REPLACE) does not have to create the 2 TB .mdf file. This is why it is so much quicker. You may want to look into instant file initialization.

Improve this answer
4
  • I actually had tried that too, and I don't remember it being under an hour. I looked at the Technet article on restoring, but it doesn't look like ... WITH REPLACE does anything terrible, I think I just found a turbo button.
    – Sean Long
    Commented Oct 29, 2013 at 22:45
  • Instant File Initialization will only "instantly" initialize the .mdf (database data file). It will not instantly initialize the .ldf (log file). Perhaps your log file is fairly large, which would account for the restore taking longer, but not quite as long, as without instant file initialization.
    – Hannah Vernon ♦
    Commented Oct 29, 2013 at 22:47
  • The log files are actually only 100GB. I didn't measure them on the previous attempts, so that might be the difference. good catch.
    – Sean Long
    Commented Oct 29, 2013 at 22:53
  • 100GB is actually decently large - enough to take a substantial amount of time. I could see it taking on the order of 20 minutes or more, depending on the speed of your disk subsystem.
    – Hannah Vernon ♦
    Commented Oct 29, 2013 at 23:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.