Backup a Single Table in SQL Server using SSMS

Our buddy Buck Woody made an interesting post about a common question: "How do I back up a single table in SQL Server?"

That got me thinking about what a backup of a table really is. BCP is often used to get the data but you want the schema as well.

For reasonable-sized tables, the easiest way to do this now is to create a script using SQL Server Management Studio. To do this, you:

1. Right-click the database (note not the table)

2. Choose Tasks > Generate Scripts

3. In the Choose Objects pane, select the table you want to script

4. In the Set Scripting Options pane, click Advanced.

5. In the Types of Data to Script option, choose Schema and Data. (If you also want indexes, etc. make sure they are also chosen)

Click your way through the remaining screens and you're done.

29 thoughts on “Backup a Single Table in SQL Server using SSMS”

  1. Hi,
    Why I can not see 'Advanced' button in step
    4. In the Set Scripting Options pane, click Advanced ?

  2. Hi Cany,
    If you are using SSMS 2008, steps that you need:
    – Right click on the DB
    – Choose "Tasks" and then "Generate Scripts" and "Script Wizard" dialog box will appear. Then click NEXT.
    – Choose DB (don't know why it ask you again?? :P) and NEXT.
    – You are at "Choose Script Options" page now. Scroll down to "Table/View Options" and you'll see "Script Data". Set to TRUE if you want to backup the data in the table too. You can also choose other options as you wish. Hit NEXT.
    – Select object you want to script.
    – Follow the rest of the instruction.

  3. Have you considered using Integration Services? That's the type of repetitive task that it's designed to do.

  4. Hi Sunil,
    Yes, but a local copy of Integration Services can connect to SQL Azure just the same. Or are you trying to copy the table to Azure storage or something? (And is it large?)

  5. hey man i tried this but the script generated was to create table is it like after generating script should i change the command to backup and run!!!!!! pls advise…

  6. Sorry, don't follow. There are scripting options for creating (or dropping) the table, and/or for including the data when generating the script. This is just a way of scripting a table with it's data in it. You then need to use the script that's generated on another DB.

  7. Guys in MSSQL 5 use
    select * into NewTableName from ExistingTableName
    make sure NewTableName  is not available in DB and ExistingTableName is already available in DB.
    This query create a new table with schema of existing table as well as dump complete data.

  8. Hi Ashwini,
    That creates a new table with the same contents of the old one, not a backup of the table. (Backups can be taken elsewhere).

  9. select * into NewTableName from ExistingTableName
    It will not create Constraints and keys on new table.

  10. Yes, if all you want to do is copy the table's data to a new table, that works fine. But it won't help with moving the data elsewhere

  11. I've used it up to reasonable-sized tables but you need to keep in mind the size of the SQL script generated. That's the main limitation. They get hard to work with when they get large.

Leave a Reply

Your email address will not be published. Required fields are marked *