How to Take Table Level Backup In MS SQL Server


Overview

SQL Server is the database management system used for storing and retrieving data such as Triggers, Rules, Functions, Tables, and Stored Procedures. Users of SQL Server understand the importance of data backup that can help users restore and recover mistakenly deleted data. Sometimes, user may want to take backup of specific tables from their databases. However, SQL Server does not provide any option for Table-level backup. The article will be discussing alternative ways on how to take table level backup in SQL Server.

Methods to create backup of table in SQL Server

  1. Using BCP (Bulk Copy Program) utility
  2. One possible method to copy SQL Table from one SQL database to another is with the help of BCP utility. This utility is a command line tool that can be used to import large number of new rows into SQL server tables or to export data out of tables into data files.

    The BCP queries needs to be executed in SSMS/ Windows Command Prompt or as a batch script. In order to write/edit scripts, user needs to enable SQLCMD mode in SSMS, which can be enabled by selecting SQLCMD mode under Query.

    • BCP file can be created with the database information with login credentials by using the following command:
    • img-1

    • After creating BCP file, copy it to destination server where table needs to be moved.
    • img-2

    This method is better than Generate scripts option when the user has a very large table or there are binary data in it as Scripts method is only convenient for small tables having less data. In addition, it is an ideal way to take a backup when user wants to transfer SQL table from one server to another where connection cannot be made.

  3. Copy the table using SELECT INTO
  4. Second Method involves copying of the SQL Table using SELECT INTO statement. The basic statement for copying SQL table is as follows:

    SELECT *
    INTO New Table
    FROM Original Table

    The above statement will copy the desired contents of ‘Original Table’ to ‘New Table’ after creating the ‘New Table’. The statement also allows user to use WHERE clauses for filtering of data from the table, if not the entire table should not be backed up. This method is just a way of copying the SQL table to another new table that act as a backup. The good point of this method is that it can copy number of rows in the table very quickly. The disadvantages are it cannot carry over the keys, indexes, and constraints of the table and backup are still stored in the database.

  5. Using Generate Scripts option from Tasks
  6. The third method to backup table to another server for disaster recovery or data loss prevention is to script the table using Generate Scripts option from Tasks.

    Steps to script the SQL Table in SQL Server 2005/2008 are as follows:

    • Right-click on the database that contains the desired table you wish to backup. Go to Tasks > Generate Scripts
    • img-3

    • After clicking on Generate Scripts, wizard will be opened. Select the database containing table for backup and click on Next
    • img-4

    • The script options will appear and scroll down until you see Table/View Options.
    • img-5

    • Select the scripts that need to be set true: Check Constraints, Script data, Foreign Keys, Primary Keys, Triggers, and Unique Keys.
    • You can select the options that are valuable for your table. Click on Next to proceed.
    • Object Types window will be opened where you will check the Tables. Click Next.
    • img-6

    • Option to choose table will appear. Select the desired ones and click on Next
    • img-7

    • Last window will provide the medium through which you want to output the backup script.

    Generating Scripts method allows copy of any of the objects associated with the table. However, this method is slow and is not recommended for backup of large tables.

    Steps for backup in SQL Server versions 2008R2 and above are as follows:

    The steps are almost similar but the wizard is different and the options are named little different.

    • The first step is same. Open the list of databases on your server, right-click on the database that contains the table for backup and go to Tasks > Generate Scripts
    • Generate and Publish Scripts wizard will be opened. Click on Next
    • img-8

    • Choose the specific database object i.e. table and Next
    • img-9

    • Select Advanced Scripting Options to choose the types of data to script.
      Data only– export only queries to import data
      Schema Only– Export queries to create structure for tables or other dependencies on what other options are selected.
      Schema & data– create a script containing queries both for creating table and to import data into that table.
    • img-10

    • Select ‘Schema and Data’ option and click on OK.
    • Select the format how script should be saved. Choose the desired location to save.
    • img-11

    • Click on Next until the script file is created.

Conclusion

The page has been aimed to discuss the importance of backup of data in SQL Server. It further defines the ways to take backup of specific database objects like Table. The user can take table level backup in SQL Server by using BCP utility, SELECT INTO query or by Generating scripts of the table. The advantages and disadvantages of each kind of backup have been included in the page. It will guide user to choose the desired backup method according to the size of table.