0

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?
variable
  • 8,262
  • 9
  • 95
  • 215
  • What if you partition your disk, so all the partitions stay on the same one and have the benefit of performance ? I think that if a disk crashes, you'll lost the partitioned table. The SQL Recovery is complex, maybe you should check https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-and-recovery-overview-sql-server?view=sql-server-2017 to have an idea on how to do it if it crashes – LPK Sep 27 '18 at 09:31

1 Answers1

0

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)

sepupic
  • 8,409
  • 1
  • 9
  • 20