BAK File Pieces

• SQL_Enthusiast

  Hall of Fame

  Points: 3805

  More actions

  October 28, 2010 at 4:54 pm

  #240514

  Hello All,

  I have BAK file that was split into 4 pieces by the customer. Can I restore them individually or not? Here's the code and error I'm trying to use.

  RESTORE DATABASE [DB1] FROM

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_1.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_2.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_3.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_4.bak'

  WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY ,

  MOVE 'DB1_Data' TO 'D:\SQL_DATA\STG.mdf',

  MOVE 'DB1_AUDIT' TO 'D:\SQL_DATA\STG_1.mdf',

  MOVE 'DB1_Log' TO 'D:\SQL_DATA\STG.ldf',

  REPLACE

  Error Message:

  Msg 3183, Level 16, State 2, Line 2

  RESTORE detected an error on page (60157:1040581563) in database "DB1" as read from the backup set.

  Msg 3013, Level 16, State 1, Line 2

  RESTORE DATABASE is terminating abnormally.

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  October 28, 2010 at 5:16 pm

  #1243928

  If the backup was done to multiple files, you have to restore from all of them together.

  If the backup was do to a single file and split into multiple files later with some utility that is a different problem.

  It looks like you may just have a bad backup.

  You should try running RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY to see what you actually have.

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  October 28, 2010 at 5:23 pm

  #1243930

  First, validate the backup files by executing:

  RESTORE VERIFYONLY FROM

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_1.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_2.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_3.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_4.bak'

  BOL states "Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable"

  If that gives an error, then the backup files are corrupt and new backup files need to be transmitted.

  Were these files FTPed? If so, they must be FTPed as binary files not as ascii. The default is ASCII, which transmits seven bits per character and is not suitable for SQL Server backup files.

  Next, check on the backup file contents by executing:

  RESTORE FILELISTONLY FROM

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_1.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_2.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_3.bak',

  DISK = 'D:\SQL_BACKUP\db_db1_20101028_4.bak'

  SQL = Scarcely Qualifies as a Language