{"id":1923,"date":"2010-11-21T18:56:48","date_gmt":"2010-11-22T01:56:48","guid":{"rendered":"https:\/\/sqlserverplanet.com\/?p=1923"},"modified":"2012-12-22T05:24:22","modified_gmt":"2012-12-22T05:24:22","slug":"how-to-backup-a-table","status":"publish","type":"post","link":"https:\/\/sqlserverplanet.com\/dba\/how-to-backup-a-table","title":{"rendered":"How to Backup a Table"},"content":{"rendered":"

There is not standard method for backing up a table in SQL Server. \u00a0A common request I receive from clients is to only backup specific tables within a database. \u00a0Well, 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.<\/p>\n

It is usually done one of two ways. \u00a0The first is to use a select into method to copy the table<\/a>. \u00a0The other way is to script the table out using the Generate Scripts Tasks. \u00a0Both methods have positives and negatives. \u00a0First off, the SELECT.. INTO Method is by far the fastest. \u00a0It 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. \u00a0The 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. \u00a0Let’s look at the generate scripts task:<\/p>\n

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

\"backup<\/a><\/p>\n

The Generate Scripts Wizard Appears. \u00a0Select “Next” past the splash screen then select the database that contains the table. \u00a0The next screen that appears is the Script Options.<\/p>\n

\"backup<\/a>

Table\/View Options<\/p><\/div>\n

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

\"script_wizard_backup_table\"<\/a><\/p>\n

Select Tables and hit next. \u00a0Lastly, choose the table or tables you want to backup and hit next.\"script_wizard_choose_tables\"<\/a><\/p>\n

The last dialog provides the medium you want to output the script to. \u00a0If you are backing up a large table, I suggest you output to a file. \u00a0Good luck!<\/p>\n","protected":false},"excerpt":{"rendered":"

There is not standard method for backing up a table in SQL Server. \u00a0A common request I receive from clients is to only backup specific tables within a database. \u00a0Well, 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. \u00a0The first is to use a select into method to copy the table. \u00a0The other way is to script the table out using the Generate Scripts Tasks. \u00a0Both methods have positives and negatives. \u00a0First off, the SELECT.. INTO Method is by far the fastest. \u00a0It 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. \u00a0The 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. \u00a0Let’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. \u00a0Select “Next” past the splash screen then select the database that contains the table. \u00a0The next screen that appears is the Script Options. Scroll down on the script options until you see Table\/View Options. \u00a0We want the following to be true: Check Constraints, Script Data, Foreign Keys, Primary Keys, Triggers, & Unique Keys. \u00a0(Or […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_s2mail":"yes","footnotes":""},"categories":[6],"tags":[],"class_list":["post-1923","post","type-post","status-publish","format-standard","hentry","category-dba"],"_links":{"self":[{"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/posts\/1923","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/comments?post=1923"}],"version-history":[{"count":4,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/posts\/1923\/revisions"}],"predecessor-version":[{"id":2980,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/posts\/1923\/revisions\/2980"}],"wp:attachment":[{"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/media?parent=1923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/categories?post=1923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/tags?post=1923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}