none
I can not restore my backed up database RRS feed

  • Question

  • Hi guys,

    I want to backup Database and restore it again, but after backup and try to restore it error message appear:

    SqlError The media set has 2 media families but only 1 are provided

    I can not to restore my database again,

    • Moved by Eileen Zhao Tuesday, October 16, 2012 9:00 AM (From:SQL Server Integration Services)
    Wednesday, October 10, 2012 6:13 AM

Answers

  • Hi MrMouslim,

    You can use SQL Server Management Tools or backup command to backup to multiple files, please refer to:
    http://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/ 

    Thanks,
    Eileen


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Tuesday, October 16, 2012 8:57 AM
  • Hello,

    I guess your original have more than one three file *.mdf, *.ldf and *.ndf. I that right you can use command :

    Try to use command

    create PROC sp_RestoreData
                    @pFldrBckp        nvarchar(100)= 'F:\',
                    @pNew_DtbsName       nvarchar(100)= 'NewDatabase',
                    @pFileFull           nvarchar(1000) = 'D:\Backup\',
                    @pFileDiff           nvarchar(1000) = 'D:\Backup\'               
    WITH ENCRYPTION
    AS
    SET NOCOUNT ON 
    
    
    -----------------------------------------------------------------------------------------------------------------
    BEGIN TRY
    	--Password restore database: '' is no password
    	DECLARE @lstrPass nvarchar(100)
    	set @lstrPass = 'XXX'
    	-----------------------------------------------------------------------------------------------------------------
    
    
    	--Dynamic SQL for restore Database
    	DECLARE @SQL nvarchar(4000)
    	set @SQL = ''
    	-----------------------------------------------------------------------------------------------------------------
    
    
    	--New database name
    	DECLARE @lstrNew_DtbsName nvarchar(200)
    	SET @lstrNew_DtbsName = @pNew_DtbsName + '_' + CONVERT(varchar(10),getdate(),112)
    	-----------------------------------------------------------------------------------------------------------------
    
    	--Command for read database file structure
    	declare @Command nvarchar(4000)
    	set @Command = ''
    	-----------------------------------------------------------------------------------------------------------------
    
    	--Check if restore file have password
    	if @lstrPass  = ''
    	begin
    		set @Command = N'RESTORE FILELISTONLY FROM DISK = N''' + @pFileFull + ''''
    	end else
    	begin
    		set @Command = N'RESTORE FILELISTONLY FROM DISK = N''' + @pFileFull + ''' WITH  PASSWORD = ''' + @lstrPass + ''''
    	end
    	-----------------------------------------------------------------------------------------------------------------
    
    	--Make template table hole list of database file need to restore
    	create table #FileList
    	(
    		  LogicalName  nvarchar(128), 
    		  PhysicalName  nvarchar(260),
    		  Type char(1), 
    		  FileGroupName nvarchar(128), 
    		  Size  numeric(20,0),
    		  MaxSize numeric(20,0),
    		  FileID  bigint ,  
    		  CreateLSN  numeric(25,0),
    		  DropLSN  numeric(25,0) NULL,
    		  UniqueID  uniqueidentifier ,
    		  ReadOnlyLSN numeric(25,0) NULL,
    		  ReadWriteLSN  numeric(25,0) NULL,   
    		  BackupSizeInBytes  bigint,
    		  SourceBlockSize int,
    		  FileGroupID int,
    		  LogGroupGUID  uniqueidentifier NULL, 
    		  DifferentialBaseLSN  numeric(25,0) NULL, 
    		  DifferentialBaseGUID  uniqueidentifier ,
    		  IsReadOnly  bit ,
    		  IsPresent  bit ,
    		  TDEThumbprint varbinary(32) 
    	)
    	insert into dbo.#FileList 
    	exec sp_executesql @Command
    
    	-- Make column hole new name of file
    	alter table #FileList add	Name nvarchar(200)
    	alter table #FileList add FilePath nvarchar(100)
    	alter table #FileList add indx int IDENTITY(1,1)
    
    	--Update new name
    	update #FileList set FilePath = REVERSE(SUBSTRING(REVERSE(PhysicalName), 0, PATINDEX('%\%', REVERSE(PhysicalName))))
    	update #FileList set Name = case Upper(REVERSE(SUBSTRING(REVERSE(FilePath), 1,4))) 
    		when '.MDF' then @lstrNew_DtbsName + '_data'
    		when '.LDF' then @lstrNew_DtbsName+ '_log'
    		when '.NDF' then @lstrNew_DtbsName + '_' + CONVERT(nvarchar,indx,0)  + '_ndf'
    		else @lstrNew_DtbsName end  + REVERSE(SUBSTRING(REVERSE(FilePath), 1,4)) from #FileList
    
    	--Check folder name
    	if SUBSTRING(@pFldrBckp, len(@pFldrBckp), 1) = '\'
    	begin
    		set @pFldrBckp = SUBSTRING(@pFldrBckp, 0, len(@pFldrBckp))
    	end
    
    	-----------------------------------------------------------------------------------------------------------------
    	--Check existed database name, if existed then delete it
    	EXEC ('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N''' + @lstrNew_DtbsName + ''') DROP DATABASE [' + @lstrNew_DtbsName + ']')
    	-----------------------------------------------------------------------------------------------------------------
    
    	--Restore with diff file
    	--1 : Restore full first
    	--2 : Restore diff after
    	IF @pFileDiff <> ''
    	BEGIN
    					--1: Restore Full first
    					--Default command for restore database				
    					set @SQL = ''
    					set @SQL = 'RESTORE DATABASE ' +  @lstrNew_DtbsName + ' FROM DISK = N''' + @pFileFull + ''''
    					
    					--Check if have password
    					set @SQL = @SQL + ' WITH NORECOVERY, ' 
    					if @lstrPass <> ''
    					begin					
    						set @SQL = @SQL + 'PASSWORD = ''' + @lstrPass + ''',' 					
    					end
    					
    					--Command for exstract D:\DB\2008R2\TestRestore.mdf => TestRestore.mdf
    					select @SQL = @SQL + ' MOVE N' +''''+ LogicalName + ''' TO ''' + @pFldrBckp  + '\'+ Name + ''',' from dbo.#FileList  
    					print @SQL
    					if @SQL <> ''
    					begin
    						set @SQL = LTRIM(rtrim(@SQL))
    						if SUBSTRING(@SQL, LEN(@SQL),1) = ','
    						begin
    							set @SQL = SUBSTRING(@SQL,0, LEN(@SQL))
    						end										
    						if @SQL <> ''
    						begin
    							exec sp_executesql @SQL							
    							set @SQL = ''
    												
    							--Restore diff
    							set @SQL = 'RESTORE DATABASE ' +  @lstrNew_DtbsName + ' FROM DISK = N''' + @pFileDiff + ''''
    							--Set @SQL = @SQL + ' WITH'
    							if @lstrPass <> ''
    							begin
    								set @SQL = @SQL + ' WITH PASSWORD = ''' + @lstrPass + '''' 					
    							end						
    							exec sp_executesql @SQL							  										 
    						end	
    					end
    									
    					
    	END
    	ELSE
    	BEGIN
    					--- RESTORE ONLY FULL          
    					set @SQL = ''
    					set @SQL = 'RESTORE DATABASE ' +  @lstrNew_DtbsName + ' FROM DISK = N''' + @pFileFull + ''''
    					
    					--Check if have password
    					--set @SQL = @SQL + ' WITH ' 
    					if @lstrPass <> ''
    					begin
    						set @SQL = @SQL + ' WITH PASSWORD = ''' + @lstrPass + ''',' 					
    					end
    					
    					--Command for exstract D:\DB\2008R2\TestRestore.mdf => TestRestore.mdf
    					select @SQL = @SQL + ' MOVE N' +''''+ LogicalName + ''' TO ''' + @pFldrBckp + '\' +Name+ ''',' from dbo.#FileList  
    					if @SQL <> ''
    					begin
    						set @SQL = LTRIM(rtrim(@SQL))
    						if SUBSTRING(@SQL, LEN(@SQL),1) = ','
    						begin
    							set @SQL = SUBSTRING(@SQL,0, LEN(@SQL))
    						end					
    						exec sp_executesql @SQL 
    					end  
    	END
    END TRY
    BEGIN CATCH
      RAISERROR ('Error',1, 0)
    END CATCH
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO



    Cuong

    Tuesday, October 16, 2012 10:08 AM

All replies

  • Hi,

    Looks like back up has been taken into two files. try to back up again.

    Similar issue here


    Rajkumar

    Wednesday, October 10, 2012 6:27 AM
  • And you want to do this with SSIS?
     

    If not try one of the more appropriate SQL Server forums:

    http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/threads

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/threads


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Wednesday, October 10, 2012 6:28 AM
  • How to get the second file for Backup ???
    Wednesday, October 10, 2012 7:20 AM
  • Hi MrMouslim,

    You can use SQL Server Management Tools or backup command to backup to multiple files, please refer to:
    http://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/ 

    Thanks,
    Eileen


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Tuesday, October 16, 2012 8:57 AM
  • Hello,

    I guess your original have more than one three file *.mdf, *.ldf and *.ndf. I that right you can use command :

    Try to use command

    create PROC sp_RestoreData
                    @pFldrBckp        nvarchar(100)= 'F:\',
                    @pNew_DtbsName       nvarchar(100)= 'NewDatabase',
                    @pFileFull           nvarchar(1000) = 'D:\Backup\',
                    @pFileDiff           nvarchar(1000) = 'D:\Backup\'               
    WITH ENCRYPTION
    AS
    SET NOCOUNT ON 
    
    
    -----------------------------------------------------------------------------------------------------------------
    BEGIN TRY
    	--Password restore database: '' is no password
    	DECLARE @lstrPass nvarchar(100)
    	set @lstrPass = 'XXX'
    	-----------------------------------------------------------------------------------------------------------------
    
    
    	--Dynamic SQL for restore Database
    	DECLARE @SQL nvarchar(4000)
    	set @SQL = ''
    	-----------------------------------------------------------------------------------------------------------------
    
    
    	--New database name
    	DECLARE @lstrNew_DtbsName nvarchar(200)
    	SET @lstrNew_DtbsName = @pNew_DtbsName + '_' + CONVERT(varchar(10),getdate(),112)
    	-----------------------------------------------------------------------------------------------------------------
    
    	--Command for read database file structure
    	declare @Command nvarchar(4000)
    	set @Command = ''
    	-----------------------------------------------------------------------------------------------------------------
    
    	--Check if restore file have password
    	if @lstrPass  = ''
    	begin
    		set @Command = N'RESTORE FILELISTONLY FROM DISK = N''' + @pFileFull + ''''
    	end else
    	begin
    		set @Command = N'RESTORE FILELISTONLY FROM DISK = N''' + @pFileFull + ''' WITH  PASSWORD = ''' + @lstrPass + ''''
    	end
    	-----------------------------------------------------------------------------------------------------------------
    
    	--Make template table hole list of database file need to restore
    	create table #FileList
    	(
    		  LogicalName  nvarchar(128), 
    		  PhysicalName  nvarchar(260),
    		  Type char(1), 
    		  FileGroupName nvarchar(128), 
    		  Size  numeric(20,0),
    		  MaxSize numeric(20,0),
    		  FileID  bigint ,  
    		  CreateLSN  numeric(25,0),
    		  DropLSN  numeric(25,0) NULL,
    		  UniqueID  uniqueidentifier ,
    		  ReadOnlyLSN numeric(25,0) NULL,
    		  ReadWriteLSN  numeric(25,0) NULL,   
    		  BackupSizeInBytes  bigint,
    		  SourceBlockSize int,
    		  FileGroupID int,
    		  LogGroupGUID  uniqueidentifier NULL, 
    		  DifferentialBaseLSN  numeric(25,0) NULL, 
    		  DifferentialBaseGUID  uniqueidentifier ,
    		  IsReadOnly  bit ,
    		  IsPresent  bit ,
    		  TDEThumbprint varbinary(32) 
    	)
    	insert into dbo.#FileList 
    	exec sp_executesql @Command
    
    	-- Make column hole new name of file
    	alter table #FileList add	Name nvarchar(200)
    	alter table #FileList add FilePath nvarchar(100)
    	alter table #FileList add indx int IDENTITY(1,1)
    
    	--Update new name
    	update #FileList set FilePath = REVERSE(SUBSTRING(REVERSE(PhysicalName), 0, PATINDEX('%\%', REVERSE(PhysicalName))))
    	update #FileList set Name = case Upper(REVERSE(SUBSTRING(REVERSE(FilePath), 1,4))) 
    		when '.MDF' then @lstrNew_DtbsName + '_data'
    		when '.LDF' then @lstrNew_DtbsName+ '_log'
    		when '.NDF' then @lstrNew_DtbsName + '_' + CONVERT(nvarchar,indx,0)  + '_ndf'
    		else @lstrNew_DtbsName end  + REVERSE(SUBSTRING(REVERSE(FilePath), 1,4)) from #FileList
    
    	--Check folder name
    	if SUBSTRING(@pFldrBckp, len(@pFldrBckp), 1) = '\'
    	begin
    		set @pFldrBckp = SUBSTRING(@pFldrBckp, 0, len(@pFldrBckp))
    	end
    
    	-----------------------------------------------------------------------------------------------------------------
    	--Check existed database name, if existed then delete it
    	EXEC ('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N''' + @lstrNew_DtbsName + ''') DROP DATABASE [' + @lstrNew_DtbsName + ']')
    	-----------------------------------------------------------------------------------------------------------------
    
    	--Restore with diff file
    	--1 : Restore full first
    	--2 : Restore diff after
    	IF @pFileDiff <> ''
    	BEGIN
    					--1: Restore Full first
    					--Default command for restore database				
    					set @SQL = ''
    					set @SQL = 'RESTORE DATABASE ' +  @lstrNew_DtbsName + ' FROM DISK = N''' + @pFileFull + ''''
    					
    					--Check if have password
    					set @SQL = @SQL + ' WITH NORECOVERY, ' 
    					if @lstrPass <> ''
    					begin					
    						set @SQL = @SQL + 'PASSWORD = ''' + @lstrPass + ''',' 					
    					end
    					
    					--Command for exstract D:\DB\2008R2\TestRestore.mdf => TestRestore.mdf
    					select @SQL = @SQL + ' MOVE N' +''''+ LogicalName + ''' TO ''' + @pFldrBckp  + '\'+ Name + ''',' from dbo.#FileList  
    					print @SQL
    					if @SQL <> ''
    					begin
    						set @SQL = LTRIM(rtrim(@SQL))
    						if SUBSTRING(@SQL, LEN(@SQL),1) = ','
    						begin
    							set @SQL = SUBSTRING(@SQL,0, LEN(@SQL))
    						end										
    						if @SQL <> ''
    						begin
    							exec sp_executesql @SQL							
    							set @SQL = ''
    												
    							--Restore diff
    							set @SQL = 'RESTORE DATABASE ' +  @lstrNew_DtbsName + ' FROM DISK = N''' + @pFileDiff + ''''
    							--Set @SQL = @SQL + ' WITH'
    							if @lstrPass <> ''
    							begin
    								set @SQL = @SQL + ' WITH PASSWORD = ''' + @lstrPass + '''' 					
    							end						
    							exec sp_executesql @SQL							  										 
    						end	
    					end
    									
    					
    	END
    	ELSE
    	BEGIN
    					--- RESTORE ONLY FULL          
    					set @SQL = ''
    					set @SQL = 'RESTORE DATABASE ' +  @lstrNew_DtbsName + ' FROM DISK = N''' + @pFileFull + ''''
    					
    					--Check if have password
    					--set @SQL = @SQL + ' WITH ' 
    					if @lstrPass <> ''
    					begin
    						set @SQL = @SQL + ' WITH PASSWORD = ''' + @lstrPass + ''',' 					
    					end
    					
    					--Command for exstract D:\DB\2008R2\TestRestore.mdf => TestRestore.mdf
    					select @SQL = @SQL + ' MOVE N' +''''+ LogicalName + ''' TO ''' + @pFldrBckp + '\' +Name+ ''',' from dbo.#FileList  
    					if @SQL <> ''
    					begin
    						set @SQL = LTRIM(rtrim(@SQL))
    						if SUBSTRING(@SQL, LEN(@SQL),1) = ','
    						begin
    							set @SQL = SUBSTRING(@SQL,0, LEN(@SQL))
    						end					
    						exec sp_executesql @SQL 
    					end  
    	END
    END TRY
    BEGIN CATCH
      RAISERROR ('Error',1, 0)
    END CATCH
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO



    Cuong

    Tuesday, October 16, 2012 10:08 AM
  • hi

    MrMouslim

    try this code

    [CODE]

    RESTORE DATABASE Test
    FROM DISK='C:\Test.bak'

    [/CODE]

    or

    refer this url

    [LINK]

    http://www.techrepublic.com/blog/window-on-windows/how-do-i-restore-a-sql-server-database-to-a-new-server/454

    [/LINK]

    Wednesday, October 17, 2012 2:24 AM
  • Hello MrMouslim,

    The BACKUP that you are trying to RESTORE is incomplete, which means is cannot be used in RESTORATION.

    I would suggest you to have a fresh database backup and use that for Restoration.

    This time do not split the backup file in multiple part and take entire backup in one .BAK file.

    Backup Database [DatabaseName] to Disk = 'Path.bak'


    • Edited by RahulKapoor Wednesday, October 17, 2012 9:54 AM Edited
    Wednesday, October 17, 2012 9:51 AM