How to open a .bak file without SQL Server

A SQL Server backup (.bak) is a container with the data pages of a database. Notepad shows noise, Excel cannot open it, and SQL Server Management Studio has no “open file” for it. What you can do depends on what you want to know: what the file is, which tables are in it, or the rows of one table. This article lists the ways, from lightest to heaviest.

1. Ask the file what it is, with T-SQL

RESTORE HEADERONLY and RESTORE FILELISTONLY read the header of a backup. They need a SQL Server instance to run on, but nothing is restored and nothing is written.

RESTORE HEADERONLY FROM DISK = N'D:\Backups\Sales_FULL.bak';
RESTORE FILELISTONLY FROM DISK = N'D:\Backups\Sales_FULL.bak';

The first shows the database, the server it came from, the SQL Server version, when it was made, whether it is full, differential or log, compressed, copy-only or encrypted. The second lists the database files with their sizes. What every column means.

This answers “what is this file and how much room will it need”. It does not show tables or rows.

2. Restore it on a free instance

To see tables and rows with the standard tools, restore the backup on a SQL Server that has room for it. SQL Server Express, LocalDB or a container will do for a look; they are free. Restore it under a name that cannot clash with anything:

RESTORE DATABASE Sales_Temp
FROM DISK = N'D:\Backups\Sales_FULL.bak'
WITH MOVE N'Sales' TO N'E:\Temp\Sales_Temp.mdf',
     MOVE N'Sales_log' TO N'E:\Temp\Sales_Temp_log.ldf',
     STATS = 10;

Things to check first:

• Room. The restored files are as large as the database was, not as large as the backup. FILELISTONLY shows the sizes (the Size column, in bytes). A compressed backup of a large database still needs the full space.
• Version. A backup cannot be restored on an older SQL Server than the one that made it. HEADERONLY shows the version it came from.
• Edition limits. Express has a limit on the size of a database. Check the limit of the version you use.
• Encryption. A backup made WITH ENCRYPTION, or of a TDE database, needs its certificate on the instance that restores it. Back up the TDE certificate explains how to keep it.

For one table, restoring everything is a lot of work. Restore a single table from a backup shows the T-SQL way and the alternative.

3. Read the file directly

SQLTreeo Restore is a Windows application that reads the backup file itself. It needs no SQL Server and no restore:

• It lists every backup set in the file, with server, SQL Server version, dates, size, and whether it is compressed, encrypted, differential or copy-only.
• It lists the database files and every user table with its row count and size, and shows the columns and the first 1,000 rows of a table you select.
• It opens compressed backups, striped backups, differential backups combined with their full backup, and encrypted backups when you give it the certificate and its private key file.
• It exports the tables you tick to CSV, or copies them into a database on a SQL Server. Without a license key that writes the first 1,000 rows of each table; a license key writes every row.

The file is only read: it is not changed and not locked. Getting started walks through opening a backup.

Which one to use

You want to know	Use
What this file is, and how big the restored database will be	HEADERONLY and FILELISTONLY
Everything, in a database you can query with any tool	Restore on an instance with room
Which tables are in it, how big, and their rows, without waiting for a restore	SQLTreeo Restore
One or a few tables out of a large backup	SQLTreeo Restore, or restore and copy

Download SQLTreeo Restore: looking at backups is free and has no time limit. A license key is only needed to export or copy more than the first 1,000 rows of a table.