0

Previously I worked with My SQL where I dumped entire database as an SQL script that includes 'CREATE TABLE' and 'INSERT' statements.

1) Is it possible to do the same in MS SQL?

For example, If I select 'Tasks->Export Data' in MS SQL Management Studio and then select 'Flat file source' in the combobox I get a text fine in comma separated format, but not an SQL script.

2) How to dump not entire database, but only selected tables?

3) Or at least what is the right (or most common) way to export tables as a text file in MS SQL?

Alexey Starinsky
  • 3,699
  • 3
  • 21
  • 57
  • 1
    Tasks -> Generate Script has you covered. It can script data as well by ticking the appropriate box in the advanced options. Note that this is entirely a function of Management Studio; SQL Server (the engine) includes no scripting facilities of its own. – Jeroen Mostert Aug 20 '19 at 15:06
  • https://www.dataaccess.com/blog/systems-environments/sharing-sql-server-databases-via-scripting-1336 – SouXin Aug 20 '19 at 15:06
  • [Here is an easy way](https://stackoverflow.com/questions/2321052/how-to-get-script-of-sql-server-data) – S3S Aug 20 '19 at 15:06
  • @JeroenMostert I do not see an option here to save not only 'CREATE TABLE', but the table data. – Alexey Starinsky Aug 20 '19 at 15:12
  • 1
    From within the wizard: Set Scripting Options -> Advanced -> Types of data to script, change from "Schema only" to "Schema and data". OK, so I misremembered, it's not an actual *box* you tick. :-P – Jeroen Mostert Aug 20 '19 at 15:13
  • 1
    If you're wanting to take backups this way, as you would for MySql, **don't**. Sql Server has other backups methods that are far superior, and script method has all sorts of downsides you may not be aware of. For the question of the `What is the most common way to export tables as text in SQL Server?`... the answer is this is **not done** in Sql Server. We have better ways. Text files are extremely poor at this, and MySql's way of using text files is the weaker and more limited option. – Joel Coehoorn Aug 20 '19 at 15:14
  • @JoelCoehoorn my colleagues asked me to send them some tables. – Alexey Starinsky Aug 20 '19 at 15:16
  • 1
    Just take a backup, which can use compression, can be protected with encryption, etc. Generating 5GB, 10GB, 20GB .sql files is far from optimal. – Aaron Bertrand Aug 20 '19 at 15:16
  • 2
    @AlexeyStarinsky So? Send them a .bak. You can't "send them some tables" - you can send them a script that creates them, or a .bak that restores them. One of these is superior to the other in at least a dozen ways. – Aaron Bertrand Aug 20 '19 at 15:17
  • @JeroenMostert It works! – Alexey Starinsky Aug 20 '19 at 15:21

1 Answers1

1
  1. Is it possible to do the same in MS SQL?

Yes

  1. How to dump not entire database, but only selected tables?

From SQL Server, you can select database >> Right Click >> Task >> Generate Script >> Select table to be exported You can explore more here, like if you want to generate script with or without data

  1. Or at least what is the right (or most common) way to export tables as a text file in MS SQL?

You can take a backup of your database and restore it anywhere.

Mohammed Dawood Ansari
  • 875
  • 11
  • 24
  • 1
    No, no, no, [**PLEASE** do **NOT** suggest people should copy MDF and LDF files as a backup](https://www.sentryone.com/blog/aaronbertrand/bad-habits-file-backups). – Aaron Bertrand Aug 20 '19 at 15:15
  • I read the article, I understand the issue and I have updated my answer accordingly, thank you so much for your comment and handling the situation without marking my answer as negative, I felt like being mentored :) – Mohammed Dawood Ansari Aug 20 '19 at 15:26