Database Backup and restore of SQL Server database ____ Restore the database hint "the media set has 2 media families, but only 1 are available." All members must be provided "

Source: Internet
Author: User

Tags: stored procedure eva puzzle SYS attach should be obtained by modifying

During the backup and restore of the database, I encountered a problem "the media set has 2 media families, but only 1 are available." Must provide all 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 I restore the database today, I only selected the D disk backup file "Yzteacherevaluate", prompting for an error: the restore failed for the server "192.168.24.101". Additional information: System.Data.SqlClient.SqlError: The media set has 2 media families, but only 1 are available. All members must be provided. (MICROSOFT.SQLSERVER.SMO). See picture 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 data file does not include the structure information of the database, and if" append to an existing backup set "is selected in" Options "in the backup, then the file does not say that a file has 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 update, that is, 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, regardless of the way to restore, you may receive the following error "because the database is in use , so the exclusive access to the database cannot be obtained ", 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".

Database Backup and restore of SQL Server database ____ Restore the database hint "the media set has 2 media families, but only 1 are available." All members must be provided "

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: