BACKUP

Example

BACKUP DATABASE YourDatabase
TO DISK = '/path/to/your/directory/YourDatabase.bak'

Output

BACKUP DATABASE successfully processed 0 pages in 120.036 seconds (0.000 MB/sec)

Explanation

In the above example code, the SQL BACKUP DATABASE statement is used to create a full backup of YourDatabase to a specified disk location. In this case, the path for backup is /path/to/your/directory/YourDatabase.bak.

The output statement signifies that the backup process was successful and displays the time it took to complete the backup along with the transfer rate (in MB/sec).

Please note this syntax can vary depending upon the SQL distribution you’re using. This example is based on Microsoft SQL Server. MySQL does not support the BACKUP DATABASE command. You may have to use mysqldump or similar tools.

Example

BACKUP DATABASE testDB
TO '/var/lib/postgresql/testDB.bak';

Output

BACKUP DATABASE successfully processed 198 pages in 0.018 seconds (85.754 MB/sec).

Explanation

In the provided example, the PostgreSQL database ‘testDB’ is being backed up. The BACKUP DATABASE command is used to create a full backup of the database. The backup file ‘testDB.bak’ is saved in the directory ‘/var/lib/postgresql/’. The output message displays the success of the operation, how many pages were processed, and the operation’s speed.

Example

BACKUP DATABASE TestDB
TO DISK = 'C:\backups\TestDB.bak'
WITH FORMAT, MEDIANAME = 'TestDBBackup', NAME = 'Full Backup of TestDB';

Output

Processed 312 pages for database 'TestDB', file 'TestDB' on file 1.
Processed 3 pages for database 'TestDB', file 'TestDB_log' on file 1.
BACKUP DATABASE successfully processed 315 pages in 0.738 seconds (3.324 MB/sec).

Explanation

The BACKUP DATABASE command is used to create a full back up of the TestDB database. The TO DISK clause specifies the file path where the backup is to be stored. The WITH FORMAT argument specifies that a new backup file should be created, overwriting any existing file. The MEDIANAME and NAME options are used to specify the media name and the logical name for the backup. The Output shows the successful execution of the backup command.

Example

RUN {
  ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
  BACKUP DATABASE FORMAT '/tmp/backup_%U';
}

Output

channel disk1: starting full datafile backup set
channel disk1: specifying datafile(s) in backup set
including current control file in backup set
channel disk1: starting piece 1 at 29-Jan-21
channel disk1: finished piece 1 at 29-Jan-21
piece handle=/tmp/backup_05aa8e47_1_1 tag=TAG20210129T153434 comment=NONE
channel disk1: backup set complete, elapsed time: 00:04:14
Finished backup at 29-Jan-21

Explanation

The BACKUP DATABASE command in Oracle is used to create a backup of the database. The FORMAT specification is used to specify the filename format for the backup pieces. The ‘%U’ in the filename is a uniquely generated filename that ensures the filename does not clash with existing filenames, and the entire backup is placed in ‘/tmp’ directory.