0

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:\DEV\DW.bak', I get the error below when I run the task (i.e. backup path is being prefixed with C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\BACKUP):

Error: Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\BACKUP\'C:\DEV\DW.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.

Improve this question
3
  • 1
    With the minimal details we have, I can't replicate your problem. Commented Sep 5, 2024 at 14:46
  • What is the actual value in the variable? Does it have single quotes in it or just the path? Maybe this could help: stackoverflow.com/a/72943674/1459036 Commented Sep 5, 2024 at 14:46
  • Thank you @ThomA for having a look. Steps to replicate within a SSIS package: 1. Add a variable - this variable is named DatabaseBackupPath and I have set value to 'C:\DEV\DW.bak' for testing 2. Add an Execute SQL Task --> Under General, configure connection, SQLSourceType = Direct input, SQLStatement = the backup script in my question above; under Parameter Mapping, add User::DatabaseBackupPath Does above help? Commented Sep 6, 2024 at 11:58
Related questions
244
SQL Server Restore Error - Access is Denied
112
mssql '5 (Access is denied.)' error during restoring database
3
SSIS Execute SQL Task SQL Command Issue
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.