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.

backup table 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.

backup table script wizard

Table/View 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.

script_wizard_backup_table

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

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!

5 comments
Howard Rothenburg 09 Dec 2014 at 7:00 pm

To export:

bcp “[MyDatabase].dbo.Customer” out “Customer.bcp” -N -S ServerName -T -E -U”xxxxxx” -P”xxxxxxx”

To import:

bcp “[MyDatabase].dbo.Customer” in “Customer.bcp” -N -S ServerName -T -E -b 10000 -U”xxxxxx” -P”xxxxxxx”

Juri 08 Jul 2016 at 6:45 am

Hello,
I try backup table, but I have’nt in Choose Script Options in Table/View Options- .. choice “Script Data” . I use SQL server 2005, login as “sa” .

I want to do the following :

1. Remember the contents of two tables
2. overwrite the contents of two tables
3. perform some process
4. return memorized the contents of two tables

thanks Juri

Abdul 05 Dec 2013 at 2:25 pm

What option do you use to include the data as part of the backup. and how to restore the data you backed up to the existing table if needed?
thanks

Anandan 29 Nov 2010 at 2:57 am

Hi,

This is further to the tip about “How to backup a table”. Out of the 2 menthods, you suggested by using the Generate Scripts mentod. Just I would like to confirm that the 2nd menthod will take the script of the table and then we need to import the data separately, is it not?

Derek Dieter 29 Nov 2010 at 8:14 pm

Hi Anandan,

Actually no, you do not need to manually import the data into the table if you script the table out and include the data.

Derek

Featured Articles

 Site Author

  • Thanks for visiting!
css.php