So far, we discussed many de-facto details about SQL Server database backup and restore. In this 15th article of the series, we are going to discuss, the file-group and piecemeal database backup and restore process.
Database “Backup and Restore” strategies are vital to every organization for smoother functioning of the business. Database design concepts are also important in defining the backup and restore strategy. A good database design structure and proper planning would give us an ample time to speed up the recovery process.
In this article, we will discuss the following topics:
- Introduction
- Explain file-group(s) level database backup and restore operations
- Discuss piecemeal database restore process
- Demo
- And more…
In some cases, taking full database backup is not a big deal, whereas, for VLDB databases or large OLTP databases, it may not be a feasible solution to initiate frequent full database backups in-and-out. In such scenarios, the file(s) and filegroup(s) backup and restore options play a vital role.
If you are operating VLDB database, in some cases, it becomes a daunting task to perform full database backup and restore as it may take several hours to complete the backup and restore operation.
Piecemeal restore helps with databases that contain multiple filegroups to be restored and recovered at multiple stages. This would give an option to customize the backup and restore (or recovery) solution.
Based on recommended practices and database design principles; if the database is designed to leverage data and segments to different file groups and store them on a different drive this provides a great advantage when doing backups of the database, and restoring the database in case of any database corruption or failure. Let’s say that one of the non-primary data files may become corrupt or otherwise it can go offline due to some hardware failure then there is no need to perform the full database restores, instead, only restore the filegroup that is needed. This operation will suffice or speed-up the entire restoration process.
Getting started
Let us jump into the demo to see how to perform the backup and restore operation.
In most of the cases, a single data file and log file works best for the database design requirement. If you’re planning to leverage data across multiple data files, create secondary file groups for the data and indexes, and make the secondary filegroup a default one for the storage. In this way, the primary-file will contain only the system objects. Then it’s possible that a single file group’s data file may become corrupted or otherwise go offline due to hardware failure or I/O subsystem failure. When this happens, there’s no need to perform a full database restore. After all, the rest of the file groups are all still safe and sound. By only restoring the file groups that need it, this way you can speed up the entire restoration process.
Further reading…
Filegroup and Piecemeal restore
