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 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 guess 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. It’s 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 which can create .bak files, here.
Since this file-name extension is used for backup files of any type and there is no standard format which 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 kind 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.
Once you selected 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 were meant to bring the data “online” in 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 file, is the easiest way to export SQL table to Excel. This is an easy-to-use utility which doesn’t require installation, all you need to do is to connect to you database, select a database and tables you want to export:
Then just press “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.
Going back into SSMS and right clicking on the database you have restored. Then you will go to Tasks and you will see a 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 database from where you want the data exported.
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 destination type.
The next step would be to browse to the location of 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 select 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 create 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.