Category: Database

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 −

BACKUPDATABASE database_name
TODISK='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 −

BACKUPDATABASE 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 −

BACKUPDATABASE 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 −

RESTOREDATABASE database_name
FROMDISK='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 −

BACKUPDATABASE database_name
TODISK='filepath'
GO

Example

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

SQL>BACKUPDATABASE testDB
TODISK='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 −

RESTOREDATABASE database_name
FROMDISK='filepath'[WITHREPLACE]
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>RESTOREDATABASE testDB
FROMDISK='D:\testDB.bak'WITHREPLACE
GO

Many a times you face a situation where you need to list down all the available databases. MySQL provides a convenient way to list down all the databases using SHOW DATABASES command where as there is no particular command in MS SQL Server to show or list the databases but, you can use the SELECT…FROM command as a work around list down available databases.

List Databases using SQL

The SQL SHOW DATABASES statement is used to list down all the available databases in MySQL database. You can use SHOW SCHEMAS as an alternate command for SHOW DATABASES.

Syntax

Following is the syntax of SQL SHOW DATABASES to list down all the available databases in MySQL −

SHOWDATABASES[LIKE'pattern'|WHERE expr];

We can use LIKE or WHERE clause along with SHOW DATABASES to filter out a list of databases.

Example

Following is an example to list down all the available databases.

SHOWDATABASES;

The output will be displayed as follows. This output depends on the number of databases available in the system −

Database
performance_schema
information_schema
mysql
testDB

Following is an example to list down all the databases whose name starts with test.

SHOWDATABASESLIKE'test%';

The output will be displayed as follows −

Database (test%)
testDB

The SHOW SCHEMAS Statement

You can use the SHOW SCHEMAS statement as an alternate for the SHOW DATABASES statement.

Syntax

Following is the syntax of the SQL SHOW SCHEMAS statement to list down all the available databases in MySQL −

SHOW SCHEMAS [LIKE'pattern'|WHERE expr];

We can use LIKE or WHERE clause along with SHOW SCHEMAS to filter out a list of databases.

Example

Following is an example to list down all the available databases.

SHOW SCHEMAS;

The output will be displayed as follows. This output depends on the number of databases available in the system −

Database
performance_schema
information_schema
mysql
testDB

Following is an example to list down all the databases whose name starts with test.

SHOW SCHEMAS LIKE'test%';

The output will be displayed as follows −

Database (test%)
testDB

The SELECT…FROM Statement

If you are working with MS SQL Server then you can use the SELECT…FROM statement to list down all the available databases as shown below.

SQL>SELECT*FROM sys.databases;

Output

If we execute the above query, it returns a table that lists down all the databases and associated information about the databases.

name	database_id	source_database_id	owner_sid
master	1	NULL	001
tempdb	2	NULL	001
model	3	NULL	001
msdb	4	NULL	001
testDB	5	NULL	001000

The EXEC sp_databases Statement

If you are using MS SQL Server then you can use the following EXEC sp_databases statement to list down all the databases −

SQL>EXEC sp_databases;

Output

This will display the same result as we got from SELECT…FROM statement.

name	database_id	source_database_id	owner_sid
master	1	NULL	001
tempdb	2	NULL	001
model	3	NULL	001
msdb	4	NULL	001
testDB	5	NULL	001000

There can be several reasons to rename a database name. One of the reasons could be to avoid naming conflicts or to separate different types of data into different databases. Another reason can be to arrange them in an organized way which makes them more descriptive and easier to manage.

The ALTER DATABASE…MODIFY Statement

The ALTER DATABASE…MODIFY statement in SQL is used to rename the name of an existing database name in SQL Database Server. Please note that this command does not work in MySQL database.

Syntax

Following is the syntax of the ALTER DATABASE…MODIFY command −

ALTERDATABASE OldDatabaseName MODIFY NAME = NewDatabaseName;

Example

Following is the SQL command in SQL Server to rename the database testDB to tutorialsDB:

ALTERDATABASE testDB MODIFY NAME = tutorialsDB ;

Rename Database using Dump and Reimport

If you are willing to rename a database name in MySQL, then simple way is to dump the complete database in an SQL file and then re-import it into a new database. This is three step process which we will follow in this tutorial:

Step 1 – Dump Old Database

Consider you want to rename testDB database to tutorialsDB. So first we will dump it in a simple SQL file using MySQL mysqldump command. This operation will be performed from command line and will require a 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.

Step 2 – Create New Database

Next step is to create new database using mysqladmin prompt command as follows:

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

Step 3 – Import Old Database

The final step is to import old database into new database as follwing:

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

Step 4 – Verification (Optional)

Now you can verify the changes by listing down all the available databases:

SHOWDATABASES;

Output

The output will be displayed as −

Database
performance_schema
information_schema
mysql
testDB
tutorialsDB

Step 5 – Verification (Optional)

Once you are satisfied with your changes, you can delete your old database as follows:

DROPDATABASE testDB;

Rename Database in SQL using RENAME DATABASE…TO (obsoleted)

SQL provides a simple RENAME DATABASE…TO statement to rename an existing database. If you want to rename a database, make sure there is no active transaction in progress otherwise the complete operation might halt once you rename the database.

Note: The RENAME DATABASE…TO is obsoleted.

Syntax

Following is the syntax of the RENAME DATABASE…TO statement −

RENAMEDATABASE OldDatabaseName TO NewDatabaseName;

Example

Before renaming a database, let us list down all the available databases −

SHOWDATABASES;

The output will be displayed as −

Database
performance_schema
information_schema
mysql
testDB

Now, issue the following command to rename the database testDB to tutorialsDB:

RENAMEDATABASE testDB TO tutorialsDB;

There used to be a simple RENAME DATABASE command in older versions of MySQL which was intended to rename database but RENAME DATABASE command has been removed from all newer versions to avoid security risks.

To work with a database in SQL, we need to first select the database we want to work with. After selecting the database, we can perform various operations on it such as creating tables, inserting data, updating data, and deleting data.

The USE DATABASE Statement

The SQL USE DATABASE statement is used to select a database from a list of databases available in the system. Once a database is selected, we can perform various operations on it such as creating tables, inserting data, updating data, and deleting data.

Syntax

Following is the syntax of the USE DATABASE statement in SQL −

USE DatabaseName;

Here, the DatabaseName is the name of the database that we want to select. The database name is always unique within the RDBMS.

Example

First of all we will create a database using the following SQL CREATE DATABASE query −

CREATEDATABASE testDB;

Now, we can list all the available databases as follws −

SHOWDATABASES;

The output will be displayed as −

Database
master
performance_schema
information_schema
mysql
testDB

Example: Select/Switch Database

Following query is used to select/switch the current database to testDB −

USE testDB;

Output

Database changed

Once we finish switching to the database testDB we can perform operations such as creating a table, and inserting data in that table as shown below −.

CREATETABLE CALENDAR(MONTHS DATENOTNULL);

Now, let us insert some records in the CALENDAR table using SQL INSERT statements as shown in the query below −

INSERTINTO CALENDAR(MONTHS)VALUES('2023-01-01');INSERTINTO CALENDAR(MONTHS)VALUES('2023-02-01');INSERTINTO CALENDAR(MONTHS)VALUES('2023-03-01');INSERTINTO CALENDAR(MONTHS)VALUES('2023-04-01');INSERTINTO CALENDAR(MONTHS)VALUES('2023-12-01');

Let’s verify the operation by listing all the records from CALENDAR table using SQL SELECT statement as shown below −

SELECT*FROM CALENDAR;

Output

The output will be displayed as −

MONTHS
2023-01-01
2023-02-01
2023-03-01
2023-04-01
2023-12-01

Selecting a Non Existing Database

An attempt to select a non-existent database will result in an error. In the following query we are trying to switch to the database which does not exist −

Example

USE unknownDatabase;

Output

On executing the above query, the output will be displayed as

ERROR 1049 (42000): Unknown database 'unknownDatabase'

The SQL DROP DATABASE statement is used to delete an existing database along with all the data such as tables, views, indexes, stored procedures, and constraints.

SQL DROP Database Statement

Following are the important points to remember before you delete an existing database −

• Make sure you have taken proper backup of the database before you delete it.
• Make sure no other application is connected and using this database.
• Make sure you have the necessary privilege to delete the database. Usually an admin can delete the databaase.

Syntax

Following is the syntax to delete a database in SQL −

DROPDATABASE DatabaseName;

Here, the DatabaseName is the name of the database that you want to delete. A database name is always unique within the RDBMS.

Example

First of all, let us create multiple databases into database system using the following SQL queries −

CREATEDATABASE testDB1;CREATEDATABASE testDB2;CREATEDATABASE testDB3;CREATEDATABASE testDB4;

Let us verify whether the databases are created or not using the following query −

SHOWDATABASES;

This will list down all the available databases:

Database
information_schema
mysql
performance_schema
testDB1
testDB2
testDB3
testDB4

Now, let us try to delete the testDB1 database using the SQL DROP DATABASE statement −

DROPDATABASE testDB1;

Once we have deleted the testDB1 database, we can verify whether it is deleted or not using the SQL SHOW DATABASES statement −

SHOWDATABASES;

This will list down all the available databases:

Database
information_schema
mysql
performance_schema
testDB2
testDB3
testDB4

That’s it! we have successfully deleted a database in SQL.

SQL DROP DATABASE IF EXISTS Statement

The SQL DROP DATABASE IF EXISTS statement includes a condition to check whether the database exists before trying to delete it. If the database does not exist in the database system, the “DROP DATABASE IF EXISTS” statement does not raise an error, but it simply terminates without taking any action.

Syntax

Following is the syntax of the DROP DATABASE IF EXISTS statement in SQL −

DROPDATABASEIFEXISTS DatabaseName;

Here, the DatabaseName is the name of the database that you want to delete.

Example

Let us try to delete an existing database testDB2 in the database system using the following SQL statement −

DROPDATABASEIFEXISTS testDB2;

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

Query OK, 0 rows affected, 3 warnings (0.024 sec)

Dropping the Database that doesn’t Exist

Let us try to drop a database testDB2 that doesn’t exist in the database system using the following SQL statement −

DROPDATABASEIFEXISTS testDB2;

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

Query OK, 0 rows affected, 1 warning (0.000 sec)

Deleting Multiple Databases

You can drop multiple databases using the SQL DROP DATABASE statement as follows:

DROPDATABASE testDB3, testDB4;

A database is a structured collection of data that is stored in a computer system. They are used to store and retrieve the data efficiently. Databases can be created using different query languages, and SQL is one such language.

CREATE Database Statement

The CREATE DATABASE statement is a DDL (Data Definition Language) statement used to create a new database in SQL. If you are creating your database on Linux or Unix, then database names are case-sensitive, even though SQL keywords are case-insensitive. If you are working on Windows then this restriction does not apply.

Syntax

Following is the syntax to create a database in SQL −

CREATEDATABASE DatabaseName;

Here, the DatabaseName is the name of the database that we want to create. The database name can contain any valid identifiers, such as number, letters, or underscores. But a DatabaseName cannot be a keyword available in SQL.

While creating a database, you may encounter an error such as ERROR 1044 (42000): Access denied for user ‘krishna’@’localhost’ to database ‘DatabaseName’, this means that you do not have the necessary privileges to create a database. To create a database, you need to have admin previleges.

Example

Following is an example to create a database testDB using SQL CREATE DATABASE statement −

CREATEDATABASE testDB;

List Databases using SQL

Once the database testDB is created, you can check it in the list of databases using SQL command SHOW DATABASES;.

Syntax

SHOWDATABASES;

Output

The output will be displayed as −

Database
master
performance_schema
information_schema
mysql
testDB

Use/Select Databases using SQL

We can now set the testDB as the default database by using the USE statement in SQL.

Syntax

USE testDB;

That’s it! we have successfully created a database in SQL. Now, we can create tables and other database objects within this new database.