Standard SQL Server backup does not support backup of a separate table, but this does not mean that this task cannot be solved in principle. Several tools and tricks allow you to backup a single table with data.

However, please pay attention that the fact that you need to backup an individual table, in some cases, may be a sign that the database is poorly designed. Perhaps the table you want to backup needs to be moved to a separate database. SQL Server supports cross-database queries, and at the SQL query level there is access to tables in other databases.

The main problem with backing up a separate table is that the backup data in the table is not consistent with the rest of the database, which can lead to logical errors in the data or banal foreign key errors.

This article will discuss the following methods for creating a backup of an individual table:

1. Backup a table to an SQL file via CLI Tool
2. Backup a table to an SQL file via SSMS
3. Export SQL Server table with data via SSMS
4. Export SQL Server table with data via CLI Tool
5. Trick for backing up an individual table to a BAK file

1. Backup a table to an SQL file via CLI Tool

The Microsoft account on Github has a repository called MSSQL-Scripter. This is a command-line utility written in Python and installed through the Python Package Manager (pip).

This utility allows you to generate an SQL script that will recreate the database.

One of the parameters of this utility allows you to specify which tables should be scripted; to do this, you need to specify the table name in the --include-objectsparameter:

mssql-scripter --server . --database Curseria --schema-and-data --include-objects Students > Students.sql

At the beginning of the Students.sql file, there will first be a CREATE TABLE statement to create a table, and then many INSERT INTO lines.

To restore a table, it is sufficient to execute this script in SQL Server. Through the command line, this can be done using the sqlcmd utility, which is installed with SQL Server:

sqlcmd -S . -d Curseria -i Students.sql

2. Backup a table to an SQL file via SSMS

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 SELECTstatement, you can have WHEREclauses 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 that appears will be the Script Options:

Here you might want to choose the following options (though you are not required to):

• 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
• Unique Keys: True

Pick the options that you consider 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, 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.

3. Export SQL Server table with data via SSMS

SQL Server database data can be exported to a .bacpac file, primarily used for data migration between different versions. However, you can use an export data tier application as a logical backup. When setting up, you can choose which specific tables should be exported.

1. Right-click on a database > Task > Export Data-tier Application
   
2. On the Export Settings tab, specify where to save the backup, and then go to the Advanced tab where you can select specific tables.
   
3. Click through the setup wizard to the end

A .bacpac file can be restored only to a new database

1. Right-click on a database > Import Data-tier Application
   
2. Set the path to the .bacpac file
   
3. Specify the name of the new database
   

After the restore process, you can transfer tables from this new database to the desired one using INSERT INTO …. SELECT

4. Export SQL Server table with data via CLI Tool

Export Data-Tier Application can be executed from the command line using the sqlpackage.exe utility. This utility is official and can be downloaded from here.

After unpacking the application, you can execute it:

sqlpackage.exe /a:Export /SourceServerName:server-name /SourceDatabaseName:db-name /TargetFile:"c:/backup/file/path.bacpac" /SourceTrustServerCertificate:True /p:TableData=[dbo].[Students]

sqlpackage has historically a non-typical command line syntax, however, like in the other utilities, you just need to specify the required values in the parameters:

•  /SourceServerName: server’s name, for the default SQL Server instance the dot character can be specified
•  /SourceDatabaseName: database’s name
•  /SourceTrustServerCertificate:True specify True if you trust the server
•  /p:TableData= the table’s name that should be backed up; the table’s name should be like [schema-name].[table-name]
•  /TargetFile: path to the file that will be created by this command

Example command:

sqlpackage.exe /a:Export /SourceServerName:. /SourceDatabaseName:Curseria /TargetFile:"Students.bacpac" /SourceTrustServerCertificate:True /p:TableData=[dbo].[Students]

You can restore the created .bacpac either as described above, or using sqlpackage.exe but with other parameters:

sqlpackage.exe /a:Import /TargetServerName:server-name /TargetDatabaseName:new-db-name /SourceFile:"c:/backup/file/path.bacpac" /TargetTrustServerCertificate:True

This command allows you to restore the database from the .bacpac file created by the previous command. It is important to indicate:

•  /TargetServerName: server’s name where the database will be imported; for the local SQL Server instance the dot character can be specified
•  /TargetDatabaseName: the name of the new database that will be created as a result of the import
•  /SourceFile:path to the .bacpac file that was created earlier
•  /TargetTrustServerCertificate:True specify True if you trust the server

5. Trick for backing up an individual table to a .bak file

As SQL Server does not support direct backup of an individual table in .bak format, there is a workaround that allows you to perform this task.

To do this, you need to create a temporary database, copy the necessary table into it, and make a backup of this new database.

Open the editor in SSMS and execute the CREATE DATABASEstatement:

CREATE DATABASE Students_table

Then copy the needed table into it:

SELECT * INTO Students_table.dbo.Students FROM OriginalDB.dbo.Students

Note that this command itself creates the required table if it does not exist.

Perform a backup of the temporary database using the BACKUP DATABASEcommand:

BACKUP DATABASE Students_table TO DISK = 'C:\Backup\Students_table.bak'

Remove the temporary database:

DROP DATABASE Students_table

This can be difficult to do manually, so you can make a simple script to perform these actions:

sqlcmd -s . -Q "CREATE DATABASE Students_table;"
sqlcmd -s . -Q "SELECT * INTO Students_table.dbo.Students FROM Curseria.dbo.Students_table;"
sqlcmd -s . -Q "BACKUP DATABASE UrlPaths_table TO DISK = 'C:\Backup\Students_table.bak'"
sqlcmd -s . -Q "DROP DATABASE Students_table"