Backing up a single table with its data from a database in SQL Server 2008

Question

I want to get a backup of one table with its data from a database in SQL Server using a script.

How can i do this?

Answer 1

There are many ways to get back to the table.

1. BCP (BIG COPY PROGRAM)
2. Generating a table script with data
3. Make a copy of the table using SELECT INTO
4. SAVE table data directly to a flat file
5. Export data using SSIS to any destination

Answer 2

select * into mytable_backup from mytable 

Creates a copy of the mytable table, and each row in it is called mytable_backup .

Answer 3

You can create a table script with its data by following these steps:

1. Right click on the database.
2. Select Tasks> Create Scripts ...
3. Press "Next.
4. Press "Next.
5. In the table / view settings, set the script data to True; then click Next.
6. Check the "Tables" box and click "Next."
7. Select a table name and click Next.
8. Click Next until the wizard is ready.

For more information, see Eric Johnson's Blog.

Answer 4

You can use the "Create a script object for database objects" function in SSMS.

• Right click target database
• Select Tasks> Generate Scripts
• Select the desired table or specific object
• Click Advanced
• In the "General" section, select the value for the Script data types . You can select Data only, Schema only, and Schema and data . The schema and data include both the creation of the table and the actual data for the generated script.
• Click Next until the wizard runs.

This solved my problem.
Hope this helps you too.

Answer 5

Try using the following query, which will create the corresponding table in the same or another database ("Database").

 SELECT * INTO DataBase.dbo.BackUpTable FROM SourceDataBase.dbo.SourceTable 

Answer 6

Put the table in your own filegroup. Then you can use the regular SQL Server built-in backup to back up the filegroup, which essentially backs up the table.

To back up a filegroup, follow these steps: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-files-and-filegroups-sql-server

To create a table in a filegroup other than the default (its simple), see Create a table in a filegroup other than the default

Answer 7

Backing up a single table with its data from a database in SQL Server 2008

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

Answer 8

Another approach you can take if you need to back up one table from several tables in the database:

• Create a script of specific tables (tables) from the database (right-click the database, select "Task"> "Generate Scripts ..."

• Run the script in the query editor. You must modify / add the first row (USE DatabaseName) in the script to the new database in order to avoid the "Database already exists" error.

• Right-click on the newly created database and select Task> Create Backup ... The backup will contain the selected table (s) from the source database.

Answer 9

This query is executed for me (for MySQL). mytable_backup must be present before executing this request.

 insert into mytable_backup select * from mytable 

Answer 10

To get a copy in a file on the local file system, using the Windows start button, this utility started working: "C: \ Program Files (x86) \ Microsoft SQL Server \ 110 \ DTS \ Binn \ DTSWizard.exe"

Answer 11

Try this code to insert all the data from the table from the old Db into another Db into the new table

 SELECT EmpCode, EmpName,DOB,MobileNo INTO [dbo].[tbl_EmployeesNew] FROM [dbo].[tbl_EmployeeOld]