How to backup all user databases

[Hamsterman]

I can see I can specify the databases to backup like this
- "DB_NAMES=
MyFirstDatabaseToRestore
MySecondDatabaseToRestore"

But how can I just let it backup all user databases - included ones I will add later on.

[christianbumann]

Hi @Hamsterman , this is currently not supported. It would also not be clear which databases are “user databases“ (ok, e.g. master can be excluded) and which are „system databases“ (this could change with further SQL Server versions), so this should not be hardcoded inside the backup container. I don‘t know if there is an easy and reliable way to detect this automatically.

Another aspect is the username and password. Currently this needs to be the same for every database, and this is not really good regarding security as every database should have another username/password.
So if you have a database with different username and/or password the script would fail.

What is the reason to have many databases running in one container? If the container is down - all databases are down at the same time - even it‘s a short outtake.

I would recommend for each database a separate compose file which starts 2 containers - so in case of a container crash only one database is affected, and you can give different user credentials for each database which makes it more secure.

I hope this answer helps you

[Hamsterman]

The way you get all user database is "just" to exclude the system databases.
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb')
And then iterate over that list. If you like I might be able to create a bash script that does this for you.

The "sa" user has access to all databases - but one could easily create a backup user that is used instead.

[christianbumann]

@Hamsterman Sorry for my late answer. It would be nice to have such as extension in the script. But then, it should not break existing functionality. An additional setting e.g. BACKUP_ALL_DATABASES (boolean - default false).
I cannot say if we can make a new docker version in near future as we are extremely busy and it's not a priority for us at the moment, I am sorry.