Fix: MS-SQL Server Restore Failed
Published on: 01 January 2007 By: Ahsan Khan
Problem:
Restore failed for Server 'WORKSTATION001\SQLSERVER2005'. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+ Server&ProdVer=9.00.3042.00& EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText &EvtID=Restore+Server&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at Microsoft.SqlServer.Management. SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()
===================================
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.3042.00&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo. ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries,
ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages) at Microsoft.SqlServer.Management.Smo. BackupRestoreBase.ExecuteSql(Server server, StringCollection queries) at Microsoft.SqlServer.Management.Smo.Restore. SqlRestore(Server srv)
===================================
Reason:
Two backup files are needed to restore a database if you have defined two destinations when backing up the database. Selecting one backup destination will not backup to that single destination. It creates a Media Family with the first item as "Media 1, Family 1" and second as "Media 1, Family 2". When restoring you would need both these files.
For example:
If backup destinations are:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\aff.bak
C:\Shared\site.bak
To restore the database you would both aff.bak and site.bak
Solution:
Change the Backup set Name.
Set Backup Type to Full.
Remove all the listed destinations.
Add only one destination.
This should resolve the issue.
Syntax for a multiple file restore:
RESTORE LOG [DATABASE NAME]
FROM
DISK = 'Path to file1',
DISK = 'Path to file2'
WITH
All your options.
View All Articles (Articles Archive)