How SQL Server restores a database if a filegroup is missing

SQL Server How to restore a database if a filegroup is missing

First, background

I have a library, because a, b two table data volume is larger, so the table is partitioned; in migrating a library to a new cluster, I only backed up the primary partition of a library in the past to revert to B library (backup and restore of primary partition can be consulted: SQL Server maintenance plan Backup primary partition and primary partition backup and restore), In order to enter new data for a, B table, I modified the name of a, a, and created two new non-partitioned tables of a and a.

This is not a problem to run, but at one time I separated the database B, copied the MDF and LDF files, now want to use MDF and LDF to restore to the B library error;

Second, resolution Process

The following error occurred while restoring using the Restore function of SSMs:

(Figure 1: Filegroup not found)

(Figure 2: Manually create a NDF file for an error)

In the restoration of the need to specify the NDF file directory, but because in the backup we only back up the main file, so there is no ndf file (note: The table structure is in the primary filegroup, so this will show the NDF file information), this time to delete the list of NDF, but found that the Delete button is not available, 1, as shown in

Manually create a NDF file in the corresponding directory, there is an error in Figure 2, because the file header cannot be read;

Can't you restore the database B? Do not worry, we put the above operation to generate SQL code, the non-need to delete the NDF file, the rest of the SQL code like the following, execute, restore the database B!

 Use [Master]GOCREATE DATABASE [Qqmonitor]  on(FILENAME=N'E:\DBBackup\DBName.mdf'), (FILENAME=N'E:\DBBackup\DBName_log.ldf' )  forATTACHGO

or write your own code directly, such as the following code:

--Restore Primary partitionRESTORE DATABASE [testant]FILEGROUP='PRIMARY' from DISK='F:\DBBackup\Ant_Primary\Ant_Primary_20110916000001.bak'  with FILE = 1, MOVE N'barefoot.ant'  toN'F:\DBBackup\TestAnt.mdf', MOVE N'Barefoot.ant_log'  toN'F:\DBBackup\TestAnt_log.ldf', RECOVERY,REPLACE, STATS= TenGO

How SQL Server restores a database if a filegroup is missing