Incremental copy with SQL databases

1

Hello, I’m still doing tests with this program and I’m realizing that in the SQL database issue every day I copy the entire database (specifically, there are several databases that occupy a total of 16GB).

Obviously these databases are being used, so the database varies every day, but I thought that I would be able to copy only that part that has changed and would not make a copy of all the files.

Can it be solved in some way? It is not only the problem of the amount of time needed to perform the backup, it is also a problem at the level of storage space on the server.

Thanks!

2

IMHO you shoud first use SQL backups anyway!
Which recovery model use your SQL? etc number of questions…
SQL have great native backup possibilities, RTFM )

You can create some jobs on SQL, in increment mode,
and copy that’s increments by UrBackup, if you want.
And you must(!) also try to restore your backups (into test bases)
for verify your backup schema is right.

Urbackup have possibilities backup SQL,
but I have not test yet, still not found exact examles.

3

Hello,

The problem is that some SQL databases do not have access to them directly, because they are proprietary software and I do not have control. Apart, I do not have much idea of SQL, the truth, I do not know if I could access them and schedule a copy (I do not know how to do it either).

If the Urbackup is not able to do the incremental copies of those variations in the database, it will not work for me, I’m afraid.

Thanks

4

UrBackup can currently only efficiently store incremental differences in files when used in connection with btrfs ( see btrfs section in the manual https://www.urbackup.org/administration_manual.html#x1-11100011.7.2 ).
Other options are using a block-level deduplication solution like ZFS or Windows Server dedup.

Only file differences get transferred as soon as you switch to “block differences - hashed” transfer mode in the advanced settings.

1 Like
5

let’s see your case -
SQL is real border between databases and your proprietary software,
SA (user SA = SystemAdministrator of SQL srv)
SA of your SQL server have(!) any permissions to backup your bases, regardless of that SW.
internal SQL permissions is totally different from internal rights of that application, and OS(vary)
Moreover - SA have full control for all bases, settings, etc on that srv = God mode )

You shoud ask who is SA of your SQL, and that person can make some native backup jobs by SQL-srv
Or, SA can create sql user for you with backup rights. (regardless of that SW, again)
May be, installers of that app - installs also your SQL srv,
but they must give SA (SQL) pwd to real owner of server, or delegated person like DBA,
System Administrator (OS) of that server, etc.
IMHO, SQL-backups is right way for your case, and should be primary.

How simple your it-environment?
As I see by your questions, you shold find/get SA password,
and set sql-backups by ask some DBA to set and check(!) it
You can DIY, manuals is not black secret, google it…
but do not start your DBA-way from games on production-server! )))
seriously, do not.

as for UrB - now I’m not use it for SQL, and also learn that possibilities,
but not for main variant in production.
You should understand, that your copy’s must be consistent, and relible as steel!
and 100% restorable. SQL bases is one about most expensive data / asset of your boss,
so you must have warranty of restore, etc disaster recovery, mean it.

UrBackup is great solution, but mainly for other, wide purposes (IMHO)
like workstations state(image), user stuff etc file backups on that machines,
file shares / file servers, and also servers image backups -
full partitions with system state, settings even with databases etc
(it’s very cool to rollup to bare metal if fail, but not always goes according to plan )))

Howewer, I make my SQL-backups by native utils.
More control, more possibilities, speed, actuality on closest before-fail moment.
Too demanding task for non-specialized SQL-backup solution
For restore - I must have only SQL srv and backups, no more else point of fail or dependencies.

UrB backups is not so relible for SQL-restore as native-SQL copy - more parts - more point of fail…
slq back is single file (variants… sometimes not exact so, but simle words…)
but UrB backups is not a file… that is a complex of depencies of files spread on file system/-s,
also hardlinks, and big set of database records,
where is parts and versions and how to assemble your copy for moment, on demand…
if something goes wrong = you fired…

paranoia is no longer a disease =]
your’s, mad-admin
poor english, sorry.

6

Thanks for the answers.
I will look at and test the option of “block differences - hashed”, to see if it gives me good results and the copies are valid.

vurdalak
The problem I think I mentioned in the other message. The databases are proprietary of a management software and I do not have those keys. Besides, my level of SQL is really bad, and with old versions of SQL Server I managed to configure the backup, it was easy, but with the new versions they have complicated it and I am very afraid to touch it.

Ideally, the backup software should make reliable copies of everything, not just a part of the company’s information.

Apart, although I could do those backups with SQL, in the end I have to worry about whether the SQL backups are doing well or not, in addition to what is being done with Urbackup. I mean, I have to worry about two different copies.

Besides, I’m not sure how to do it. Do I make an incremental copy with SQL and from those files do I upload it with Urbackup? Will it work well? Because if I make a complete copy we are with the same problem: every day will upload the 16GB that occupies the complete databases.

Anyway, I do not know, that system does not convince me much.

7

Hello! SQL backups is not so terrible, learn it. Looks the same, if not very-very old…
I use MSSQL from 6.0 version, differences exist, but if you are IT, not so big.
Create test base, back it, restore it… 10 times check what you now click before,

I use UrBackup as full system-state-backup,
but also native SQL backups for base-state,
much more often than system partition image by UrB

May force be with you! ))

8

I know of, and have tested in practice, two products that do properly incremental backup of open databases saving only the changed parts of database files:

  1. Duplicati (use VSS)
  2. Ferro backup (use own driver for reading blocked files like VSS)

However, I recommend running them at night when the databases are unused because of the buffers in RAM that MSSQL server uses.

Correct procedure of backup running databases is using .sql scripts for full, and differential backup.
Here you have scripts for do this for many databases with auto generating scripts for automatic restore them from backup:

9

I am sure a database dump will have much less then 16 GB!
incremental copies means files while incremental blocks means deduplication.
Deduplication on filesystem basis is ZFS or BTRFS
Deduplication on Application level is Duplicati org Borg

Deduplication on our mailserver (1,8 TB Mails) with many many copies of same files:

root@mail:~# borg info /srv/backup/repo/
Repository ID: XXXXXXX
Location: /srv/backup/repo
Encrypted: No
Cache: /root/.cache/borg/XXXXXXX
Security dir: /root/.config/borg/security/XXXXXX

    Original size Compressed size Deduplicated size

All archives: ____347.30 TB ____320.69 TB_____1.19 TB

Have to keep all mails for 10 years for the gov! :frowning:
Thanks to borg no headache! :slight_smile:

10

Hi!
IMHO Backup method of sql databases allways should be based on the solution, provided by the database vendor, if you want ever, to restore it.
In most databases, the file based backup is consistent only, if the db engine is shut down during the backup, snapshotting can help, but it also depends on the disk layout, where, and how the database components installed, and timing of the snapshotting (I won’t recommend it).
If your database supports restore, based on changelogs (oracle archive log, postgresql wal, mysql binlog, etc) these logs are suitable for incemental backup too, and based on these files and a baseline backup, it can solve, the basic problem.
16 GB is not a big db. If making a full consistent backup( sqldump, dbbackup, rman, etc) takes just minutes, there is no simlier, and more reliable way, to do backup on databases.
The sql backup features in urbackup, are great, I am using it on different types of sql server, sometimes trhu pre backup script, sometimes directly.
Regards,
PAtt