The media set has 2 media families but only 1 are provided. All must be provided (Microsoft.SqlServer.SMO)

Microsoft SQL Server, Error: 3132

The media set has 2 media families but only 1 are provided. All must be provided (Microsoft.SqlServer.SMO)

Causes: While taking database backup from SQL server 2005, in the Back Up Database dialog window à Destination list box; if more then one path is available then SQL server creating back up in all available files and Media Sequence: Media 1, Family 2….n.

If 2 files path available then back created in 2 files, if 3 then back created in 3 files etc

In the above case .bak file would be created with Media Sequence: Media 1, Family 2. SQL server will create two database backup file in two (two file location mention in above destination box) locations.

And while restoring you must need all two files to restore database successfully.

If there are two paths in path selection box as shown in image above, the backup file is generated in two parts and saved at two different locations.

Here we have two backup files generated for selected database. Now at the time of restoring if you are assigning only one file and try to restore the database, you will get “SQL Server Error: The media set has 2 media families but only 1 are provided. All must be provided (Microsoft.SqlServer.SMO)” error.

Solution:

1. While creating back up file make sure only one file available in the Back Up Database dialog box à Destination list box. SQL server will create complete database backup file in one location only.

Follow below procedure to take back up

1) Go to restore database

2) Select the database that you want to back up to

3) Locate the backup file on disk. You may have to put it into the MSSQL Server -> MSSQL.1-> MSSQL -> Backup Folder. It must be a .bak file.

4) Select the back that want to restore from the available backups.

5) Go to the top left "options" property and when you do that select "overrite existing database".

6) Now make sure that the paths to the files on database to be restored are correct in this same dialog view. Look at the paths to the database file and the log file and make damn sure that they are the correct ones for the database to be restored. The problem here is that those paths are going to be for the filesystem that the backup came from, not the one you are goning to put the restore onto. That's the big problem here.

2. If you are aware about all destination files path then select all files path while resorting, add all the files (in above case two files) in destination list box.

And while restoring you must need all files to restore database successfully.

In short

1) You are trying to restore incomplete back up file or only 1 part of database back up file

2) You are trying to restore corrupt database backup file

3) You have not selected all database backup file path where all parts of back up files are present

Most of the situation you must not aware about all paths selected for back up, and you copy the back file from selected (from one location only) location (This is not complete back up files) and tried to restore that time you will get this error.

To avoid this situation make sure only one path available in the destination list box and copy back up file from available file path (This time you are coping complete database back up file) and while storing you will not get any such type error.