In this SQL Backup Database tutorial, we will explain how we can take a backup of a database in MySQL and MS SQL Server. It is very important and basic development practice to have a backup of the database in case the original is corrupted or lost due to power surges or disk crashes etc. By practicing this, the database can be recovered as it was before the failure.

SQL Backup Database Statement

In SQL, the BACKUP DATABASE statement is used to create database backups.

Syntax

Following is the syntax of SQL Backup Database Statement −

BACKUP DATABASE database_name
TO DISK = ''filepath''
GO

Here, the SQL command creates a backup file of the database_name database.

Types of Backups in SQL

In SQL, there are three types of database backups available. These are:

• Full Backup
• Differential Backup
• Transaction Log (T-log) backup

SQL Full Backup

A full backup is a complete backup of an SQL server database.

Syntax

Following is the syntax of SQL Full Backup −

BACKUP DATABASE database_name
TO medium = ''filepath''
GO

Here, database_name is the name of the database, medium refers to the storage medium such as disk, tape or url.

SQL Differential Backup

In Sql, you can also backup only the new changes by using the WITH DIFFERENTIAL command.

Syntax

Following is the syntax of Sql Differential Backup −

BACKUP DATABASE my_db
TO medium = ''filepath''
WITH DIFFERENTIAL;
GO

Here, database_name is the name of the database, medium refers to storage device such as disk, tape or url.

Transaction Log (T-log) backup

A transaction log backup includes all the transactions since the last transaction log backup. BACKUP LOG comnmand is used to perfom the Transaction Log backup.

Syntax

Following is the syntax of Sql transaction log backup −

BACKUP LOG database_name
TO medium = ''filepath'';
GO

Here, database_name is the name of the database, medium refers to storage device such as disk

Restore Database From Backup

To restore a backup file in Database, we can use the RESTORE DATABASE command.

Syntax

Following is the syntax of Restore Database From Backup −

RESTORE DATABASE database_name
FROM DISK = ''filepath'';
GO

Here, database_name is the name of the database, medium refers to disk, tape or url.

MySQL and MS SQL Database Backup and Restore

Here is the process to create backup in MySQL and MS Sql databases.

Backup MySQL Database

MySQL mysqldump command can be used to take complete backup of a given database. This operation will be performed from command line and will require database user name and password, preferably admin privilege.

$ mysqldump -u username -p"password" -R testDB > testDB.sql

We are using the -p flag immediately followed by our password to connect to the database with no space between. The -R is required to tell mysqldump to copy stored procedures and functions along with the normal data from the database.

Depending on the database size, above command may take sometime to create a final output file testDB.sql. Once command is completed, you will have a complete database dump in testDB.sql file which you can keep safe anywhere you like. Later this file can be used to restore the database.

Restore MySQL Database

If we have a database dump then we can use the following two step process to restore our database. First step is to create our new database using mysqladmin prompt command as follows:

$ mysqladmin -u username -p"password" create tutorialsDB;

The next step is to import old database into new database shown below :

$ mysql -u username -p"password" tutorialsDB < testDB.sql;

If you want to keep your database name same as the old one then you will have to drop old database and then re-create it before importing old data into this database, but make sure you don”t have any data in this database which you do not want to loose.

Backup MS SQL Database

If you are working with MS SQL Server then to create a backup for an existing database, SQL provides us with a simple SQL BACKUP DATABASE command.

Syntax

Following is the syntax of the BACKUP DATABASE command in SQL −

BACKUP DATABASE database_name
TO DISK = ''filepath''
GO

Example

Following is an example to create a backup file for the database testDB on D drive.

SQL> BACKUP DATABASE testDB
TO DISK = ''D:testDB.bak''
GO

To perform a backup or restore you should have admin sysadmin privileges. You should also back up the database onto a different disk other than the actual database. Even if the disk crashes, we will not lose our backup file along with the database.

Output

When we execute the above query, the output is obtained as follows −

Processed 344 pages for database ''testDB'', file ''testDB'' on file 1.
Processed 2 pages for database ''testDB'', file ''testDB_log'' on file 1.
BACKUP DATABASE successfully processed 346 pages in 0.011 seconds (245.383 MB/sec).

Restore MS SQL Database

If you have a proper backup of an MS SQL database then youc an easily restore it when needed.

Syntax

Following is the syntax of the RESTORE DATABASE command in SQL −

RESTORE DATABASE database_name
FROM DISK = ''filepath''
[WITH REPLACE]
GO

Here WITH REPLACE option can be given if you want to overwrite the existing database.

Example

Following is an example to restore a database from a backup file testDB.bak available on D drive.

SQL> RESTORE DATABASE testDB
FROM DISK = ''D:testDB.bak''
WITH REPLACE
GO