SQL Server partial file and file group restores with recovery or standby option fails

SQL Server partial restore of file and file groups along with recovery or standby option to a destination where the database does not exist fails with an error message.

Symptoms

When you restore selected SQL Server file and file groups to a destination where the database does not exist, and with the recovery or standby option selected, the restore operation fails with the following error message. The database is restored and remains in the ONLINE or RESTORING state.

Error Code: [30:299] Description: Query Result [Microsoft.SqlServer.Management.Common.ExecutionFailureException:The file 'File_One' of restored database 'db_name' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.

Causes

This is a SQL behavior when you perform a piecemeal (partial) recovery of file and file groups to a new destination where the database does not exist. The restore operation completes and the selected files and file groups are recovered in the destination database. Since the database restore is not complete for other read-write files and file groups, the error message appears when you bring the database online.

Resolution

To avoid the error message, run the restore operation without selecting the Recovery or Standby option. After the restore operation completes, manually bring the database to ONLINE state using the command RESTORE DATABASE [db_name] WITH RECOVERY.