SQL - Backup table data and table schema

Posted on 2012-09-14
Medium Priority
Last Modified: 2012-09-14
I would like to make  an exact copy a SQL table with selected columns of data so I can sent to a vendor to debug its software. What is the right way of doing it so the data is exactly the same (i.e. including printed and not printable character) as in the SQL database
Question by:tommym121
  • 2
LVL 16

Expert Comment

ID: 38399070
If your table has fields f1 through f20 (for example) and you want to sen fields f1 through f5, then

select f1, f2, f3, f4, f5
into <new table name>
from <current table name>

Open in new window

should give you a new table with just those fields and the identical info. It will not add in things like indices, though.

Alternatively, make a new database, copy the table there (right-click on current database in SSMS; then Tasks and Export Data), remove the columns you don't want to send, do a backup of that new database to a .bak file, and send them that. They can restore the .bak file and see the info just as you had it in the second DB.


LVL 27

Assisted Solution

Zberteoc earned 2000 total points
ID: 38399071
If the table is not too big script it out including the data. To do that you go in Management Studio and right click on the database > Tasks > Generate Scripts and the wizard will open.

Click Next > Choose Select specific database objects and then in the panel bellow expand the Tables node and check the table(s) you want to script. Click Next. In the next step click on the Advanced button > and from the General options you select whatever you want but make sure you choose Script Create , whet SQL version you want, and don't script USE as the client might have a different db name. In the Table?View Options section at the bottom make sure you choose Data, Indexes, PKs, Uinique Keys, Triggers and FKs if needed. Click OK. Choose Save to File and click Next. The script will be generated and when done click Finish.

Get the file, zip it and send it to the client.
LVL 27

Accepted Solution

Zberteoc earned 2000 total points
ID: 38399089
The script will have the CREATE statement with all the indexes, PKs and other objects you chose and right after that there will be INSERT statements with all the data in the table. If that script is executed by the client against his database it will create and populate the table.

Author Closing Comment

ID: 38399983

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question