Restore Database With Partitions Excluded (or Selected Partitions)

Refreshing QA environment is not an easy work when your backup files are terabytes in size. The challenges are not only on space but also on time. Some tables are never envolved into QA phase. Restoring them will waste of your time and space. If you put tables in different file groups, then you would have the option to not restore them. If they are accessed by QA test, you can rename existing corrupted table (file groups are not restored) and create an empty table to make the logic correct. Now, if there is a huge table holding the historical data for the system, QA only need the latest 2 partitions of worth of data for testing, most of the DBAs in this case will restore entire database fist, switch useless partions out of the table, drop the table and file group. What if you have space limitation that only allow you restore part of the database? The solution would be

  • Restore database with primary file group and desired file groups
  • create table empty table which has the same structure as original, including the partition scheme
  • Swith the partition from existing table to the empty table
  • Rename tables

That’s a big work especially when you have hundreds of partitions across multiple tables. Here I post my script for that. It supports full, differential, log, and point in time retore, as well as multiple partitions over tables.

Read more