How to deal with spaces on the DB for MSSQL?

[farzadha2]

HI
Currently was wondering how to deal with db which have spaces in their name?

example-mssql-db-backup | 2023-12-08.12:00:29 [INFO] ** [16-192.168.2.244__'Reportes\ SAP'] Next Backup at 2023-12-08 21:00:00 -05
i tried the following

- DB16_NAME=Reportes\ SAP
but not sure what's the correct way?

As
in the log i was seeing this

[tiredofit/db-backup:4.0.29 12:07:37 /] $ backup16-now
2023-12-08.12:08:00 [INFO] ** [db-backup16] Starting Manual Backup for db-backup16
2023-12-08.12:08:00 [NOTICE] ** [16-192.168.2.244__'Reportes\ SAP'] Dumping MSSQL database: ''Reportes\ SAP''
2023-12-08.12:08:01 [INFO] ** [16-192.168.2.244__'Reportes\ SAP'] DB Backup of 'mssql_'reportes\ sap'_192.168.2.244_20231208-120800.bak.gz' completed successfully
stat: cannot stat '/tmp/backups/mssql_'\''reportes\ sap'\''_192.168.2.244_20231208-120800.bak.gz': Invalid argument
date: '/tmp/backups/mssql_'\''reportes\ sap'\''_192.168.2.244_20231208-120800.bak.gz': Invalid argument
stat: cannot stat '/tmp/backups/mssql_'\''reportes\ sap'\''_192.168.2.244_20231208-120800.bak.gz': Invalid argument
2023-12-08.12:08:01 [NOTICE] ** [16-192.168.2.244__'Reportes\ SAP'] Backup of 'mssql_'reportes\ sap'_192.168.2.244_20231208-120800.bak.gz' created with the size of  bytes
chmod: cannot access '/tmp/backups/mssql_'\''reportes\ sap'\''_192.168.2.244_20231208-120800.bak.gz': Invalid argument
mv: cannot stat '/tmp/backups/mssql_'\''reportes\ sap'\''_192.168.2.244_20231208-120800.bak.gz': Invalid argument
2023-12-08.12:08:01 [ERROR] ** [16-192.168.2.244__'Reportes\ SAP'] Moving of backup 'mssql_'reportes\ sap'_192.168.2.244_20231208-120800.bak.gz' reported errors
2023-12-08.12:08:01 [NOTICE] ** [16-192.168.2.244__'Reportes\ SAP'] DB Backup for ''Reportes\ SAP'' time taken: Hours: 0 Minutes: 00 Seconds: 01
2023-12-08.12:08:01 [ERROR] ** [16-192.168.2.244__'Reportes\ SAP'] Skipping Cleaning up old backups because there were errors in backing up
2023-12-08.12:08:01 [INFO] ** [16-192.168.2.244__'Reportes\ SAP'] Backup 16 routines finish time: 2023-12-08 12:08:01 -05 with exit code 0
2023-12-08.12:08:01 [NOTICE] ** [16-192.168.2.244__'Reportes\ SAP'] Backup 16 routines time taken: Hours: 0 Minutes: 00 Seconds: 01

debug version

+ silent run_as_user /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'thepassword' -Q 'BACKUP DATABASE ['\''Reportes\ SAP'\''] TO DISK = N'\''/tmp/backups/mssql_'\''reportes\ sap'\''_192.168.2.244_20231208-121442.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\'''\''Reportes\ SAP'\''-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
+ '[' true = true ']'
+ run_as_user /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'thepassword' -Q 'BACKUP DATABASE ['\''Reportes\ SAP'\''] TO DISK = N'\''/tmp/backups/mssql_'\''reportes\ sap'\''_192.168.2.244_20231208-121442.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\'''\''Reportes\ SAP'\''-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
+ sudo -Eu root /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'thepassword' -Q 'BACKUP DATABASE ['\''Reportes\ SAP'\''] TO DISK = N'\''/tmp/backups/mssql_'\''reportes\ sap'\''_192.168.2.244_20231208-121442.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\'''\''Reportes\ SAP'\''-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
Msg 102, Level 15, State 1, Server HERCULES, Line 1
Incorrect syntax near '\ '.
Msg 319, Level 15, State 1, Server HERCULES, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
+ exit_code=0
+ var_true ''
+ '[' '' = true ']'
+ '[' '' = yes ']'

also tried with putting it together without space says db not found

also tried with normal space this is the other debug

+ silent run_as_user /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'thepassword' -Q 'BACKUP DATABASE ['\''Reportes SAP'\''] TO DISK = N'\''/tmp/backups/mssql_'\''reportes sap'\''_192.168.2.244_20231208-121816.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\'''\''Reportes SAP'\''-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
+ '[' true = true ']'
+ run_as_user /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'thepassword' -Q 'BACKUP DATABASE ['\''Reportes SAP'\''] TO DISK = N'\''/tmp/backups/mssql_'\''reportes sap'\''_192.168.2.244_20231208-121816.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\'''\''Reportes SAP'\''-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
+ sudo -Eu root /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'thepassword' -Q 'BACKUP DATABASE ['\''Reportes SAP'\''] TO DISK = N'\''/tmp/backups/mssql_'\''reportes sap'\''_192.168.2.244_20231208-121816.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\'''\''Reportes SAP'\''-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
Msg 102, Level 15, State 1, Server HERCULES, Line 1
Incorrect syntax near 'sap'.
Msg 319, Level 15, State 1, Server HERCULES, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
+ exit_code=0
+ var_true ''
+ '[' '' = true ']'
+ '[' '' = yes ']'

Thank you

[tiredofit]

Not really sure. I have a build at tiredofit/db-backup:develop that may solve what you are looking for perhaps? If it works I will include it in next release.

[farzadha2]

Hi @tiredofit Thank for the reply, i updated to the dev version, but still shows the same issue when there spaces in the db

[tiredofit]

OK. I'm not sure then. I tried to wrap it in 'quotes' but that doesn't seem to work. I would recommend trying to figure out the CLI commands and then I can work that into the automation..

[farzadha2]

gotcha will take a look at it and post back

[farzadha2]

it seems got it working

[tiredofit/db-backup 18:34:00 /] $ /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'mypassword' -Q "BACKUP DATABASE [Reportes SAP] TO DISK='C:\Backup\backup_file.bak' WITH INIT"
Processed 464 pages for database 'Reportes SAP', file 'Reportes SAP' on file 1.
Processed 1 pages for database 'Reportes SAP', file 'Reportes SAP_log' on file 1.
BACKUP DATABASE successfully processed 465 pages in 0.947 seconds (3.829 MB/sec).

i had to put the brackets, but this worked manually i assume on the container would need to be changed?

[tiredofit]

Nice one. I'm wondering though if we're running into quoting issues. Can you show me your docker-compose DBXX_NAME entry (whole line please)

[farzadha2]

hi there sure thing

      - DB16_TYPE=mssql
      - DB16_HOST=192.168.2.244
      - DB16_PORT=1433
      - DB16_NAME=Reportes SAP

[tiredofit]

OK! I see where things have gone off here. This was happening with some other variables where quotes are being added inadvertently.

Can you try with the latest tiredofit/db-backup:develop and then try to execute backup16-now to see if your backup works?

BTW - its impressive to see 16 backup jobs on the same container ;)

[farzadha2]

Thanks for the reply,

Currently this is the debug log im getting after updating, i think there is an extra quote which is having the issue

+ silent run_as_user /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'mypassword' -Q 'BACKUP DATABASE [[Reportes SAP]] TO DISK = N'\''/tmp/backups/16_dbbackup.rCuibe/mssql_[reportes sap]_192.168.2.244_20231212-193446.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\''[Reportes SAP]-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
+ '[' true = true ']'
+ run_as_user /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'mypassword' -Q 'BACKUP DATABASE [[Reportes SAP]] TO DISK = N'\''/tmp/backups/16_dbbackup.rCuibe/mssql_[reportes sap]_192.168.2.244_20231212-193446.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\''[Reportes SAP]-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
+ sudo -Eu root /opt/mssql-tools18/bin/sqlcmd -C -S 192.168.2.244,1433 -U sa -P 'mypassword' -Q 'BACKUP DATABASE [[Reportes SAP]] TO DISK = N'\''/tmp/backups/16_dbbackup.rCuibe/mssql_[reportes sap]_192.168.2.244_20231212-193446.bak'\'' WITH NOFORMAT, NOINIT, NAME = '\''[Reportes SAP]-full'\'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
Msg 102, Level 15, State 1, Server HERCULES, Line 1
Incorrect syntax near '.168'.
Msg 105, Level 15, State 1, Server HERCULES, Line 1
Unclosed quotation mark after the character string ', SKIP, NOREWIND, NOUNLOAD, STATS = 10

BTW currently using 43 jobs working amazing

[tiredofit]

OK, I'm seeing multiple [[ brackets which seems to be the cause here. Can yoi verify to make sure that your DB_NAME doesn't have any [ in there?

[farzadha2]

yep my bad forgot to remove it there now its working perfectly thank you so much

[tiredofit]

Great! 4.0.31 is the version with the fix, being built as I write this..

c37de57