SQL - Backup table data and table schema

Posted on 2012-09-14
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
    LVL 16

    Expert Comment

    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 26

    Assisted Solution

    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 26

    Accepted Solution

    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


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    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 …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now