How to Backup Table in SQL Server – A Step-by-Step Guide

We usually deal with various tables and accomplish different operations through several database tools like PostgreSQL, MYSQL, Oracle, etc. Many businesses or organizations take a backup table in SQL Server as a precautionary need. Because of this, they keep crucial data in their tables, such as employee information, stock, and inventory data. So, this blog will discuss how to take table backup in SQL Server to prevent data loss. But before moving towards the solutions, users should be aware of all the riskiness and some necessary conditions.

 

• The particular table is complete and independent.
• Keep data and scheme aside. Leave all things as it is.

How to Create a Table in SQL Server?

We will opt for the SELECT INTO Statement, which will help us to copy the SQL Tables.

• Create a new target table and insert the below data of the original table into it.

use DatabaseName

go

select * into TargetTable from OriginalTable

go

• After that, copy and put the data in the original table to the existing target table. It will not impact the current data in the target table.

use DatabaseName

go

insert into TargetTable select * from OriginalTable

go

• Copy only the structure of the actual table into the desired table.

use DatabaseName

go

select * into TargetTable from OriginalTable Where 1=2

go

Note: TargetName: You can highlight the name of the newly targeted table in the query.

Where 1=2: It will create a new table and filter out all the data.

Now, we will move towards the procedure to SQL table backup restore table in SQL server.

How to Take The Backup Table in SQL Server?

We have several methods to create backup table in SQL. You just need to read the entire blog to get your answer.

1. Create Scripts in SSMS to Take Backup Tables
2. Opts SQL Server Import and Export Tool to Backup Tables
3. Run BCP Commands to a backup table in SQL server

Now, we will discuss each method in detail. It will help you to have a better understanding.

Method 1: Create Scripts in SSMS to Take Backup Tables

• Open SSMS or Microsoft SQL Server Management Studio and connect to your server.
• After that, Right-click on the database you want to create a backup of tables.
• Then, choose Tasks > Generate Scripts.
• Move to the Choose Objects page > select specific database objects option.
• Here, tick the option of tables you need to create a backup > Next button.
• Afterwards, click on the Advanced button under the Set Scripting Options page. It will show the Advanced Scripting Options window.
• Choose Types of data to script option > Scheme and data > OK button.
• Back to the Set Scripting Options page, choose Open in the new query window > Next button.
• Moving ahead, click the Next button to save scripts > Finish button.

The above procedure will help you to a backup table in SQL Server.

Method 2: Opts SQL Server Import and Export Tool to Backup Tables

• Open SSMS or Microsoft SQL Server Management Studio and connect to your server.
• Right-click on the Database to SQL server create backup table with data.
• After that, choose Tasks > Export Data.
• Then, choose SQL Server Native Client 11.0 in the Data Source list under the Choose a Data Source page.
• Choose Server Name > Authentication process > Database to the backup table in SQL Server.
• Choose Copy data from one or more tables or views under the Specify Table Copy or Query page > Next button.
• Highlight the Specific Tables under the Select Source Tables and Views page > Next button.
• You can opt for the Run Immediately or Save SSIS Package option as required in the Save and Run Package page.
• In the end, click on the Finish button.

It will help you to SQL table backup restore table in SQL server.

Method 3: Run BCP Commands to a Backup Table in SQL Server

• Press Win + R to open the Run window. Mention cmd to run the Command Prompt window and go with the following command > Enter button.

bcp DatabaseName.SchemaName.TableName out Filepath -c -T -S InstanceName.

• After that, write PowerShell ISE in the run window and ensure to run it as administrator.
• Mention the following commands > Execute bar.

$db = “DatabaseName”

$schema = “SchemaName”

$table = “TableName”

$path = “Filepath”

$psCommand = “bcp $($db). $($schema). $($table) out $path -T -c -S InstanceName.”

Invoke-Expression $psCommand

• Open the SQL Server Data Tools (SSDT) > New Project button.
• Choose Business Intelligence > Integration Services > Integration Services Project. Mention the Name, Location, and Solution name in the desired fields > OK button.
• Choose and drag Execute Process Task in the blank. Double-click to launch the Execute Process Task Editor window.
• Mention the path of the file bcp.exe > Executable. Then, mention the following commands > Arguments > OK button.

DatabaseName.SchemaName.TableName out Filepath -c -T -S InstanceName.

• After that, right-click on the desired task and choose Execute task.

* Note: filepath – Set a desired location where you want to save the exported data.

-c: char type

-T: uses a trusted connection

-S: Server name

We have explained to you all the manual procedures for how to take table backup in SQL server. You can check them out. If the above solutions are not working properly, it simply indicates that your MDF or NDF files are highly corrupted. If you want to repair your files, you can opt for the Sysinfo SQL Database Recovery. It will repair the maximum corruption in your files.

Time to Wind Up!

Now the time comes to conclude the entire technical blog. We have mentioned various topics in the above blog. First, we have mentioned the possible conditions to create a table backup in MS SQL Server. After that, we discussed three manual methods for the backup table in SQL server. You can understand and follow any of the procedures where you feel comfortable while accomplishing them. You can also read another blog to fix the SQLite database disk image malformed issue.

Related Post