How to Backup a table in SQL Server with SELECT INTO statement

 The first method involves using a SELECT INTO statement to create a copy of the table. The basic format of this statement is as follows:

SELECT *
INTO tableCopy
FROM originalTable

This statement WILL CREATE the table called tableCopy, thus you do not have to previously create it. Due to the fact that the statement is a SELECT statement, you can have WHERE clauses if you want to filter your data, or you can add specific columns into your table copy, if not the entire table should be backed up.

This form of backing up a table is not as the traditional method of backing up a database to a file, as it is just a simple way to create a copy of a table, in the same database, which you can later use in the form of a backup.

Advantages:

• • • This method is by far the fastest. It can copy a very large number of rows very quickly.

Disadvantages:

• • • Unfortunately, by using SELECT INTO, the major downfall is that it does not carry over the Keys, Indexes and Constraints of the table.
    • Also, the backup of the table is still stored in the database