SQL Server backup format: BAK vs SQB

SqlBak creates backups in the so-called .bak format, while RedGate’s product stores backups in .sqb format. What’s the difference?

Usually, database backup files with .bak extension contain data in the standard Microsoft Tape Format (MTF)  used by  Microsoft itself as well as many backup tools for the Microsoft Windows platform. Many third-party SQL Server backup products including SqlBak and SQLBackupAndFTP produce backups in this format as well.

Files with .sqb extension contain backups in RedGate’s proprietary format. You would need to use some special utilities if you want them to be converted into MTF. Let’s learn more about Microsoft Tape Format (MTF) and SQL Backup format from Red Gate.

Microsoft Tape Format (MTF)

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 what file extension to use for SQL Server database backup files. Usually, users 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:

• Full Database Backup – *.bak
• Transaction Log Backup – *.trn
• Differential Backup – *.dif
• Filegroup Backup – *.fil

Please remember that changing the extension does not affect the content of the file. Actually, you can give your backup file any name you want and add a different extension.

You can create such files with the help of standard T-SQL command:

BACKUP DATABASE your_database TO DISK = 'full.bak'

and restore them using another simple command:

RESTORE DATABASE your_database FROM DISK = 'full.bak'

If you use SqlBak to backup your SQL Server database, you can simply restore all backups from the “Backup history & restore” section at your backup job settings page:

It’s necessary to mention that SqlBak additionally zips the .bak to save your disk space and secure your data with encryption.

SQL Backup Format from Red Gate (SQB)

SQB is a proprietary backup format that belongs to Red Gate. It also contains data in MTF format, but they are additionally compressed (they say up to 95%) and encrypted (using 256-bit AES encryption).

If your backups are in RedGate’s custom backup format you can not restore them using standard T-SQL Commands directly. To restore such backups you have to have RedGate’s SQL Backup Tool installed on your machine. If you’re ready to make an extra effort you can convert those files to Microsoft Tape Format. To do it you need to use SQL Backup Converter, which is installed along with Redgate’s SQL Backup tool:

This makes this SQB format a bit inflexible in terms of the restoration process. What if you don’t have Redgate’s products installed on the computer where you need to restore the database?

Bottom Line

While Redgate uses their own SQB format to compress and encrypt MTF data, other third-party database backup utilities like SqlBak and SqlBackupAndFtp compress and encrypt backup files using common zip and 7zip formats. This makes the output files more convertible and allows you to restore your database even if you don’t have those applications installed.