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.

2010-06-07