Backup Start Date Time and Finish Date Time

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