As we have discussed in the previous post how to Improve performance of DB Backup timing by more than 50%, today we shall see how to save storage by more than 85% without any compromise on duration.
I am taking the same database which is of 30GB size – 20GB data and 10GB log file. As you know, you may find these details by using below command:
sp_helpdb db_name
Above command will provide you complete database size as well as size of data, log and few other details.
We have seen usage of stripe and its positive impact, lets use a new keyword now along with stripe to reduce the backup size and that is “Compress”. Compress option gives you multiple options and different compression levels, we shall use compression of 5 for our example and its impact.
In the previous example, when backup was taken with stripe however without any compress option, size of backup was 17GB approximately. we can check their size on the server by running below command:
ls -ltr db_name_dump_stripe*
Now, we shall run below command to take striped backup with compress:
dump database db_name to
“compress::5::/path/db_name_dump_comp_1.bak” stripe on
“compress::5::/path/db_name_dump_comp_2.bak” stripe on
“compress::5::/path/db_name_dump_comp_3.bak” stripe on
“compress::5::/path/db_name_dump_comp_4.bak”
Dump completes in 6 minutes itself and doesn’t take any extra time as you may see below:
Dec 27 13:56:35 2020: Backup Server: 4.188.1.1: Database db_name: 190906 kilobytes (1%) DUMPED.
Dec 27 13:56:37 2020: Backup Server: 4.188.1.1: Database db_name: 362946 kilobytes (2%) DUMPED.
Dec 27 13:56:52 2020: Backup Server: 4.188.1.1: Database db_name: 534986 kilobytes (3%) DUMPED.
Dec 27 13:56:54 2020: Backup Server: 4.188.1.1: Database db_name: 707026 kilobytes (3%) DUMPED.
.
.
.
Dec 27 14:01:05 2020: Backup Server: 4.188.1.1: Database db_name: 17401458 kilobytes (96%) DUMPED.
Dec 27 14:01:06 2020: Backup Server: 3.43.1.1: Dump phase number 1 completed.
Dec 27 14:01:06 2020: Backup Server: 3.43.1.1: Dump phase number 2 completed.
Dec 27 14:01:06 2020: Backup Server: 3.43.1.1: Dump phase number 3 completed.
Dec 27 14:01:06 2020: Backup Server: 4.188.1.1: Database db_name: 17444912 kilobytes (100%) DUMPED.
Dec 27 14:01:06 2020: Backup Server: 3.42.1.1: DUMP is complete (database db_name).
Now, lets check the backup size at disk by running same command:
user@servername:/path>ls -ltr dump_comp*
-rw-r—– 1 user grp 685154301 Dec 27 14:01 db_name_dump_comp_1.bak
-rw-r—– 1 user grp 685590883 Dec 27 14:01 db_name_dump_comp_2.bak
-rw-r—– 1 user grp 686320944 Dec 27 14:01 db_name_dump_comp_3.bak
-rw-r—– 1 user grp 686681011 Dec 27 14:01 db_name_dump_comp_4.bak
As you can see here, size of dump on disk is 2.55GB. Earlier dump size was 16.6GB, meaning we saved close to 85% of storage. When database is really massive in size, compress can save lot of storage and could help in saving more disk.
Note: compress option is deprecated and is currently in use only for compatibility option, I shall do another post on with compression very soon.
More posts will be there on covering dump, load, compress and stripe. Please leave your comment if you liked this post or have any feedback.