SQL Server Backup Format (BAK)

database backup - SeniorDBA

When using the standard backup utilities in SQL Server, the backup files will have the default BAK extension. Standard database backup files with the .bak extension contain data in the standard Microsoft Tape Format (MTF), which is used by  Microsoft itself as well as many backup tools created for the Windows platform. Many third-party backup products produce backups in this format as well.

Microsoft Tape Format is used while writing and reading data to and from removable storage devices during storage management or data protection operations such as data transfers, copies, backup and restore. In the case of SQL Server, MTF files contain data and log information (MDF and LDF files) necessary for restoring the database. The MDF file is the Main Data File or primary database data file, that binds all other files in that database together. The LDF is the Log Data File and it contains all log information and is crucial for a database restore process.

There is no official standard that enforces a specific file extension for SQL Server database backup files. Users normally use the .bak extension to mark a backup file that contains full database backup. Here are some other extensions that many users use to name their backup files:

*.bak – Full Database Backup

*.trn – Transaction Log Backup

*.dif – Differential Backup

*.fil – Filegroup Backup

You should also remember that changing the extension does not affect the content of the file. You can give your backup file any name you want and add a different extension based on you own requirements.

You can create backup files with this standard T-SQL command:

BACKUP DATABASE my_database TO DISK = 'D:\myfullbackup.bak'
GO

and restore the backup using another simple T-SQL command:

RESTORE DATABASE my_database FROM DISK = 'D:\myfullbackup.bak'
GO

To view the contents of a SQL Server BAK file, you can run this command:

RESTORE HEADERONLY FROM DISK = 'D:\myfullbackup.bak' GO

This will return some detailed information about the data contained in the backup file:

ColumnName Value
BackupName NULL
BackupDescription NULL
BackupType 1
ExpirationDate NULL
Compressed 0
Position 1
DeviceType 2
UserName Server1\TestDBA
ServerName Server1
DatabaseName my_database
DatabaseVersion 611
DatabaseCreationDate 10/22/08 13:48
BackupSize 177324544
FirstLSN 414000000754800000
LastLSN 414000000758300000
CheckpointLSN 414000000754800000
DatabaseBackupLSN 0
BackupStartDate 3/19/16 12:02
BackupFinishDate 3/19/16 12:02
SortOrder 0
CodePage 0
UnicodeLocaleId 1033
UnicodeComparisonStyle 196608
CompatibilityLevel 90
SoftwareVendorId 4608
SoftwareVersionMajor 9
SoftwareVersionMinor 0
SoftwareVersionBuild 3077
MachineName Server1
Flags 512
BindingID 459DDE25-B461-4CFD-B72E-0D4388F50331
RecoveryForkID E1BF182D-E21A-485A-9E2F-09E9C7DEC9D4
Collation Latin1_General_CS_AS
FamilyGUID E1BF182D-E21A-485A-9E2F-09E9C7DEC9D4
HasBulkLoggedData 0
IsSnapshot 0
IsReadOnly 0
IsSingleUser 0
HasBackupChecksums 0
IsDamaged 0
BeginsLogChain 0
HasIncompleteMetaData 0
IsForceOffline 0
IsCopyOnly 0
FirstRecoveryForkID E1BF182D-E21A-485A-9E2F-09E9C7DEC9D4
ForkPointLSN NULL
RecoveryModel FULL
DifferentialBaseLSN NULL
DifferentialBaseGUID NULL
BackupTypeDescription Database
BackupSetGUID 0C6D57F2-2EDB-4DEB-9C10-53C68578B046

This detailed information can help you verify you have the correct backup file before you begin the restore process.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s