How can I take backup of particular tables in SQL Server 2008 using T-SQL Script

  • I want to take a backup of particular tables available in my database in a .bak file, and all these should be done using a T-SQL script.

  • Tufan Chand

    Tufan Chand Correct answer

    5 years ago

    Backup Types are dependent on SQL Server Recovery Model. Every recovery model lets you back up whole or partial SQL Server database or individual files or filegroups of the database. Table-level backup cannot be created, there is no such option. But there is a workaround for this

    Taking backup of SQL Server table possible in SQL Server. There are various alternative ways to backup a table in sql SQL Server

    1. BCP (BULK COPY PROGRAM)
    2. Generate Table Script with data
    3. Make a copy of table using SELECT INTO
    4. SAVE Table Data Directly in a Flat file
    5. Export Data using SSIS to any destination

    Here I am explaining only the first one rest you might be knowing

    Method 1 – Backup sql table using BCP (BULK COPY PROGRAM)

    To backup a SQL table named "Person.Contact", which resides in SQL Server AdventureWorks, we need to execute following script, which

    -- SQL Table Backup
    -- Developed by DBATAG, www.DBATAG.com
    DECLARE @table VARCHAR(128),
    @file VARCHAR(255),
    @cmd VARCHAR(512)
    SET @table = 'AdventureWorks.Person.Contact' --  Table Name which you want    to backup
    SET @file = 'C:\MSSQL\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup
    + '.dat'
    SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
    EXEC master..xp_cmdshell @cmd
    

    OUTPUT

    enter image description here

    Note -

    1. You must have bulk import / export privileges
    2. In above Script -n denotes native SQL data types, which is key during restore
    3. -T denotes that you are connecting to SQL Server using Windows Authentication, in case you want to connect using SQL Server Authentication use -U -P
    4. This will also tell, you speed to data transfer, in my case this was 212468.08 rows per sec.
    5. Once this commands completes, this will create a file named "AdventureWorks.Person.Contact_20120222" is a specified destination folder

    Alternatively, you can run the BCP via command prompt and type the following command in command prompt, both operation performs the same activity, but I like the above mentioned method as that’s save type in opening a command prompt and type.

    bcp AdventureWorks.Person.Contact out C:\MSSQL\Backup\AdventureWorks.Person.Contact_20120222.dat -n -T
    

    enter image description here

  • Method 1:

    If you are only concerned with data in the tables and that to be used locally within the same database and server you can use below query to take backup of selected tables:

    SELECT * INTO newtable1
    FROM originalTable1
    

    --- For table 2

    SELECT * INTO newtable2
    FROM originalTable2
    

    and so on... for n number of tables

    This statement WILL CREATE the tables called newtable1, newtable1,.. thus you do not have to previously create it.

    Note* This method takes the backup quite fast but major disadvantage would be that it does not carry over the Keys, Indexes and Constraints of the table and backup is stored within the database not somewhere externally to a file

    Method 2:

    If you want to backup table over to other server for any disaster recovery solution or data loss prevention, then you can script the table using the Generate Scripts option from Tasks.

    1. First, right click on the database that contains the tables you want to backup and choose Tasks -> Generate Scripts

    2. Select you're database from the list whose tables needs to be backed up

    3. The next screen that appears is the Script Options.

    4. Scroll down on the script options until you see Table/View Options. We want the following to be true: Check Constraints, Script Data, Foreign Keys, Primary Keys, Triggers, & Unique Keys. (Or you can choose whatever you need of course). Select Next and we are presented with the Select Object Types Screen

    5. Select Tables and hit next. Lastly, choose the table or tables you want to backup and hit next

    Method 3:

    You can use bcp utility for table backups as well.

  • You cannot backup specific tables to a .bak file, you can export them to csv or script them out or use bcp to put them into a file.

    What you can do if you want to back up specific tables (and they are always the same) is move them into a separate file group and back up that file group.

    See Back Up Files and Filegroups for the documentation.

    For example if you want to back up specific files or filegroups using T-SQL you could use (from the link)

    --Back up the files in SalesGroup1.
    BACKUP DATABASE Sales
       FILEGROUP = 'SalesGroup1',
       FILEGROUP = 'SalesGroup2'
       TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck';
    GO
    

    and

    --Backup the files in the SalesGroup1 secondary filegroup.
    BACKUP DATABASE Sales
       FILE = 'SGrp1Fi2', 
       FILE = 'SGrp2Fi2' 
       TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck';
    GO
    

    Unfortunately, you have to back up all read/write filegroups together, so you can't choose only one.

  • Building upon Method 1 from KASQLDBA's response:

    Create a separate database to hold the backed up copy of the tables. Modify KASQLDBA's provided query so that you select from your original table and copy to the alternate database.

    SELECT * INTO MyNewDatabase.dbo.newtable1
    FROM OriginalDatabase.dbo.originalTable1
    
    --- For table 2
    SELECT * INTO MyNewDatabase.dbo.newtable2
    FROM OriginalDatabase.dbo.originalTable2
    

    You could create a script that drops the tables in the new database, then run the select into commands for every required table. If you wish to have indexes and other objects avalailable, then you could build those on your new database and truncate the tables prior to re-populating them. If you prefer truncation, then you would need to use insert statements in place of select into.

    This option will provide you a method for creating a .BAK file. You simply run a backup database command after you have populated the tables in the new database.

  • Using the bulk export feature of SSMS, here is the best and easiest method mentioned

    SQL Server import and export wizard: The SQL Server Import and export wizard provides a graphical user interface onto a SQL Server Integration Services (SSIS) package. Once created the package can be automated, to run on a schedule. It can be further configured and modified by using SQL Server Data Tools (SSDT)

    To begin, open the Import and export wizard, right-click a database and select the Tasks sub-menu -> Export data command.

    enter image description here

    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review

    @kevinsky Sure! But at least you should have up voted it!

  • Another method is to use script/sproc: DumpDataFromTable.sql from: https://github.com/Zindur/MSSQL-DumpTable/tree/master/Scripts

     EXECUTE [dbo].[DumpDataFromTable] @SchemaName = 'dbo', @TableName = 'Table', @PathOut = 'c:\temp\Scripts'
    

    You have to pass: schema name and table name, then output path where the created script will be saved (note folder must already created/exist and sql have permission see comment in sproc).

    And if you would like you can add a condition to table (filter must start with AND for the moment)

    this procedure depends on a missing procedure PRC_WritereadFile

  • Backup a single table with its data from a database in SQL Server 2008:

    SELECT * INTO  [dbo].[tbl_NewTable] 
    FROM [dbo].[tbl_OldTable]
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM