Sql server - Restore failed for Server 'servebrname'.

Total Post:46

 1889  View(s)
Rate this:

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


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



  1. Post:397

    Re: Sql server - Restore failed for Server 'servebrname'.

    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]
     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

      Modified On Apr-10-2018 12:12:17 AM


Please check, If you want to make this post sponsored

You are not a Sponsored Member. Click Here to Subscribe the Membership.