How to take a backup of table in SQL? :
In my previous articles i have given idea about the different tutorials from SQL. In this article i would like to give you idea about how to take a backup of table in SQL? User faces issues in taking the backup of the table. There are so many methodologies to take a backup in SQL. The main question raised here is How to take a backup of table in SQL?I will try to explain all important methods for taking the backup table in SQL.There are some most important methods in t-SQL, oracle ,PostgreSQL and MySQL for How to take a backup of table in SQL? I will explain real life industry examples with the syntax for taking backup.
How to take backup table in SQL ? -For SQL Server
In this section i would like to explain the different backup strategies and methodologies used in SQL Server with real life examples.There are different methods of taking backup in SQL server.
Type 1 : Taking Backup of table using Into Clause
The T-SQL engine uses INTO clause to copy the data from one table to another table.
Syntax :
Select * INTO BackUP_TABLE
from Table_TO_BE_BACKUP;
With this strategy user can create backup table directly without using create statement. The backup table is created with the table needs to be backup. This strategy is useful when user needs the table backup on same server.This methodology is used to take a fast backup but the main con of this method is it can not take the indexes or keys.
Real life Example :
If user wants to take a backup of Employee table and did some operations on Employee table then following query is useful.
Query:
Select * INTO Employee_Backup
from Employee;
The above statement will create a backup of Employee table and creates a new table named Employee_Backup.
Type 2 : Taking Backup of table using Bulk Copy Program
The second method of taking backup of the table using Bulk Copy Program in SQL server. There should be some conditions to take a backup of table using bulk copy.The main condition is you must have Bulk Import and export privileges.
Using BCP utility:
User can take a backup of table using BCP utility.
If user want to take a backup of Employees table which resides in Company database following command is useful.
bcp Company.Employees out
C:\MSSQL\Backup\Company.Employees_20180222.dat -n -T
In above command following are the different indications
-n : -n stands for the native sql datatypes which is key during the restore.
-T : -T denotes that you are connecting to SQL server using Windows authentication. If user wants to connect with microsoft server authentication kindly use -U option or -P option.
Using BCP utility Program :
User can take the backup of the table using standard program as well.
— SQL Table Backup
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = ‘Company.Employees’ — Table Name which you want to backup
SET @file = ‘C:\MSSQL\Backup\’ + @table + ‘_’ + CONVERT(CHAR(8), GETDATE(), 112) — Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup
+ ‘.dat’
SET @cmd = ‘bcp ‘ + @table + ‘ out ‘ + @file + ‘ -n -T ‘
EXEC master..xp_cmdshell @cmd
I have already explained the options -n and -T in above section.
Type 3 : Using generate Script option in Microsoft SQL
This type of option is most used option in Microsoft SQL to take a backup from one server and restore it to another server.Following steps are used to take a backup of table in SQL server.
Step 1 : Right click on the database and choose Tasks –> Generate Scripts.
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 which you want to back up and Hit next button.
The above option is used to create .bak file from the database.
How to take backup table in SQL ? -For Oracle
In this section I will explain different ways to take a backup of table in SQL for Oracle database management system. There are multiple ways to take a backup of table for oracle database.
Using Select Command :
User can take a backup of table with the data in oracle or without a data. The simple create table command is used to take a backup of table in oracle. Using import and export strategies that table will be restored from one server to another server.
Syntax :
Create table Backup_Table as select * from Table_To_be_Backup;
Example :
If user wants to take backup of Employee table kindly use following command
Create table Employee_bak as select * from Employee;
This will create the backup table with data. There are some requirements where user needs to take backup of table without data. Means user needs to create a skeleton of table.
Syntax :
Create table Backup_Table as select * from Table_To_be_Backup where 1 = 2;
Example :
If user wants to take backup of Employee table without data then kindly use following command
Create table Employee_bak as select * from Employee where 1=2;
Using Metadata Information :
The second method is selecting the metadata information from the system table. The following command is useful to take backup of metadata.
SELECT dbms_metadata.get_ddl( ‘TABLE’, ‘EMPLOYEE’, ‘OTHER_SCHEMA_NAME’ ) FROM DUAL;
With using above query you will get DDL scripts to create the Employee table. User needs to rename the table name in the scripts.
How to take a backup of table in SQL ? -For MySQL and PostgreSQL
In this section i will explain different ways to take a backup of table in SQL for MySQL and PostgreSQL database management systems using select command.
Using Select Command :
User can take a backup of table with the data in MySQL and PostgreSQL or without a data. The simple create table command is used to take a backup of table in MySQL and PostgreSQL. Using import and export strategies that table will be restored from one server to another server.
Syntax :
Create table Backup_Table as select * from Table_To_be_Backup;
Example :
If user wants to take backup of Employee table kindly use following command
Create table Employee_bak as select * from Employee;
This will create the backup table with data. There are some requirements where user needs to take backup of table without data. Means user needs to create a skeleton of table.
Syntax :
Create table Backup_Table as select * from Table_To_be_Backup where 1 = 2;
Example :
If user wants to take backup of Employee table without data then kindly use following command
Create table Employee_bak as select * from Employee where 1=2;
These are some most important strategies to take backup in Oracle,SQL and PostgreSQL. If You like this article on How to take a backup of table in SQL? or if you have any questions with How to take a backup of table in SQL? article kindly comment it in to comment section.