Table backup in SQL Server

Introduction

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.

Syntax:

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.

Query

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

Result

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

Facebook Comments Box
Please like or share this post if you found it helpful, thank you.
Share on Facebook
Facebook
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Email this to someone
email
Print this page
Print

Comments

Leave a Reply

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