Self hosted: MSSQL nightly backup isn't create for non-default DB name

[baurmatt]

Steps To Reproduce

1. Install Bitwarden with the help of bitwarden.sh/run.sh.
2. Choose a non default database name. e.g. bitwarden.
3. Wait 24h for the nightly backup/

Expected Result

Backup is created every ~24h.

Actual Result

No backup is created

Screenshots or Videos

$ ls -la /opt/bitwarden/bwdata/mssql/backups/
total 8
drwxrwxr-x 2 bitwarden bitwarden 4096 Jan 14 09:34 .
drwxrwxr-x 4 bitwarden bitwarden 4096 Jan 14 09:34 ..

(This is the state of the MSSQL backups directory after a full weekend where it was running.)

Additional Context

The problem seems to be that the database name is still hardcode:

• server/util/MsSql/backup-db.sh

  Line 17 in 56ee3bd

  /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SA_PASSWORD} -i /backup-db.sql

• server/util/MsSql/backup-db.sql

  Line 3 in 56ee3bd

  SET @DatabaseName = 'vault'

Build Version

1.45.2 / 2.25.0

Environment

Self-Hosted

Environment Details

• Operating system: Ubuntu 18.04
• Environment: Docker
• Hardware: QEMU VM

[anhnvme]

I have same issue. +1

[notnamed]

The issue is not that the database name is hardcoded, because it is replaced in this script:

https://github.com/bitwarden/server/blob/master/util/MsSql/entrypoint.sh#L43

But for some reason, that $DATABASE variable is not making it through the setup container, where it is set here:

https://github.com/bitwarden/server/blob/master/scripts/run.sh#L98

Into the mssql container in order for the substitution to occur properly.

As a temporary fix, you can set:

DATABASE=yourdbname

in the file: ./bwdata/env/mssql.override.env. This seems to resolve the issue until a fix can be released.

[venu2062]

That, unfortunately, is not a complete solution. The script entrypoint.sh replaces the string “vault” with the database name provided in the overrides file. If the custom name contains vault as a substring such as “my-vault” the embedded vault gets replaced every time update script is run to update Bitwarden. In this case, it becomes “my-my-vault” followed by “my-my-my-vault” and so on.

The workaround is to remove the data base name override from the overrides file after the first time.

[bradd3ls]

That, unfortunately, is not a complete solution. The script entrypoint.sh replaces the string “vault” with the database name provided in the overrides file. If the custom name contains vault as a substring such as “my-vault” the embedded vault gets replaced every time update script is run to update Bitwarden. In this case, it becomes “my-my-vault” followed by “my-my-my-vault” and so on.

The workaround is to remove the data base name override from the overrides file after the first time.

Hi There,

When you say "after the first time" -- could you please elaborate?

Do you mean after rebuilding the configuraion via ./bitwarden.sh rebuild or are you talking after the first database backup after adding the custom DATABASE name to ./bwdata/env/mssql.override.env file?

[Athanasius]

When you say "after the first time" -- could you please elaborate?

Do you mean after rebuilding the configuraion via ./bitwarden.sh rebuild or are you talking after the first database backup after adding the custom DATABASE name to ./bwdata/env/mssql.override.env file?

It can't be "after the first database backup is made", because I correctly have:

bw_vault_FULL_20230108_000000.BAK
bw_vault_FULL_20230109_000000.BAK
bw_vault_FULL_20230110_000000.BAK

after I'd made the change/rebuild/restart, and have only just commented out the DATABASE=bw_vault from my file (without doing anything else, so no rebuild etc).

[venu2062]

When you say "after the first time" -- could you please elaborate?
Do you mean after rebuilding the configuraion via ./bitwarden.sh rebuild or are you talking after the first database backup after adding the custom DATABASE name to ./bwdata/env/mssql.override.env file?

It can't be "after the first database backup is made", because I correctly have:

bw_vault_FULL_20230108_000000.BAK
bw_vault_FULL_20230109_000000.BAK
bw_vault_FULL_20230110_000000.BAK

after I'd made the change/rebuild/restart, and have only just commented out the DATABASE=bw_vault from my file (without doing anything else, so no rebuild etc).

It can be any time between rebuild and first update. I have done that after rebuild and first backup is successful - just to ensure that backups are working.

[Athanasius]

However it's now several days later and I have no more backups. Last container restart was 6 days ago (so the 8th, when I started getting backups). It feels like, somehow, something has picked up on me commenting out that DATABASE= line.

docker compose logs mssql
2023-01-11 00:00:00.94 Backup      Error: 3041, Severity: 16, State: 1.
2023-01-11 00:00:00.94 Backup      BACKUP failed to complete the command BACKUP DATABASE bw_bw_vault. Check the backup application log for detailed messages.
2023-01-12 00:00:01.02 Backup      Error: 3041, Severity: 16, State: 1.
2023-01-12 00:00:01.02 Backup      BACKUP failed to complete the command BACKUP DATABASE bw_bw_vault. Check the backup application log for detailed messages.
2023-01-13 00:00:00.14 Backup      Error: 3041, Severity: 16, State: 1.
2023-01-13 00:00:00.14 Backup      BACKUP failed to complete the command BACKUP DATABASE bw_bw_vault. Check the backup application log for detailed messages.
2023-01-14 00:00:00.24 Backup      Error: 3041, Severity: 16, State: 1.
2023-01-14 00:00:00.24 Backup      BACKUP failed to complete the command BACKUP DATABASE bw_bw_vault. Check the backup application log for detailed messages.

Really we need the root cause of this fixed. If we specified a custom database name it needs to propagate fully to everything.

[Athanasius]

And, yes, that logging shows it trying to use bw_bw_vault, despite my setting having been bw_vault on setup and in env/mssql.override.env when uncommented. So that's not even the "it keeps adding _vault to the end" that @venu2062 reported.

[venu2062]

And, yes, that logging shows it trying to use bw_bw_vault, despite my setting having been bw_vault on setup and in env/mssql.override.env when uncommented. So that's not even the "it keeps adding _vault to the end" that @venu2062 reported.

Yes. Same thing is happening for me too. I completely removed the DATABASE setting line from the overrides file. But after a machine restart which restarts Bitwarden, the data base name used in the backup got modified, vault was substituted with the full data base name again and the backup now fails.

It looks like the data base name is copied somewhere else while bitwarden rebuild is run and used for restart, update, etc. I am wondering why this script is being updated repeatedly.

This requires more exploration into various scripts for the fix/workaround, looks like.

Hopefully, someone else has some ideas.

[venu2062]

And, yes, that logging shows it trying to use bw_bw_vault, despite my setting having been bw_vault on setup and in env/mssql.override.env when uncommented. So that's not even the "it keeps adding _vault to the end" that @venu2062 reported.

If you are still looking for a solution, the following may be what is causing the issue. Value set to DATABASE from the overrides file is assigned as an environment variable in the mssql container. The entrypoint script uses it to update the backup script. In most instances, this backup script is reset when the container restarts and hence the problem does not occur. Only time the problem reoccurs in my tests is when the machine is restarted. For some reason, in this case the already updated backup script remains in the container and entrypoint script is updating it again. It is possible that docker stores container state on shutdown and is restoring it on restart - not sure of this.

So, removing the setting from the overrides file is not a solution. In fact, you should leave it there and restart bitwarden using “bitwarden.sh restart” command when the backup fails. This, unfortunately, has to be done manually. As of now, this is needed only after a machine restart, as far as i can tell.

Hope that helps.

[mr-kek]

Having the same problem, and can verify the restarting of the machine triggers the issue.
I think I'm just going the backup my vault, rebuild the whole VM and install bitwarden again, and just use the default name of vault to avoid the issue, then upload my vault.
I don't want to go through the issue of having to do the, log into the vm, do the "bitwarden.sh restart" after a machine rebuild.
Just using the default db name seems simpler instead of remembering to "bitwarden.sh restart" each time.

[Athanasius]

Just to note that it's not only a machine restart that causes the issue. It's any restart of the docker containers services that's not mediated by the bitwarden.sh script. I've had the 'doubling up' happen on two separate "new docker packages" upgrades (I'm using the docker.com APT repository).

[venu2062]

I always use bitwarden.sh script. So, in my case it appears that restart of the machine is the only case the problem appears. I seem to have fixed that by adding the line: “@restart sleep 30 && bitwarden.sh restart >/dev/null 2>&1 ” to crontab for the user bitwarden. I haven’t seen the issue after multiple restarts in more than a week.

[mr-kek]

Just to note that it's not only a machine restart that causes the issue. It's any restart of the docker containers that's not mediated by the bitwarden.sh script. I've had the 'doubling up' happen on two separate "new docker packages" upgrades (I'm using the docker.com APT repository).

Restarting the containers with the DATABASE=yourdbname override in the ./bwdata/env/mssql.override.env file worked fine. The issue only occurred when the VM was restarted.

I just created a new VM, installed Bitwarden using vault as the db name, imported my vault, and tested a manual backup, and it works fine. It's just after midnight here, and the auto backup worked.
I found this just easier to do instead of troubleshooting and trying to resolve this issue. I'm just hosting it for myself, so I understand it might not be as easy for others who have lots of users, but if you're just doing this for yourself, just redo it and use db name, vault, it's just easier than trying to jump through hoops.

[Athanasius]

To be clearer, what I really meant was "any restart of the docker services", not the docker containers.

I just:

1. Confirmed that I had, e.g. SET @DatabaseName = 'bw_vault' in the mssql container /backup-db.sql file.
2. systemctl stop docker.service
3. systemctl stop containerd.service
4. systemctl start docker.service (which also brought containerd.service back up).
5. Checked mssql container /backup-db.sql file again, and now that line is SET @DatabaseName = 'bw_bw_vault'.

Basically "restart the machine/VM" is just a specific case of "restart the docker services". I commented mostly so anyone else experiencing this will have in mind that any upgrade, or restart otherwise, of their docker services, will also cause this issue.

@mr-kek I'm probably just going to follow your lead and redo things with the default database name as well. I too am only utilising this for personal use, and it'll be easier than putting up with this headache.

But that absolutely doesn't mean that the bitwarden developers should consider this solved. Either fix this or stop offering the option to have a custom database name at install time. The latter option at least needs to outline, or link to an explanation of, both the actual reasons you might want to do this, and this bug.

For my part I was unaware of the bitwarden docker setup at the time, i.e. that it uses mssql for the database, and was planning ahead in case I ever shifted the database over to, e.g. my already running postgresql instance (which I now realise was never an option). Alternatively ... the default database name could be changed to something less generic than vault, but I can see that then causing issues for all the uses who took the current default. Really, this bug just needs fixing.

[JasmeowTheCat]

Why hasn't this been solved yet honestly - This is giving me a bit of a headache where I am having to manually update the SET line for the backup to complete... I don't know what else is broken by not using the default "vault" database name and made it a different name.

[venu2062]

When you notice that backup is failing fixing it is as simple as running “bitwarden.sh restart”. Of course, you may not notice it for many days/weeks. A brute force method is to put the restart command in cron and run everyday an hour or so before the backup is supposed to happen if it is not too disruptive.

BTW, the problem occurs many times when the OS is updated - could be because some docker components are updated.

[vgrassia]

Thank you all for the help troubleshooting this issue. The fix in #2962 will go out in the July release.