Backup a table in SQL Server – How to

Unfortunately, there is no standard or specific feature to backup a table in SQL Server, but there are workarounds.

This can be a very common request from clients, as they might want to backup specific tables from their databases, and as there is no feature in SQL Server that offers this functionality out of the box, then we need to create our own way.

Backup a table

The backup operation of a table is usually done one of two ways, although most people will probably go with the second method.

This will depend on the reason WHY you want to backup the table? There are two questions to answer before deciding/picking the option to go for.

• • Do I need to back up just the data or the table structure and constraints?
    • If you need just the data and you want it locally (on the same database/server) then you can use the first option, through T-SQL scripts.
  • Do I need to back up the table to prevent data loss for a disaster situation?
    • If the answer is yes, then in this scenario the best recommendation would be to back up the entire database. But just to back up the table for a disaster scenario you should use the second option with generating *.sql files with the scripts for creating/inserting data into the table.

Option #1

• 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

Option #2

• The second method is to script the table using the Generate Scripts option from Tasks.
• The below option has 2 parts, the first part is the graphical interface which is common for SQL Server versions 2005 and 2008, followed by the interface which can be found for all SQL Server editions from version 2008R2 and above.

These steps will work for SQL Server 2005 and 2008

After clicking Generate Scripts, a wizard appears. Select Next past the splash screen and select the database that contains the table you want to back up.

The next screen which appears will be the Scripts Options:

Here you might want to choose, but you are not limited or forced to select, the following options:

• • • Check Constraints – True
    • Script Data – True (by ticking this option you will also backup the table’s data, which will be inserted in the table’s script)
    • Foreign Keys – True
    • Indexes – True
    • Primary Keys – True
    • Triggers – True
    • and Unique Keys – True

As said, you can pick what options you consider are most valuable for your table.

Selecting NEXT we will be presented with the Select-Object Types Screen, where we check the Tables checkbox and hit Next.

We are now presented with the tables you want to back up.

At this point, just check the tables you want to be backed up and press Next.

The last dialog box will provide the medium through which you want to output the backup script to.

If you are backing up a large table, data included, we suggest you output to a file.

Advantages:

• • • It facilitates copying over any of the other objects associated with the table.

Disadvantages:

• • • It is slow and thus it is not recommended for very large tables.

Note: If you have created your script file, and then ran it on another system to create the tables and there is no data in the tables, make sure you set the Script Data option to True in the Script Options screen, Table/View section (you can review the steps above).

These steps will work only for SQL Server versions 2008R2 and above

The first step is common, but from this point on, the wizard is different and the options are named differently and found under other drop-down lists.

You go to the list of databases on your server and right-click the database that you want to back up and select Generate Scripts.

This is the first screen of the wizard, and on this screen we press Next.

Now we are going to select the object types that we want to export, in our case the tables. We can check this top option to select all tables or expand the tree and select just a few tables.

On the following screen, we are going to press the Advanced button, which will take us to the part of the wizard we are interested in.

Here we are going to select one of the options under Types of data to script.

• • • Data only – which will export only queries to import data
    • Schema only – which will export queries that will create the structure for the tables, or other dependencies depending on what other options are selected
    • Schema and data – which will create a script containing the queries that will create the tables you selected and also the queries that will import the data into that table.

After you’ve selected one of these options, press OK and make sure that you have set up the path where the file will be created.

Then you can continue to press Next until the script file is created.