SQL recovery - partitioned table (file groups) - each on separate disk (and one of disk crashes)

145 Views Asked by At

For huge tables I am thinking of using the concept of partitioning using file groups and have each file group on a separate disk. My question is that -

  1. If one of the file group disk crashes, then should this incident be treated as database crash? Will it cause the database to stop working?
  2. Will the restore operation (assuming full backup was taken) automatically create the file groups as configured before the crash?
1

There are 1 best solutions below

0
On

If one of the file group disk crashes, then should this incident be treated as database crash? Will it cause the database to stop working?

If all your data from that disk is in memory, you'll not even notice this crash. Until a checkpoint attempts to write on that disk, or you need to read from it a new portion of data(if it's not in memory) you'll be able to work without any error.

Will the restore operation (assuming full backup was taken) automatically create the file groups as configured before the crash?

Your question is not clear.

You can restore certain filegroups from full backup, but if these filegroups are not readonly you'll not be able to reconcile them with the rest of your database. It will be only possible if your db is in full recovery model and you take and restore tail of the log backup after your full backup (and may be other log backups in between).

Here you can read more in details Piecemeal Restores (SQL Server)