How to identify backup files?

Question

Post reply

How to identify backup files?

beejug1983

SSCertifiable

Points: 6491
More actions
August 2, 2010 at 7:15 am

#227365

I have four sets of backup files with extension .bak one is full bakcup, one is differential backup, one is filegroup backup & one is transaction log backup. How can I identify them which one is ful, which one is differential, which one is filegroup & which one is transactional log ?
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Name them appropriately?

Try restore headeronly. Should show you what's in the files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ankur_libra Hall of Fame Points: 3999 More actions · Answer 2

Hi,

Follow as Gila suggested.

You can run the command as follows:-

restore headeronly from disk='D:\Temp\Anna_backup_200909251644.bak'

Now on checking backup type column of the data, you can find the type of the backup.i.e.

If Backup type:

VALUE BACKUP TYPE

1 = Database

2 = Transaction log

4 = File

5 = Differential database

6 = Differential file

7 = Partial

8 = Differential partial

For More info, u can refer to BOL link below :-

http://msdn.microsoft.com/en-us/library/ms178536.aspx

🙂

With regards,

Ankur

GAF Mr or Mrs. 500 Points: 546 More actions · Answer 3

Isn't there's also msdb.dbo.backupset.type?

Values are:

D = Database.

I = Database Differential.

L = Log.

F = File or Filegroup.

You'll have to join backupset.media_set_id with backupmediafamily.media_set_id for backupmediafamily.physical_device_name = 'YourFullBackupDirectoryAndBackupName'

Right?

Ray Mond SSCertifiable Points: 7159 More actions · Answer 4

RESTORE HEADERONLY as per Gail's suggestion if you have access to a SQL Server instance, SQL BAK Reader if not.

Isn't there's also msdb.dbo.backupset.type?
Values are:
D = Database.
I = Database Differential.
L = Log.
F = File or Filegroup.
You'll have to join backupset.media_set_id with backupmediafamily.media_set_id for backupmediafamily.physical_device_name = 'YourFullBackupDirectoryAndBackupName'
Right?

Yes, if you were planning to retrieve the details from the backup history tables, but RESTORE HEADERONLY is much easier, and you don't need access to the instance where the backups were originally made.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 5

Easiest thing is doing what Gail mentioned.

I give Full Bkp's a .zip ext, Diff's .bak, Log's .trn, and don't use filegroup but am pretty certain any 3-char extension would work just fine

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Perry Whittle SSC Guru Points: 233917 More actions · Answer 6

I would use

Full backups .BAK

Diff backups .DIF

Log backups .TRN

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

alexus8888 SSC Enthusiast Points: 123 More actions · Answer 7

You'll have to join backupset.media_set_id with backupmediafamily.media_set_id for backupmediafamily.physical_device_name = 'YourFullBackupDirectoryAndBackupName'

Nike air pas cher

nike shox pas cher