Category Archives: SQL Admin

ogr2ogr to load a database from a shapefile

gdal, SQL Admin

GDAL, the  Geospatial Data Abstraction Library

Is a great set of tools for managing data with a spatial data type.

I use the ogr2ogr tool to load shapefiles or other data into databases. It will automatically create the spatial metadata tables that are part of the OGC standard.

Spatial Metadata Tables

Two tables are created for letting the user (and applications) know where the spatial column is in what tables they are located, and what the projection is. This is all in another post.

ogr2ogr

You can load the database using ogr2ogr. This will handily create the spatial metadata tables for you during load.

For example, on MSSQL server, loading from a shape file in WGS84 (EPSG: 4326) to WA State Plane (EPSG 2927):

ogr2ogr -overwrite -progress -f MSSQLSpatial “MSSQL:server=SERVER1;database=GPSTrails;trusted_connection=yes” Points.shp -s_srs EPSG:4326 -t_srs EPSG:2927

Here I am overwriting the table if it exists, showing progress information, indicating that I have a trusted connection converting from 4326 to 2927.

If you look over the data types you can use  for ogr2ogr, you can see that you could load from sqlite, ESRI FileGeodatabase, csv, and doxens of other formats.

MSSQL Database Restore History simple query

SQL Admin

After reading several posts on the internet for discovering database restoration history, I realized that most were way over complicating what you can do in SQL 20008R2 and up.

This is the easiest method for me:

use msdb
select * from restorehistory

And that is pretty much it.

However, I do have one server that is making me scratch my head. The restorehistory table is empty, but the logs clearly show that I had several restores in the last 3 months. Hmmm.

UPDATE:

OK I feel rather stupid about this. Of course the restore history is empty, I am cleaning it up with part of my maintenance plan!