I have the following SQL Statement in a SSIS Execute SQL Task:
use [master];
declare @DatabaseBackupPath varchar(250) = ?;
backup database [DW]
to disk = @DatabaseBackupPath
with copy_only
, noformat
, init
, name = N'DW-Full Database Backup'
, skip
, norewind
, nounload
, compression
, stats = 10;
go
Variables are configured correctly and if value is e.g. set to ‘C:DEVDW.bak’, I get the error below when I run the task (i.e. backup path is being prefixed with C:Program FilesMicrosoft SQL ServerMSSQL16.MSSQLSERVERMSSQLDATABACKUP):
Error: Cannot open backup device ‘C:Program FilesMicrosoft SQL ServerMSSQL16.MSSQLSERVERMSSQLDATABACKUP’C:DEVDW.bak”. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
Our workload requires database to be restored in the event of a failure in the process and the Backup Database task is not suitable for us as it adds a timestamp to the database backup name. Any help with above error would be much appreciated.
If I put the query in a variable and then reference this in the execute SQL Task, it works but just wondered if anyone has encountered above issue before or can explain why it may be happening.
3