How to backup or create new table from Existing SQL Server Table in SQL Server - SQL Server / TSQL Tutorial Part 105

Scenario:

You are working as SQL Server developer, you need to provide some update or delete scripts to update or delete data from a table.You want to take the backup of those records or if the table is small you might want to backup entire table before you run update or delete statements.

How would you backup entire table or only the records on which you need to run update or delete statements?

Solution:

SQL Server does not provide Table level backup. When we say that we want to take the backup of the table, we are talking about making a copy of existing table with records.

Let's say if we have dbo.Customer Table with few records and we want to create backup table dbo.Customber_Bkp_TodayDate, we can use below script

First create dbo.Customer table with sample records

USE yourDatabaseName
Go
Create Table dbo.Customer(
Id int identity(1,1),
FName VARCHAR(50),
LName VARCHAR(50),
Age int,
DOB Date,
Salary Numeric(6,2))

--Use the Insert Into with Values single Statement
Insert into dbo.Customer
Values('Aamir','Shahzad',36,'1980-01-01',5000.50),
('Raza','M',33,'1983-03-03',4900.34),
('John','Smith',26,'1990-04-05',5000.50)

Now let's create dbo.Customber_Bkp_TodayDate backup table with all the records which are present in dbo.Customer.

Select * into dbo.Customber_Bkp_20160507 from dbo.Customer

To create new table with records, you have to use Into NewTable from OldTable as shown above.

If we are only interested to copy records where FName='Aamir' our query will be like below.

Select * into dbo.Customber_Bkp_20160507_OnlyAamir from dbo.Customer where FName='Aamir'

Run above scripts and check the tables if created with required records.

How to backup records into new table from existing SQL Server Table in SQL Server

Scripts used in the video demo:

--Take the backup or create table for all records
Select * into [YourDBName].dbo.Customer_20160510  from [dbo].[Customer]

--Create table for selected records
Select * into [YourDBName].dbo.Customer_20160510_TwoRecords from [dbo].[Customer]
where id<=2

--Check if table is created successfully
Select * From [YourDBName].dbo.Customer_20160510_TwoRecords

Select * from [dbo].[Customer]

--Update Records in current table
update [dbo].[Customer]
set LName=LName+' Test'
where id<=2


--Update records in current table from backup table
update d 
set d.LName=s.LName
from [YourDBName].dbo.Customer_20160510_TwoRecords s
inner join [dbo].[Customer] d
on s.id=d.id

Video Demo : How to create new table from existing table with data in SQL Server quickly