SQL

SQL Databases

SQL Databases

This page covers the implementation of SQL databases.

Before any processing or manipulation of data, we need a database. In other words, a place to store the information.

The syntax for managing databases is very straightforward. The functions within this tutorial are:

  • create databases
  • show databases
  • drop databases
  • backup databases

Create databases

SQL uses the keywords “create database” to create new a database.

The following is an example of creating a database under the name of “students”.

CREATE DATABASE students;

# Output: Commands completed successfully.

If no errors, the execution produces a message “commands completed successfully”, alongside other information regarding the specific statement.

Show databases

The syntax for showing databases is also very easy.

The following is an example of showing all available databases.

SELECT * FROM sys.databases;

If execution is successful, the program produces a list of databases. Further tutorials cover the “SELECT FROM” statement.

The usage of asterisk (star *) sign in most programming languages refers to “all elements”. As such, the above statement outputs all databases.

Drop databases

To drop databases, we can use the drop statement. The keywords are “drop databases”.

The following is an example of dropping the database “students”.

DROP DATABASE students;

# Output: Commands completed successfully.

If successfully, the message “commands completed successfully” appears.

Please be careful when using the drop statement in SQL. Dropping a database deletes all the data as well.

Backup databases

Backup of databases is an important concept. A backup is a great mitigation method in scenarios of unexpected incidents, in which vital data can be damaged lost.

For backup, SQL uses the keywords “backup database to disk”. Please note that in computing, the extension “.bak” refers to a backup copy file.

Please note that it is important for backups to be stored in different locations of the original databases. For instance, save the copies in different disks, servers, or any other storage facility. This prevents damage or loss of data in cases of storage crashes.

Below is an example of backup.

BACKUP DATABASE students
TO DISK = 'C:\SQL_backups\students_copy.bak';

With differential

SQL has the addition of “with differential”. This backup method copies only the new changes from the previous save.

As such, this methodology speeds up the backup process due to the reason of saving only the changes (and not the whole database).

The following is an example of a “with differential” backup.

BACKUP DATABASE students
TO DISK = 'C:\SQL_backups\students_copy.bak'
WITH DIFFERENTIAL;

Next: SQL Tables

Related Posts