The Situation: Our 2008 MSSQL server is old and rapidly running out of drive space. We have a new 2016 server ready, we just need to move a 40 TB database from the old server to the new one. The DB, of course, has dozens of tables, but the EVENTS table is the one that is eating up all the space. The EVENTS table is partitioned by month, and the partitions are spread out on multiple logical drives (we kept adding disks until we physically ran out of space).

The Question: How do we move this massive database from the old server to the new server with a minimal amount of down time? Outside of not upsetting the users, the table grows by about 15 GB per day, so every day spent offline creates a significant backlog of records that needs to be worked through.

The backup/restore method doesn't really seem feasible, as we have no place to store the backup.

Is it possible to move a partition at a time? (My research so far leads me to believe it is not)

We are a bit overwhelmed by the task, and any advice would be appreciated.

  • You say you have no place to store the backup - where are you storing it now (please tell me you're backing this up somewhere)? You can restore across the network if it's stored on a share, however it'll be open to failing if your network connection drops. I'd suggest this method for the first restore, then just configure log shipping to keep it warm until you're ready to cut over. – Dan Mar 19 '18 at 21:21
  • You are right, Dan, my problem is remarkable similar to the one you linked. I promise I searched for what I thought to be a reasonable time before posting a new question. We have the advantage of the servers being on the same LAN. No, the DB is not currently being backed up (I just walked into this problem last week, so please go easy on me) I will research log shipping. Thank you! – user2920594 Mar 19 '18 at 21:35
  • 4
    I'd prioritize getting backups configured asap then, before worrying about the migration. If you get pushback from management, ask that if one day of downtime is unacceptable for migration purposes, how does infinite downtime work? Also, it was @sp_BlitzErik that linked the other post, not me :) – Dan Mar 19 '18 at 21:48