Were you ever faced with the situation in which you had to extract and export data from a .bak file to Excel (.xls, .xlsx, or .csv format)?

Surely if you have arrived here you are facing this problem and looking for a solution. Well, you have come to the right place. In the article below we will show you how you can achieve this.

Exporting data from a .bak file to Excel is not possible directly, but there are some workarounds. We will try to show you how you can do this in the context of a SQL Server database, but first, we will give you some general details about backup files.

If you already know what a backup file is and what is it used for, you can skip to the third section where we will start the restore process of the backup file in order to extract data from it.

What is a .bak file?

Starting off, it would be best to understand what a .bak file is and what its purpose is.

You might have guessed by this point, but if you did not, a .bak file is the extension name for a backup file. A backup file will contain details and/or information regarding a software system. Its contents are most likely the information stored in a database.

As said, nowadays, the most common .bak files are those of databases, such as SQL Server. There are still other programs or applications that might create these types of files, with the .bak extension (like SqlBak). You can find a more detailed list of software products that can create .bak files, here.

Since this file-name extension is used for backup files of any type and there is no standard format that creates a .bak file, each program can create its own format of a backup, with the same .bak extension.

This, in turn, will mean that unlike other kinds of file types, for example, .zip or .rar, which have a standard format for creating them and also a standard way of being un-archived, there is no standard equivalent for extracting information from a file with a .bak extension.

How to open a .bak file?

Since we said that a .bak file can be created by different software systems and if you do not have any information about the origin of the file or the program that it was created from, then you need to pass through some additional steps in order to get the information from the backup file.

Not knowing details about the file’s history, like the program it originates from, then what you can do is just try and see if there is a restore option for your file.

But, since this blog is dedicated to SQL Server and SQL Server backups, there are higher chances that you want to open and/or restore a backup (.bak) file. From this point, we will only refer to a database backup when talking about a .bak file.

If you want to see the data inside a database backup file then you will have to take it through a process called restore.

Can you restore a .bak file to Excel?

You cannot restore the data from a .bak file to Excel directly, but you will have to restore it on your database server first.

During this process of restoring the database backup file, the table structures will be created and data will be inserted in them, depending on what type of backup file it is. In order to restore a .bak file using SQL Server Management Studio (SSMS), you have to follow these 6 steps (or quickly skip over them and do a quick restore by clicking here).

Choose on what database you want to restore the data to, right-click it then go to Tasks -> Restore -> Database, as can be seen below.

Next you should check the radio button “From device” and then proceed to click on the button in order to browse for the location of the .bak file.

You will get to a screen like the one below where you will have to click on Add in order to choose the .bak file.

The next screen that appears will be similar to the one below, where you will have to go to the folder that contains your .bak file and select it for restoring.

Once you have selected the file, click OK and then OK again until you get back to this screen where you will have to select the backups you want to restore.

You will have to tick both the FULL backup type and the Transaction log in order for the restore process to succeed.

Once you select them you press OK and you should pretty soon (depending on your backup’s size) get a message similar to this.

And this is all you have to do. Now your database should be online and you will be able to access the data inside it.

Exporting the data to Excel

After reaching this point, where we have restored our database backup, the next steps actually involve exporting the data to Excel. All the steps that we have done up to this point have been meant to bring the data “online” into the database. There are two ways how to export data to Excel:

     Using  Sql to Excel Utility

Using Sql to Excel utility which creates CSV files, is the easiest way to export SQL tables to Excel. This is an easy-to-use utility that doesn’t require installation, all you need to do is to connect to your database, select a database and tables you want to export:

Then just press the “Generate CSV” button and Sql to Excel utility will create a separate CSV file for each table in the selected folder.

Also, you can find more information on the official page.

     Using SSMS

Go back into SSMS and right-clicking on the database you have restored. Then you will go to Tasks and you will see an option called Export Data, which you will have to click.

After clicking Export Data, a new window will appear where you will have to select the Data Destination.

After selecting the Data Source you’ll press Next and get to a window where you will have to select the Data Destination.

You will have a drop-down menu, like the one you can see below where you will have to select Excel as a destination type.

The next step would be to browse to the location where the file will be created and input its name. Also, you should select the version of Excel you want the file to be created for.

Press Next and get to the next Wizard window where you have the option of either running a custom query to output custom data from your tables or just selecting all data from more tables.

Choose whatever version works best for you, but for now, let’s just say you want to export all data from a specific table and thus you’ll choose the first option.

The next window you will see will have you select the table or tables you want to select data from. Select the table you want to export data from and either press Next or Edit Mappings.

Press Next to get to the next step of the wizard. You will get to a window where you have the option of running the query “Right Now” and also creating an SSIS package.

After you’ve selected the options you’d like, press Next. You will get to the last window of this wizard where you will have to press Finish.

This will create the export file to the file path you specified with the file name you have selected.

If we go to review the file, you can see that the data is in that specific format.