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.
No comments:
Post a Comment