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.
- Do I need to back up just the data or the table structure and constraints?
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.
Will Option 2 work for Sql Server 2012?
Hi Ruslan,
Yes, Option #2 will work for SQL Server version 2005 and above.
Regards,
Radu
When you copy a table using a script, how do you designate the target database/table name? I don’t see any reference to that. I want to copy a table from one databases to another while not overwriting the same table name, so I can give it a different target name.
Hi Bob,
you can specify the Database name at the time of execution of script
For Example :-
USE [detail_epro]
GO
hi
I tried to execute the script to the another instance. I change the name of the database and it gives me following error message.
Msg 1921, Level 16, State 1, Line 8
Invalid partition scheme ‘PDO’ specified.
Msg 208, Level 16, State 1, Line 18
Invalid object name ‘dbo.films’.
what should to do Please advise me
Many Thanks
Radu,
Generate script task doesnot generate mapping related information for specific user in the database, for ex if a user has read and write only roles assigned on a database and in addition to that if a DBA goes to mapping link and assigns alter to this user on specific table. then that will not be generated via this “generate scripts” option. Please suggest alternate method
Hi Amit,
The scope of the article is in general, to provide details as to how to backup the data in a specific table.
But your point is valid also, if you intend on keeping user permission. Although for this situation which you are looking for a resolve, I would recommend a regular FULL backup, you can achieve the same thing but at the table level.
In order to keep user table permissions, at the window where you select the type of data to script, in the General section there is an option called: “Script Object-Level Permissions”, which you will have to set to True.
hello sir
my question is how to restore the generated script .sql file to same system or other system. plz reply asap
Hello,
In order to restore the generated script file just open SQL Server Management Studio and open the .sql file in this program.
You will then be able to execute the entire script and it will create the table schema and/or data, depending on the options you selected for creating the script file.
Please note that although opening on the same system you might want to run the script on a different database, which is why you should either remove or replace the first line of the script which will have “USE“.
This is by default the name of the database from which the .sql file containing either the table schema and/or data was created.
Please comment back if this has helped you.
from where I can take a full kit to download sql server wich has Maintenance Plans
when I tried to copy a [Sales].[SalesOrderHeader] table to another database ‘ABC’ I am getting this error:
Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37
Object is invalid. Extended properties are not permitted on ‘Sales.SalesOrderHeader’, or the object does not exist.
Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37
Object is invalid. Extended properties are not permitted on ‘Sales.SalesOrderHeader.PK_SalesOrderHeader_SalesOrderID’, or the object does not exist.
Msg 1088, Level 16, State 11, Line 1
Cannot find the object “Sales.SalesOrderHeader” because it does not exist or you do not have permissions.
Processed 100 total records
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘Sales.SalesOrderHeader’.
I am creating a plugin , and that plugin need to create a table while installing a plugin for its data, and when i uninstall a plugin , it is automatic deleted table which is created by this plugin,
So my question is, if i get backup that tables data while uninstalling plugin, and should i use that backup while i reinstall the plugin again?