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.

  • 1
    "I would prefer not to have to do so just for this task." Why not? It's the way to do what you're trying to do. – swasheck Jun 27 '12 at 22:53
  • Does restoring to something else even make any sense? What do you want to do with the resulting data? – Philᵀᴹ Jun 27 '12 at 22:55
  • 1
    @Phil I would like to move the resulting data to a PostgreSQL database, or even flat text files. – Abe Jun 27 '12 at 23:27
  • 1
    @swasheck I removed the quoted line, as it seems to have detracted from the question. But to answer your question, because I have no idea how to use it, and my server runs Linux. An available windows laptop does not have enough room. – Abe Jun 27 '12 at 23:28
  • 2
    Well that is information that we didn't have before now :). Thanks for the extra information - I have a plan – swasheck Jun 28 '12 at 0:41

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.

  • +1 I was going to suggest the bcp method once I got to a computer. Some gotchas are table delimiters, IDENTITY (sequence, in pg), nvarchar, just to name a few. Otherwise, this is what I would have suggested ( sans Red-Gate ) in my edit section. – swasheck Jun 28 '12 at 2:44
  • There are 8, 20GB .bak files, and it would be great if you could help, but please see related questions on gis.se, gis.stackexchange.com/q/28281/3218 and gis.stackexchange.com/q/28257/3218 about the USDA Soils Database (SSURGO). It is currently very difficult to use access these data in an automated way for simulation modeling. It would be a great benefit to science to have these data in a more useable structure. You could download it from my server. The data is not confidential, any can get it for $50/CD or $100/DVD or perhaps less from their extension agent. – Abe Jun 28 '12 at 4:37
  • It looks like you have answers on how to obtain the data on GIS.SE. I've gone and looked at those sites but do not see where they specify that the file is a SQL Server backup. How/from where did you obtain these files? – swasheck Jun 28 '12 at 17:31

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

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
  • 3
    I don't think you need to configure SQL Server to talk to Postgres. I'm sure once you get SQL Server installed you can extract the data into a variety of formats Postgres will understand. – Aaron Bertrand Jun 28 '12 at 0:55
  • Apologies. I meant that you'd need to configure Postgres to accept external connections (SQL Server in this case) – swasheck Jun 28 '12 at 1:32
  • Don't apologize. :-) I was just clarifying that you don't need to have SQL Server talk directly to Postgres or vice versa.. – Aaron Bertrand Jun 28 '12 at 1:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.