ANSWER:

So far by the given answers you are backing up your base table data, but not the table itself. The table has many other attributes, conducting a SELECT * INTO basically just get’s you the cell values and column structure. Although not complete, some additional attributes to consider:

• Partitioning Scheme/Functions
• Indexes
• Foreign Key Relationships
• Object Level Permissions
• -Extended Properties
• etc.

This is a very large table, so I wouldn’t be surprised to find partitioning and heavy indexing. The best answer so far IMO is to backup the entire database. If that is not a good option, script out the entire schema and supporting schema (in case of items that reside outside the table like in partitioning). Have a copy of that, then do a SELECT INTO.

Of course, some very knowledgeable people such as Kim Tripp recommend adding your indexes and sometimes partitioning after you dump the data, which is probably sound advice. Just remember if you do partition after you dumped the data, you will want to use a check constraint to ensure partition elimination happens. That’s something to look into if you actually see it configured though.

Ali Razeghi from http://dba.stackexchange.com/questions/42748