Table backup in SQL Server

Spread the love


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.


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.


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 !!

Spread the love
Author: OTi
Meet OTi, the brains behind the popular tech blog "" OTi is an accomplished author, tech enthusiast, and an expert in SQL Server. With over a decade of experience in the tech industry, he has become a trusted voice in the world of database management. OTi's passion for technology began at a young age, and he has been tinkering with computers ever since. He graduated with a degree in Computer Science and has since worked for some of the biggest tech companies in the world. His experience has given him a deep understanding of how technology works and how it can be used to improve our lives.

Leave a Reply

Your email address will not be published. Required fields are marked *