Backup of database is very crucial for a DBA and it is mostly a daily job. Database backup is used for loading on other environments and also in case of failure of the primary server. There are multiple types of backup and they can be used together to achieve point in time recovery, which will be taken in a later post.
Normally we take backup in non-working hour or at a time when there is relatively lesser load on the server as it is a disk intensive process and also required CPU core(s) to be utilized, more details on the core part will be taken soon.
Now, lets look at the normal backup command and see the performance of it. Here I am taking a database of 30720.0 MB size(30GB) – data file of 20GB and log file of 10GB. You may get details of database size by using below command:
This command will return result in below grid:
sp_helpdb db_name
| name | db_size | owner | dbid | created | durability | lobcomplvl | inrowlen | status |
db_size will give you DB size and other details as the header suggests.
Now, lets run dump of database using below command:
dump database db_name to “/path/db_name_dump_nostripe.bak”
When we issue this command, dump starts and you could see a new file named db_name_dump_nostripe.bak is created at the path you have specified. Backup starts and you can check the entry in backup log as below:
Dec 27 09:32:52 2020: Backup Server: 4.188.1.1: Database db_name: 182582 kilobytes (1%) DUMPED.
Dec 27 09:32:53 2020: Backup Server: 4.188.1.1: Database db_name: 354630 kilobytes (2%) DUMPED.
Dec 27 09:32:54 2020: Backup Server: 4.188.1.1: Database db_name: 526678 kilobytes (2%) DUMPED.
Dec 27 09:32:54 2020: Backup Server: 4.188.1.1: Database db_name: 698726 kilobytes (3%) DUMPED.
.
.
.
Dec 27 09:44:57 2020: Backup Server: 4.188.1.1: Database db_name: 17399574 kilobytes (85%) DUMPED.
Dec 27 09:45:04 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 27 09:45:04 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 27 09:45:04 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 27 09:45:04 2020: Backup Server: 4.188.1.1: Database db_name: 17445698 kilobytes (100%) DUMPED.
Dec 27 09:45:04 2020: Backup Server: 3.42.1.1: DUMP is complete (database db_name).
As you can see from the above window, dump took approximately 13 minutes to complete and backup size is close to 17GB.
Now, lets use another command to improve the performance of this backup, we shall do that by using stripe command. Please note that, striped backup will use more number of cores than the previous command without stripe. Command for taking striped backup will be as below:
dump database db_name to
“/path/db_name_dump_stripe_1.bak” stripe on
“/path/db_name_dump_stripe_2.bak” stripe on
“/path/db_name_dump_stripe_3.bak” stripe on
“/path/db_name_dump_stripe_4.bak”
In earlier case, we had only one file as a backup whereas in this case there will be be 4 files created at the same time and will be also be written into simultaneously. Entry in backup log will be as below:
Dec 27 09:52:31 2020: Backup Server: 4.188.1.1: Database db_name: 190904 kilobytes (1%) DUMPED.
Dec 27 09:52:45 2020: Backup Server: 4.188.1.1: Database db_name: 362944 kilobytes (2%) DUMPED.
Dec 27 09:52:46 2020: Backup Server: 4.188.1.1: Database db_name: 534984 kilobytes (3%) DUMPED.
Dec 27 09:52:47 2020: Backup Server: 4.188.1.1: Database db_name: 707024 kilobytes (3%) DUMPED.
.
.
.
Dec 27 09:58:49 2020: Backup Server: 4.188.1.1: Database db_name: 17250350 kilobytes (97%) DUMPED.
Dec 27 09:58:49 2020: Backup Server: 4.188.1.1: Database db_name: 17415774 kilobytes (97%) DUMPED.
Dec 27 09:58:50 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 27 09:58:50 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 27 09:58:50 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 27 09:58:50 2020: Backup Server: 4.188.1.1: Database db_name: 17444912 kilobytes (100%) DUMPED.
Dec 27 09:58:50 2020: Backup Server: 3.42.1.1: DUMP is complete (database db_name).
Backup size remains same i.e. 17GB however check the duration, it finished in 6 minutes only. so, this is improvement of backup time by more than 50%.
In case of voluminous database and more number of cores are available, you can increase the number of stripe and can reduce backup time multi-fold.
More details on backup and loading in upcoming posts. If you have any query, comments, please feel free to do so.