Can I restore a SQL Server .bak files without SQL Server?

Question

I have some large .bak files from a SQL Server 2005 dump.

Can I restore these without using SQL Server, either to PostgreSQL, MySQL, or to flat text files?

An open source solution would be most useful.

Answer 1

Here is what I suggest:

1. build a virtual machine running Windows, with enough disk space to hold the backup. Copy the backup file there. If you don't already have the ability to build virtual machines, you could do so with free products like Oracle VirtualBox.
2. download and install the evaluation edition of SQL Server. Make sure you include both the database engine and Management Tools - Complete.

3. if the VM has enough space to hold the backup but not enough space to also restore it, you can perform a "virtual restore" using the trial version of a product from Red-Gate with the same name (which allows you to interact with the backup file as if it had been restored). Otherwise, restore the database the normal way.

4. Once the database is available (either through a normal restore or virtual restore), you can generate scripts for the schema and data in the following way:

   • Open Management Studio and connect to your instance.
   • Open Object Explorer.
   • Right-click your newly restored database, choose Tasks > Generate Scripts...
   • Click Next, Click Next
   • On the "Choose Script Options" page, scroll down and set "Script Data" to True
   • Click Next
   • Check all of the relevant objects and click Next
   • Check the tables you want and click Next
   • Choose to script to a file. Now you'll have a file that contains all your objects and data using SQL Server insert syntax, you will have to play with the output to get it in a format that works for Postgres (I am not privy to any minor syntax differences).

Alternatively you can try and play with the bcp utility to extract data to CSV files or similar, but you'll have to do this table-by-table or use some clever scripting (PowerShell, T-SQL, C#/SMO, etc.) to generate all of the bcp commands for you. Once in CSV files, it should be trivial to bulk load the data into Postgres (but you will still have some work to generate the tables).

As a final suggestion, if the .bak file is not ginormous, and the data is not confidential, I am more than willing to try and generate files for you in the format you need. I have plenty of Windows VMs with space, the challenge would be getting the .BAK file to a place where I can retrieve it - especially if it's larger than most of the file-sharing services support.

Answer 2

Unfortunately there isn't a way to gain access to the contents of a .bak file without having deep knowledge of the internals of the file itself. I can think of one person on here that may be privy to this information, but i cant speak to whether or not said person would tell you how to go about doing it

So, you're going to need to install a SQL Server instance. You're also going to need to ensure that this instance can talk to your Postgres server (frigging with the pg_hba.conf) Once there, you have a couple of good paths to migrate the data.

The first path would be to install the Postgres Windows ODBC driver and set up a connection to the pg server. Then you can use SSIS to script a data migration. If you're going to go this route, I suggest you install SSIS when installing the database server.

The other option also involves the ODBC driver connection, but you can create a linked server in SQL Server and run inserts on the pg instance through SQL Server. I have answered this exact question on here before so it shouldn't be hard to find.

EDIT

To incorporate Aaron's comment, once you get SQL Server up and running, you could also export the data to flat files in a variety of different ways. If you choose this path let me know and I'll post a few ways to do that

EDIT (2):

The linked server process may not be the best approach unless you want to create the structures in advance. It's my preferred method, but I usually already have the structure in place on both sides.

That leaves Aaron Bertrand's answer as the best answer. Please note that in addition to data types (IDENTITY vs. SEQUENCE, postgres knows nothing of NVARCHAR since you set the encoding on the database itself). Postgres knows nothing of CREATE CLUSTERED INDEX (CLUSTER may work for you). Finally, since I see in the comments that you're going to be using spatial data, postgresql knows nothing of CREATE SPATIAL INDEX syntax. You'll need to install postgis and use the INDEXTYPE keyword in order to create spatial indexes. Finally, make sure you handle schemas appropriately.

Long story short:

1. Generate scripts and data using Aaron Bertrand's method (I'd probably stick with the table level)
2. Take note of the index DDL (if it's still valid), but don't include it
3. Create indexes on postgres once structure and data is in place