-- View the data and log files in the backup files
RESTORE FILELISTONLY
FROM DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_1.bak',
DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_2.bak',
DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_3.bak',
DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_4.bak'
-- Creating the AdventureWorks2012_NEW database through restore
RESTORE DATABASE AdventureWorks2012_NEW
FROM DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_1.bak',
DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_2.bak',
DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_3.bak',
DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_4.bak'
WITH
MOVE 'AdventureWorks2012_Data' TO 'E:\SQLData\AdventureWorks2012_NEW_Data.mdf',
MOVE 'AdventureWork_Data2' TO 'E:\SQLData\AdventureWork_NEW_Data2.ndf',
MOVE 'AdventureWorks2012_Log' TO 'L:\SQLLog\AdventureWorks2012_NEW_log.ldf',
STATS = 10, RECOVERY

Note:

  • All backup files must be included in order to backup the database.
  • Order of backup files does not matter.
  • Renaming backup files does not matter.
  • Renaming backup file extension does not matter.
  • Backup file size will be evenly divided among all the backup files.
  • You can use WinZip to compress the backup files.

Errors:

Missing one backup file…

-- Creating the AdventureWorks2012_NEW database through restore
RESTORE DATABASE AdventureWorks2012_NEW
FROM DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_1.bak',
DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_2.bak',
DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_3.bak'
--DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_4.bak'
WITH
MOVE 'AdventureWorks2012_Data' TO 'E:\SQLData\AdventureWorks2012_NEW_Data.mdf',
MOVE 'AdventureWork_Data2' TO 'E:\SQLData\AdventureWork_NEW_Data2.ndf',
MOVE 'AdventureWorks2012_Log' TO 'L:\SQLLog\AdventureWorks2012_NEW_log.ldf',
STATS = 10, RECOVERY
Msg 3132, Level 16, State 1, Line 9
The media set has 4 media families but only 3 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.

Wrong backup files include…

Msg 3258, Level 16, State 1, Line 9
The volume on the device "E:\SQLBackup\AdventureWorks2012_2017.bak" is not part of the media set that is currently being processed. Ensure that the backup devices are loaded with the correct media.
Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.

Sources:

https://www.datavail.com/blog/how-to-restore-your-backups-from-striped-backup-files/

Last modified: May 6, 2020

Author

Comments

Write a Reply or Comment