You can recover your SQL database using the piecemeal restore after crashes, corruption, or other disasters. This method helps you recover large-sized databases from backup with minimal downtime. A piecemeal restore allows you to restore the database filegroups in stages, but it requires a valid filegroup backup. In this article, we will explore piecemeal restore—what it means and how to implement this type of recovery. If such a backup is unavailable or corrupted, you can use Microsoft’s DBCC CHECKDB repair option (REPAIR_ALLOW_DATA_LOSS), which carries the risk of data loss, or use repair tools.
What is Piecemeal Restore in Microsoft SQL Server?
Piecemeal Restore in SQL Server is a staged recovery technique for multi‑filegroup databases. It helps you restore a large database file at filegroup level rather than completely at once. The primary filegroup is always restored first so the database can come online quickly and secondary filegroups are restored later. This allows critical data to be available sooner while less essential data is recovered later.
Piecemeal restore is supported under all recovery models - simple, full, and bulk‑logged. However, under the simple recovery model, it applies only to read-only filegroups after the initial restore of primary and read/write filegroups. Additionally, Enterprise Edition supports online piecemeal restore (other filegroups can be restored while the database remains accessible), whereas Standard Edition supports only offline piecemeal restore.
How Piecemeal Restore Works?
Piecemeal restore works by bringing the database online quickly with the primary filegroup (and required read/write filegroups), then gradually restoring secondary filegroups. In Enterprise Edition, filegroups can be restored online while the database remains accessible. In Standard Edition, restores must occur offline. The filegroups that not yet restored remain offline and are marked as “recovery pending”. And these remaining filegroups can then be restored later, one by one or in groups, depending on recovery goals and business needs.
Prerequisites for SQL Piecemeal Restore Process
To perform a piecemeal restore, you should check following things:
Multiple Filegroups in Database:
Make sure your database has multiple filegroups with this command:
USE JohnCompanyDB;
GO
SELECT name, type_desc
FROM sys.filegroups;
If the database is not accessible, then you can open SQL database in EMERGENCY mode or simply run the following command:
SELECT name, physical_name, type_desc
FROM sys.master_files
WHERE database_id = DB_ID('JohnCompanyDB');
Recovery Model:
Piecemeal restore works with all recovery models, but if the database uses FULL or BULK_LOGGED recovery, then it is more flexible. You can easily perform a piecemeal restore on database containing multiple filegroups as these models support point-in-time recovery and maintain detailed transaction logs. However, under the SIMPLE recovery model, there are some restrictions. You must restore the primary and all read/write filegroups first, and then restore read-only filegroups later.
File Group Backup File:
Make sure you have the correct set of backup files. For SIMPLE model, you require a full or partial backup containing primary filegroup and all read/write filegroups and separate backups of read-only filegroups. And for Full or BULK_LOGGED, you need backups of at least primary filegroup and others according to your restore plan.
To check which model your database is using, run this command:
Check Backup Integrity:
You should also check the integrity of backup file to ensure it is free from corruption. For this, run the following command:
RESTORE VERIFYONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\JohnCompanyDB_Log.trn';
Initial Restore Sequence:
Use RESTORE DATABASE ... PARTIAL to restore the primary filegroup (and all read/write filegroups in SIMPLE), then restore other filegroups as needed.
How to Perform SQL Server Piecemeal Restore?
The way to carry out this restore method depends on the recovery model in use:
Piecemeal Restore in Full/Bulk-Logged Recovery Model:
Under the full or bulk‑logged recovery model, piecemeal restore can be used on any database with multiple filegroups and it allows recovery to a specific point in time. You can start with a partial restore of a primary filegroup and secondary filegroups (optional). If you want to perform point-in-time recovery, then specify the time correctly in sequence. Next, you can restore the additional filegroups.
In Enterprise Edition, secondary filegroups can be restored while the database remains online. The consistent read-only filegroups can be recovered directly without restoring data. To do so, run these commands step-wise:
-- Step 1: Restore PRIMARY filegroup
RESTORE DATABASE JohnCompanyDB
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\JohnCompanyDB_Primary.bak'
WITH PARTIAL, NORECOVERY;
-- Step 2: Restore FG_TV filegroup
RESTORE DATABASE JohnCompanyDB
FILEGROUP = 'FG_TV'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\JohnCompanyDB_TV.bak'
WITH NORECOVERY;
-- Step 3: Restore FG_Laptop filegroup
RESTORE DATABASE JohnCompanyDB
FILEGROUP = 'FG_Laptop'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\JohnCompanyDB_Laptop.bak'
WITH NORECOVERY;
-- Step 4: Apply log backup
RESTORE LOG JohnCompanyDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\JohnCompanyDB_Log.trn'
WITH RECOVERY;
SQL piecemeal recovery in SIMPLE Recovery Model:
Under this model, piecemeal recovery applies to read-only filegroups. You need to restore the primary file group and all read/write groups. And at this stage, your database remains offline. Once the restoring process is complete, then the database comes online with restored filegroups available. Next, restore read-only filegroups one-by-one while the database stays online.
To restore piecemeal recovery in SIMPLE RECOVERY model, run these commands in sequence:
-- Step 1: Restore PRIMARY filegroup
RESTORE DATABASE JohnCompanyDB
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\JohnCompanyDB_Primary.bak'
WITH PARTIAL, RECOVERY;
-- Step 2: Restore FG_TV filegroup
RESTORE DATABASE JohnCompanyDB
FILEGROUP = 'FG_TV'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\JohnCompanyDB_TV.bak'
WITH RECOVERY;
-- Step 3: Restore FG_Laptop filegroup
RESTORE DATABASE JohnCompanyDB
FILEGROUP = 'FG_Laptop'
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\JohnCompanyDB_Laptop.bak'
WITH RECOVERY;
Challenges with SQL Server Piecemeal Restore
This restore type has few limitations as given below:
- It must be performed according to the recovery model.
- Database must go offline during the initial restore of the primary and read/write filegroups, which is disruptive in large production environments.
- In the Full Recovery model, point‑in‑time restore requires the same STOPAT value in every step. Mismatches can cause restore failures.
- Requires consistent filegroup backups. Damaged backups can lead to errors.
- Online restore of secondary filegroups is supported only in Enterprise Edition.
Piecemeal Restore Fails? Repair SQL Databases with Minimal Downtime
If the piecemeal restore fails due to corrupt backup file, missing transaction log file, or un-restorable file group, then you can use the DBCC CHECKDB command to repair the SQL database. It may cause data loss because it resolves corruption by deallocating or removing damaged elements. To prevent data loss, use Stellar Repair for MS SQL. This professional SQL repair tool mdf quickly fixes corrupt MDF and NDF files directly regardless of the recovery model. It also helps you perform specific object recovery without data loss. It can even work when your file group backups are incomplete or corrupted. It supports all versions of SQL Server. Also, it allows you recover database including pages, indexes, with complete integrity.
Conclusion
In this article, we have discussed the process to perform piecemeal recovery in SQL. This approach can be used to restore large enterprise databases at the filegroup level when downtime is unacceptable. It helps reduce downtime, but it has limitations since it requires readable filegroup-based backups and may not be suitable if all filegroups are equally important. Transaction consistency must also be carefully managed. If the backup SQL file is corrupted or unavailable, you can take the help of Stellar Repair for MS SQL to repair the database file quickly and with reduced downtime.
Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing.
7 min read
