Long story short, we had a hosting plan on www.hostforlife.eu (btw, terrible service even on more expensive packages, plus they lost ALL files on their servers and we had to restore our backups as they lost those too) and for db backups we were opening tickets so they would take them manually for us.

When we moved to another host (the plan with hostforlife had expired) and tried to restore the SQL Server 2014 backup we couldn't do it (our host actually).

The backup had no extension and we are not sure what type of backup it is (a couple of methods were tried but didn't work).

We contacted their support for this but because we had no active plan they refused to even tell us how the backup was taken.

We could pay for a 3 month hosting but I do not want to give any more money to this crappy company.

So, my question is, how can we identify the backup type to restore it?


2 Answers 2

Restore Headeronly From Disk = '...'

There's only one SQL Server backup format. If this doesn't return anything useful then it's not a SQL Server backup file and may be something else (I can only imagine what; perhaps you'd have to open it in a text editor and see what the first few characters are).

It will return a BackupType column:

1 = Database 
2 = Transaction log 
4 = File 
5 = Differential database 
6 = Differential file
7 = Partial 
8 = Differential partial

That would help you determine the order in which you need to restore (usually database with norecovery, then differential database with norecovery, then transaction log, then with recovery).

There are other methods to identify the file using PowerShell and SMO but this is the quickest.


A simple way to identify the backup is by running restore headeronly command. Like RESTORE HEADERONLY FROM DISK = 'D:\Backup\backupfil.bak' ;

Look at the output and look at the column backup type. Below are types and there corresponding backups

Backup type:

1 = Database

2 = Transaction log

4 = File

5 = Differential database

6 = Differential file

7 = Partial

8 = Differential partial.

If you have access to SQL Server and have rights to access SQL Server catalogs you can run below query to get backup information for Last 7 days. This is the query source, I have modified the query a bit

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.name AS backupset_name, 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.