As best practice it is recommended that you have to backup date time with the backup file name so anyone can get the idea of Backup creation.
Sometimes due some issue we took backup without specifying the datetime with the backup file name so during restore we are unsure that how much data backed up in the backup file, type of backup, Is it Copy only and more.
SQL Server stores the Backup Metadata into backup header. You can restore header only command to get the required information.
RESTORE headeronly FROM disk = ‘c:\jshah.bak’
| Column Name | Values | Description |
| BackupName | NULL | |
| BackupDescription | NULL | |
| BackupType | 1 | Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = Partial 8 = Differential partial |
| ExpirationDate | NULL | |
| Compressed | 0 | 0 = Un-Compressed Backup 1 = Compressed Backup |
| Position | 1 | |
| DeviceType | 2 | |
| UserName | JShah | |
| ServerName | SQLDBPool | |
| DatabaseName | jshah | |
| DatabaseVersion | 655 | |
| DatabaseCreationDate | 12/31/10 9:55 AM | |
| BackupSize | 1453056 | |
| FirstLSN | 28000000006000100 | |
| LastLSN | 28000000013000000 | |
| CheckpointLSN | 28000000006000100 | |
| DatabaseBackupLSN | 0 | |
| BackupStartDate | 12/31/10 10:06 AM | |
| BackupFinishDate | 12/31/10 10:06 AM | |
| SortOrder | 52 | |
| CodePage | 0 | |
| UnicodeLocaleId | 1033 | |
| UnicodeComparisonStyle | 196609 | |
| CompatibilityLevel | 100 | |
| SoftwareVendorId | 4608 | |
| SoftwareVersionMajor | 10 | |
| SoftwareVersionMinor | 0 | |
| SoftwareVersionBuild | 2757 | |
| MachineName | SQLDBPool | |
| Flags | 512 | 1 = Log backup contains bulk-logged operations. 2 = Snapshot backup. 4 = Database was read-only when backed up. 8 = Database was in single-user mode when backed up. 16 = Backup contains backup checksums. 32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors. 64 = Tail log backup. 128 = Tail log backup with incomplete metadata. 256 = Tail log backup with NORECOVERY. |
| BindingID | 85A5505D-ADB1-4B33-A181-549DC520A0F8 | |
| RecoveryForkID | 03DE5437-1E27-4885-9011-91CFED12338A | |
| Collation | SQL_Latin1_General_CP1_CI_AS | |
| FamilyGUID | 03DE5437-1E27-4885-9011-91CFED12338A | |
| HasBulkLoggedData | 0 | 1 = Yes 0 = No |
| IsSnapshot | 0 | 1 = Yes 0 = No |
| IsReadOnly | 0 | 1 = Yes 0 = No |
| IsSingleUser | 0 | 1 = Yes 0 = No |
| HasBackupChecksums | 0 | 1 = Yes 0 = No |
| IsDamaged | 0 | 1 = Yes 0 = No |
| BeginsLogChain | 0 | 1 = Yes 0 = No |
| HasIncompleteMetaData | 0 | 1 = Yes 0 = No |
| IsForceOffline | 0 | 1 = Yes 0 = No |
| IsCopyOnly | 0 | 1 = Yes 0 = No |
| FirstRecoveryForkID | 03DE5437-1E27-4885-9011-91CFED12338A | |
| ForkPointLSN | NULL | |
| RecoveryModel | FULL | |
| DifferentialBaseLSN | NULL | |
| DifferentialBaseGUID | NULL | |
| BackupTypeDescription | Database | |
| BackupSetGUID | 62EB4399-C119-42C2-91F1-BF0FF19CB896 | |
| CompressedBackupSize | 1453056 |
