Restore database error

SQL Server Administration
21

Dear Kristen,
this is the result

upload
upload.jpg1366×768 175 KB

thx

22

We would prefer that you post text here please, we can't cut & paste anything from images, and the full text would have included the extra columns that are missing in the image.

But in this case the BackupType is all I need so we are good. However, if you scrolled right you would see the Description of the Backup Type which would have told you what the problem is:

You are trying to restore from a file which is BackupType = 5

Your backup file is a DIFFERENTIAL backup. You need to also restore from the (earlier) FULL backup file (but it must be the FULL backup which is matched to that DIFFERENTIAL backup - .e. there must not have been any intervening Full Backup).

You can either restore from just a Full Backup file, or you can restore from the Full Backup file and then additionally from a subsequent Differential Backup.

If you need to restore to the point-in-time of the Differential Backup then you need both, of course. If the Date/Time of the Full Backup is "good enough" you can restore just from that.

The script I posted earlier includes optional Differential Backup restore, so you can use that to substitute the names of the relevant files to make your restore (of both Full and Differential files)

NOTE: if the Full Backup file you restore is NOT matched with the Differential Backup you will get an error when you attempt to restore the Differential Backup. You can then just repeat the process using different Full backup file, if necessary.

I recommend that you change your backup system so that it includes the type of the backup in the filename - e.g.

D:\backupdb\bis_backup_FULL_201711090007.bak
D:\backupdb\bis_backup_DIFF_201711100007.bak
D:\backupdb\bis_backup_TLOG_201711110007.bak
...

because that would prevent this type of issue.

23

so are there any solution from my backup file ?

thx

24

Yes, use a Backup File from a Full Backup.

You cannot restore a Differential backup - all that contains is JUST the changes from the previous Full backup.

25

This will tell you the filenames and types of backups which have been made for the database.

  • Run this on the server where the backup was made
  • Change the database name as appropriate
  • You will see that Filters are available for Date and Backup Type if you need them
SELECT TOP 1000
	BS.backup_start_date
	, BS.type
	, [Size]=BS.backup_size
	, BMF.physical_device_name
	, BS.name
	, BS.user_name
	, BS.description
FROM	msdb.dbo.backupset AS BS
	LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
		ON BMF.media_set_id = BS.media_set_id
WHERE	1=1
	AND BS.database_name = N'BIS'
	-- Backup Type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND BS.type IN ('D', 'I', 'L')
--	AND BS.backup_start_date >= '20171101 00:00'
-- 	AND BS.backup_start_date <  '20171129 00:00'
--
ORDER BY BS.backup_start_date DESC
	, BS.database_name
	, BMF.family_sequence_number
26
  1. A media failure may occur on the device where the backup is located.
  2. A write failure may occur during the creation of the backup.

And there could be several reasons behind the backup error. Take a look over here to know how to resolve SQL Server error 3013

27

This is not a corruption of the backup file, it is because the backup file is Differential backup rather than Full backup.