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.
|show 1 more comment|
Here is what I suggest:
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 (
Long story short: