I made a backup of my database from ServerA (SQL 2000) and trying to restore it to ServerB (SQL 2005) and get this error below, even after configuring the log and data paths for the restore:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'serverbname'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
------------------------------
BUTTONS:
OK
This error message means that the original backup was done as a striped backup where the backup stream was split into two destination files.
When attempting to restore, you need to specify all of the same files which were used to take the backup.
Because this works very much like RAID1, we do not have one set of data on one file and another set of data on the other one, but rather the data is inerspersed between the two files, which is the behavior which gives optimum performace. One of the unfortunate results of this is the fact that you cannot get any data out of a subset of the backup files used to create the backup.
The reason that you cannot do a backup adding on to one of these files is that the files themselves are initialized to understand that they are part x of a y-part backup. If you specify only 1 file, it doesn't fit.
You can always perform a backup by specifying a new backup file, and using WITH INIT.
If you don't need to stripe the backups across multiple files for performance reasons, then I'd suggest going with a single destination for management simplicity.
So, the simple version is:
If you back up to 2 files, you must specify 2 files to restore from. If you back up to 5 files, you must specify 5 files to restore from, etc.
You cannot restore any data from less than the full set of files used for the backup. This isn't like RAID 5 where we can synthesize missing data, nor is it a continuation volume, where we could restore up to the missing bit.
You can use the following query to locate the missing parts (demo 2 parts):
SELECT [media_set_id] ,[family_sequence_number] ,[media_family_id] ,[media_count] ,[logical_device_name] ,[physical_device_name] ,[device_type] ,[physical_block_size] ,[mirror] FROM [msdb].[dbo].[backupmediafamily] /* media_set_id family_sequence_number media_family_id media_count logical_device_name physical_device_name device_type physical_block_size mirror 218 1 A899C345-0000-0000-0000-000000000000 1 NULL C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\MediaFamily1.bak 2 512 0 218 2 94DC7E1C-0000-0000-0000-000000000000 1 NULL F:\temp\MediaFamily2.bak 2 512 0 */