2

I have no experience using Microsoft SQL Server at all. Someone gave me a .bak file of their database and said that I can extract their spatial data that way. They said that they would not export them as JSON, KML, or shapefiles for me.

So... how can I open the .bak file and export the tables/data that I need as shapefiles? I am currently downloading SQL Server Express. I have very limited knowledge of SQL.

closed as too broad by user30184, Vince, whyzar, Jochen Schwarze, ThingumaBob Oct 26 '18 at 7:49

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • a .bak file is a backup of a database. You'd better off asking the person to export the data that you need as a csv. You can't convert a database backup file to a shapefile. If you can't get a csv then you'll need to download sqllite, restore from backup (the .bak file) and then export the data yourself. I don't believe that you can export to shapefile from SQL SRVR. So you'll then need to make a connection to your restored database in either QGIS or ArcCatalog, or some other client and extract the data. Like I said, would be much easier if the person could provide a csv or even a txt file. – enolan Oct 25 '18 at 17:22
  • You have a database backup. First thing is to read stackoverflow.com/questions/1535914/…. – user30184 Oct 25 '18 at 17:24
  • @enolan we had originally asked if they could export as a csv but they just directed me to download SQL server. I'll try downloading sqllite and see if that works instead. thank you so much for the help! – michelle Oct 25 '18 at 17:34
  • Unfortunately, you haven't been placed in a good position here. A database backup is going to requrire database setup and restoration, then a proper export. The existing data could have just been exported as ASCII text with geometry in Well-Known Text with the same level of effort. This question is probably too broad for our "Focused question/Best answer" model. I'd recommend that you ask for assistance from an experienced DBA, or work the provider's refusal to provide useful data with management. – Vince Oct 25 '18 at 17:34
2

I don't have experience with SQL Server Spatial, but have enough experience with PostGIS to say that someone giving you data in this format is a big "screw you". If I had data in PostGIS and needed to share it with someone, exporting it to a portable spatial format (like Geopackage or shapefile) would be trivial. Creating a database backup would probably be harder, and frankly, if there were some reason I couldn't export to a spatial format (and I'm having a hard time understanding what that reason could possibly be), I would do them the favor of saying "I can't give you this data" rather than the not-helpfully-helping by delivering a database backup to someone who was unfamiliar with PostGIS (as you are unfamiliar with SQL Server).

The steps to do this involve the following:

  1. Install SQL Server. This is off-topic, massive, and should be researched indpendently.
  2. Restore from backup. See for example https://stackoverflow.com/questions/1535914/import-bak-file-to-a-database-in-sql-server.
  3. Use ogr2ogr to export to your desired output format. See info on the OGR driver for SQL Server Spatial at https://www.gdal.org/drv_mssqlspatial.html.

My recommendation is that you ask again for an export to a spatial format, or, if the data happen to be point data, perhaps a CSV with point coordinates.

If the person is not willing to accommodate this request, you should probably assume the data are unattainable and pursue alternative sources.

If you are not willing to abandon this data, you should research 1 and 2 using official documentation, StackOverflow, and DBA.SE. Once you have SQL Server up and running, an appropriate follow-up question for this site would be "How can I export a spatial layer from SQL Server".

  • I was able to successfully install SQL Server, create a new database, and restore the backup. From there, I connected to the database in ArcCatalog and can export the tables/spatial files as I wish! Thanks for the helpful links! – michelle Oct 25 '18 at 19:14
  • Wow! Considering that you said you had "limited knowledge" with SQL Server, I'm kind of stunned that you were able to make progress that quickly. Good work! (I still think this was an obnoxious thing for the person who gave you the data to do.) – Lee Hachadoorian Oct 25 '18 at 19:33
  • Ha, I am too to be honest! Yeah, it's definitely not the easiest format to work with... still got it to work though :D – michelle Oct 25 '18 at 20:32

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