27

Want to create a script to export Data and tables and views to a sql script.

I have SQL Server 2008 r2.

So far I've only been able to automatically generate an sqlscript for all tables and views. But the data wasn't included.

OR is there any easier way to export data, tables, views, from one SQL Server to my ISP's SQL server?

Regards Tea

TeaDrinkingGeek
  • 1,995
  • 5
  • 34
  • 52

7 Answers7

63

If for some reason a backup/restore won't work for you, SSMS' Generate Scripts tool includes an advanced scripting option to include data:

enter image description here

John Dewey
  • 6,985
  • 3
  • 22
  • 26
6

Here are some options to think over (prioritised in terms of what I would recommend):-

  • A simple backup and restore will be the easiest and quickest solution;
  • Using a data scripting tool (like Red-Gate's Data Compare) could solve your needs;
    • Use the database comparison as part of Visual Studio.
  • A SSIS package could be developed to pump data back and forth between the two instances; or
  • Write your own script using the SET IDENTITY INSERT ON / OFF command for the identity seeded tables
Kane
  • 16,471
  • 11
  • 61
  • 86
  • I agree that a scripting tool is usually the best option. Red-Gate and other providers definitely filled that gap for a while. Just thought to mention that SSMS now serves just fine (though the generated SQL could be better by chaining the VALUES clauses). – Eniola Apr 29 '15 at 07:04
4

The easiest way to do this is to create a backup, copy the .bak file to the other server, and restore the backup there.

jhewlett
  • 324
  • 3
  • 9
  • Will it work even if the SQL server is in another company eg ISP? It won't look for my name in the backup will it? – TeaDrinkingGeek Dec 11 '12 at 21:47
  • @TeaDrinkingGeek no it won't care. As long as you are going between the same version of SQL at both ends this should be fine. You should also be able to go to newer versions (e.g. 2008 to 2012) but I don't think you can go to an older version (e.g. 2008 to 2005) – Greg Dec 14 '12 at 02:22
1

Like @jhewlett said that will be the best way to do it. to answer the question in the comment section. no it shouldn't be a problem. Just make sure that the SQL Server Versions are the same. Had a bit of an issue not to long ago where there were two pc's with different releases of the R2 installed and couldn't restore the backup. Other thing you can also do is to script the entire database with data, but this will not be recommended as it could take a long time to generate the script and for it to finish running on the other computer.

Or you can simply just stop the SQL server instance and copy the database away onto an external hard drive and re-attach it to the other server. just remember to start the instances after doing this step.

Louis
  • 134
  • 1
  • 3
  • 16
  • Copy and attach is really brute force. Don't you think backup and restore is neater in the event that scripting is not an option. – Eniola Apr 29 '15 at 07:06
1

I use Navicat Premium for these kind of things in mysql. It generates sql from data, tables, views and anything else. It provides tools to copy or synchronize table from one database on different server or platforms as well. For example I use it so much to transfer my tables from MySQL to a SQLite database, So easy and fast. Otherwise I had to transfer it manually with so much trouble.

very good tool and required for any DB admin or programmer. It support MySQL, Oracle, MS SQL Server, PostgreSQL and SQLite.

Ali
  • 21,572
  • 15
  • 83
  • 95
  • Navicat worked a treat exporting table and structure out of a sql server db. I'd tried the usual export using SQL Server Management Studio but it kept erroring - some sort of permissions problem with the user I was told to use. – Lanceomagnifico Feb 20 '15 at 16:25
1

To Generate a schema with data follow these steps.

  • Select database to generate a schema '>' right click '>' Tasks '>' Generate schema '>' click NEXT in popup window '>'
  • select DB object to generate schema and click NEXT '>'
  • Go to advance option and scroll down '>'
  • Find Type of data to script and select one option as you need. '>' and then Next Next and finish it.
  • Enjoy it.

As in image

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Hafiz Ameer Hamza
  • 462
  • 1
  • 4
  • 14
0

If you don't want to port all tables data (for example you need to port just some base data in special tables) scripting options is not useful for you. In this case you'll have two options. First is using some third parties tools such as Red-Gate and Second way is writing the script by yourself. I prefer Second option because except the expensive price of most of them i want to run just little script for little delete, update and inserting purpose. But the important problem is here: maybe the record count is too long to write scripts record by record. I Think the linked Server is good point to solve that. It's enough for describing Just Declare Linked Server as you see in Images and get new script in your source DB and write scripts with access to both source and destination DB. Attached image must be clear.

Create New Linked Server:

enter image description here

Write Destination SQL Server Address:

enter image description here

Fill Login Info:

enter image description here

Now you have Linked Server:

enter image description here

Write script and enjoy:

enter image description here

Hope this help.

QMaster
  • 3,743
  • 3
  • 43
  • 56
  • While your method works, it's definitely a hard and convoluted way of going about it. And it increases the surface area of your security considerations. – Eniola Apr 29 '15 at 07:00
  • @Eniola you right. I don't say it is best way. I just talked about another way we could have most control on details of schema and data I believe. In normal case I'll use SQL Server Generate Scripts but in rare cases specially when I want to port some data from one DB to another I prefer this way. Thanks for your notice. – QMaster Apr 29 '15 at 21:44