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.
Here is what I suggest:
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.
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:
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.
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.
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
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 (
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: