Operating System Error 123 On Backup of SQL Database Via Subprocess

2
votes

I am currently attempting to backup a database automatically using a Python 3.7 application running the background. I managed to backup the database using the following query string. 

SQLCommand = ("sqlcmd -E -S %s -Q \"BACKUP DATABASE %s TO DISK=\'%s\%02d-%02d-%02d-%02d:%02d:%02d.bak\'\"" % 
("MYSERVER\SQLSERVICE", "MyDatabase", 
"C:\\Users\Malek\Documents\PYSQLBS\Backups",
CDT.year, CDT.month, CDT.day, CDT.hour, CDT.minute, CDT.second))
...
print(subprocess.Popen(SQLCommand, shell=True, stdout=subprocess.PIPE).stdout.read())

However, I am getting this error. I am 100% sure that the folder C:\Users\Malek\Documents\PYSQLBS\Backups exists because I tested the command directly in the command line, and it worked perfectly.

Msg 3201, Level 16, State 1, Server MYSERVER\SQLSERVICE, Line 1
Cannot open backup device 'C:\Users\Malek\Documents\PYSQLBS\Backups\2019-09 -09-16:24:46.bak'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
Msg 3013, Level 16, State 1, Server MYSERVER\SQLSERVICE, Line 1
BACKUP DATABASE is terminating abnormally.

1
pythonsqlsql-serverdatabase-backups
Pls pay attention to what product tags you use! This question is about ms sql server, not mysql! – Shadow
First thing you could do: hand in the command line as a list and not as a quoted sting. See the subprocess docs for many examples. – Klaus D.
I believe that your problem is beacuse of the duplicated \-es, but I might be wrong. – g_bor
Windows folder/file paths cannot contain : characters, except when delimiting the drive letter, e.g.: C:\foo\bar.txt is ok. C:\foo\bar:::baz.txt is not. – AlwaysLearning

1 Answers

1
votes

The problem is the : characters in your backup file's name: 2019-09 -09-16:24:46.bak

Windows folder and file paths cannot contain : characters, except when delimiting the drive letter, e.g.: C:\foo\bar.txt is ok. C:\foo\bar:::baz.txt is not.