0

Is it possible to export a database' schema and data, similar to https://stackoverflow.com/a/1515975/14731, programmatically (without using the GUI)?

I'm trying to:

  • Export a database' schema and data from a remote server to a local file.
  • I want the schema for all tables, but the data for a subset of the tables. Meaning, I need to exclude data from some tables up-front. We are talking about terabytes of data which are too big to duplicate or send over the wire.
  • Import the data from the local file to a different remote server.
  • Ideally do this all programmatically, without using the GUI
Community
  • 1
  • 1
Gili
  • 86,244
  • 97
  • 390
  • 689
  • Scripts exists to dynamically generate the Insert statements. see http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table – Mark Aug 13 '15 at 14:39
  • 1
    i don't believe you can script an object via TSQL, but you can with powershell/SMO. http://stackoverflow.com/questions/12948678/scripting-out-individual-objects-from-sql-using-smo. For exporting/importing of data, you can use BCP.exe. – Greg Aug 13 '15 at 14:40
  • backup, restore, drop, delete would be a lot easier to implement – Julien Vavasseur Aug 13 '15 at 14:56
  • @Greg Sorry, I am not very familiar with Powershell or BCP so I am strugging to make this work. Can you please post an answer with specific commands to be run from cmd.exe which will export the schema and data of a remote database? Also, keep in mind I want to exclude data for some tables (as mentioned in the question). – Gili Aug 13 '15 at 14:56
  • @JulienVavasseur Not an option because the data I want to exclude is huge. I need to avoid sending it over the wire in the first place. I've updated the question accordingly. – Gili Aug 13 '15 at 14:57
  • How about this approach: (1) Create a copy of the database, (2) eliminate in the copy all the things you don't want to transfer, (3) backup that database with compression on, (4) restore that database .bak on the "different" remote server. All of these steps can be easily done using TSQL. – Ralph Aug 13 '15 at 15:11
  • Is a linked-server connection an option? – Ralph Aug 13 '15 at 15:13
  • @Ralph The databases I am trying to export are hundreds, sometimes thousands of GBs large. Won't making a copy be prohibitively expensive (space and time wise)? Most of the data is coming from audit logs which I plan on excluding from the export step, so it would be ideal to avoid duplicating this data in the first place. – Gili Aug 13 '15 at 15:14
  • @Ralph I am not familiar with linked-server connections. How would they help in this case? – Gili Aug 13 '15 at 15:23
  • LinkedServerSolution: (1) Create a linked server https://msdn.microsoft.com/en-us/library/ms190479.aspx, (2) Use `SELECT * INTO RemoteServer.RemoteDB.dbo.SomeTable FROM LocalDB.dbo.SomeTable` to quickly copy over the necessary tables, (3) recreate necessary indexes, calculated columns, triggers, etc. – Ralph Aug 13 '15 at 15:30
  • @Ralph I'd still need to copy over the schema. Greg's answer (above) shows the most promise but I can't get the Powershell script working on command-line. Please, post a formal answer (we have too many comments here) and provide concrete command-line options for copying over the schema and the data. I have already succeeded in copying over the data using BCP for a single table, so that works and is relatively easy. – Gili Aug 13 '15 at 15:40

0 Answers0