how to take backup and restore big data archive selected table in sql server

Lily June 101 Reputation points
2021-12-01T07:51:34.923+00:00

I have big database and I would like to backup archive data tables and restore if need client request in future.
table space is over 400GB
Pls help advice.
here's my archive table size

153999-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,268 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 38,941 Reputation points
    2021-12-01T08:15:33.697+00:00

    You can not backup single table, only the complete database.
    You could create a separate database with that archive data/table and create in prod database a view with a cross-database query to archive database.


2 additional answers

Sort by: Most helpful
  1. AmeliaGu-MSFT 13,956 Reputation points Microsoft Vendor
    2021-12-01T08:42:40.383+00:00

    Hi LilyJune-5583,

    Agree with Olaf.

    It is not supported to backup specific tables to a .bak file currently.
    You can use alternative ways to copy specific tables to flat files or other databases.
    Please refer to the following articles which might be help:
    Six different methods to copy tables between databases in SQL Server
    good strategy for big tables and archiving
    An Automated Process to Archive Big Tables

    Best Regards,
    Amelia


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. TADA SUDEER RAO 0 Reputation points
    2023-06-17T10:07:33.8366667+00:00

    Huge/Large SQLSERVER Table to .BAK file

    Step 1 : Create a new DEMO database.

    Step 2: Use import/export wizard, copy the huge table from Production Database to DEMO database.

    Step 3: Take the backup of the DEMO database. This DEMO.BAK will contain only the backup of the large table data.

    0 comments No comments