How SQL Server restores a database if a filegroup is missing

How SQL Server restores a database if a filegroup is missing

I. 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 the A library in the past to revert to B library (backup restore for primary partitions can be consulted:SQL Server Maintenance Plan Backup primary partition and Primary partition Backup and restore ), in order to the new data for a, B table, I modified the name of a, a, a and a new two non-partitioned a a table;

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;

Ii. process of Settlement

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

650) this.width=650; "title=" clip_image002 "src=" http://images.cnitblog.com/blog/48305/201408/291705172826475.jpg "alt=" clip_image002 "width=" 558 "height=" 395 "border=" 0 "style=" border:0px;background-image:none;margin:0px; padding-left:0px;padding-right:0px;padding-top:0px; "/>

(Figure 1: Filegroup not found)

650) this.width=650; "title=" clip_image004 "src=" http://images.cnitblog.com/blog/48305/201408/291705235639655.jpg "alt=" clip_image004 "width=" 558 "height=" 215 "border=" 0 "style=" border:0px;background-image:none;padding-left:0px; padding-right:0px;padding-top:0px; "/>

(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!

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Background-color:rgb ( 255,255,255); border:none rgb (221,221,221);/>

Use [Master]gocreate DATABASE [qqmonitor] on (filename = n ' E:\DBBackup\DBName.mdf '), (filename = n ' E:\DBBackup\DBName_lo G.ldf ') for Attachgo

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Background-color:rgb ( 255,255,255); border:none rgb (221,221,221);/>

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

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Background-color:rgb ( 255,255,255); border:none rgb (221,221,221);/>

--also the primary partition restore DATABASE [testant]filegroup= ' PRIMARY ' from disk= ' F:\DBBackup\Ant_Primary\Ant_Primary_ 20110916000001.bak ' with FILE = 1,move n ' barefoot.ant ' to n ' F:\DBBackup\TestAnt.mdf ', MOVE n ' barefoot.ant_log ' to n ' f:\ Dbbackup\testant_log.ldf ', recovery,replace, STATS = 10GO

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "style=" Background-color:rgb ( 255,255,255); border:none rgb (221,221,221);/>

How SQL Server restores a database if a filegroup is missing