The SQL Server RESTORE DATABASE hint media set has 2 media families, but only 1 are available. Must provide a solution for all members "

> Today, during the backup and restore of the database, I ran into a problem. The media set has 2 media families, but only 1 are available. Must provide the There are members, the following is a detailed description of the problems encountered and problem-solving methods!

One, backup and restore encountered problem description and workaround:

The first two days of user testing We do the evaluation of the system of small software, a bit of a problem we used to look and want to back up their test data so that we can modify the software test use.

I backed up the database backup by default has a path: c\ ..., and I added a new path, did not delete the default path, but the new path is selected, I take it for granted that the database data back to my new path.

When restoring the database today, I only selected the D disk backup file "Yzteacherevaluate", prompting for an error: restore failed for server "192.168.24.101".   Additional information: System.Data.SqlClient.SqlError: Media sets have 2 media families, but only 1 are available. All members must be provided.   (Microsoft.SqlServer.Smo). See image for details:

I want to let the user back to my backup a issued, the results of his operation process, the database to delete, and then the C-disk database backup files sent to me, I mistakenly thought it was he back up, add a bit still prompts the above error. Suddenly remembered, the two files together to restore, should be OK.

Next, I restore the database, the C-drive and D-disk backup database file attached at the same time, the restoration is successful, the data is still, fortunately!

Second, the database backup and restore need to pay attention to the place:

1. When making a backup of the database, note that in the "target" of the backup, it is best to have only one path, if multiple paths, when restoring, you need to provide multiple Bak files to successfully restore, otherwise the restore fails, and the " media set has 2 media families, but only 1 are provided." Must provide all members "; The backup destination has only one path, and after the backup succeeds, the path generates a BAK file that is the structure information of the data file that does not include the database, if" append to an existing backup set "is selected in" Options "at the time of backup Then the file does not say that a file is only one backup of the data, but save all the backup information, when you want to restore, will be listed for you to choose which version you want to restore, if the "option" is selected " Overwrite all existing backup sets ", then there is only one copy of the latest , which means that once the data has a structural change, such as adding or deleting a table field, it is likely that the restore will not succeed.

2. Another area of note is that database backups can only be backed up to native

3. When restoring, there are two ways, one is "database Restore", the system will find in the installation directory of the database of the "latest" backup, one is "file" restore, that is, can be restored through the data file bak; In either case, the following error may occur " Unable to gain exclusive access to the database because the database is in use "Workaround: Restart the database server ;

4. The above is the backup and restore of the data, if it is to involve the database structure of the backup, such as table structure, stored procedures ..... Backup, there are two ways: a "Attach" MDF file, which of course will come with all the information, including data, but in the case of MDF, such as the database you want to copy is someone else's server, and your permissions can not get the MDF, only use the following method, Another way: first generate a script (task-build script) for the source database to get the structure of the database, and then "import data".

Source: Project experience--sql Backup and restore of the server database ____ Restore the database Tip "The media set has 2 media families, but only 1 are available. All members must be provided "

PS: If you do not delete the data, you can back up the backup history path before the backup to completely delete it.