How to Backup a Table

There is not standard method for backing up a table in SQL Server.  A common request I receive from clients is to only backup specific tables within a database.  Well, unfortunately there is no out of the box method for doing this akin to a database backup so we need to roll our own way.

It is usually done one of two ways.  The first is to use a select into method to copy the table.  The other way is to script the table out using the Generate Scripts Tasks.  Both methods have positives and negatives.  First off, the SELECT.. INTO Method is by far the fastest.  It can copy a large number of rows very quickly, the downfall to this however, is that it does not carry over the Keys, Indexes or Constraints.  The Generate Scripts method is slow (and I don’t recommend it for very large tables), however it can facilitate copying over any of the other objects associated with the table.  Let’s look at the generate scripts task:

First, right click on the database that contains the table you want to backup and choose Tasks -> Generate Scripts.

The Generate Scripts Wizard Appears.  Select “Next” past the splash screen then select the database that contains the table.  The next screen that appears is the Script Options.

Scroll down on the script options until you see Table/View Options.  We want the following to be true: Check Constraints, Script Data, Foreign Keys, Primary Keys, Triggers, & Unique Keys.  (Or you can choose whatever you need of course).  Select Next and we are presented with the Select Object Types Screen.

Select Tables and hit next.  Lastly, choose the table or tables you want to backup and hit next.

The last dialog provides the medium you want to output the script to.  If you are backing up a large table, I suggest you output to a file.  Good luck!