Skip to main content
Our Tech Ideas

Table backup in SQL Server


Here we will discuss different backup strategies and methodologies used to take the table backup in SQL Server with real-life examples. There are different methods of taking backup in SQL server.

Type 1: Taking Backup of the table using Into Clause

The T-SQL engine uses INTO clause to copy the data from one table to another table.


Select * INTO BackUP_TABLE_Name
from Original_TABLE_Name;

With this strategy, the user can create a backup table directly without using create statement. The backup table is created with a table that needs to be backup. This strategy is useful when the user needs the table backup on the same server within the same database.This methodology is used to take a fast backup.

Real-life Example:

If the user wants to take a backup of ‘Person.Address’ table and did some operations on same table then the following query is useful.


USE Our_Tech_Ideas
Select * INTO Person.Address_Backup 
from Person.Address;

The above statement will create a backup of ‘Person.Address’ table and creates a new table named ‘Person.Address_Backup’.


Type 2: Using generate Script option in Microsoft SQL

This type of option is the most used option in Microsoft SQL to take a backup from one server and restore it to another server. The following steps are used to take a backup of the table in the SQL server.

Step 1: Right-click on the database and choose Tasks –> Generate Scripts.

Click Next

Step 2: Select the database from which you need to take a backup of the table.

Step 3: You will see the Table/View options on the screen while scrolling down. Select the table in which you want to back up and hit the next button.

Step 4: Click on advance

Step 5: Select ‘Types of data to script’ as ‘Schema and data’ & then click OK

Step 6: Type the backup table name, by default extension, will be’.sql’. Then click ‘Save’ > then ‘Next’

Step 7: Review the summary

Step 8: Click ‘Next’, scripting will start.

Backup done !!