SQL Server RESTORE BACKUP via pre SQL statement

My use case is to collect data from SQL server backup file which has hundreds of databases. I would like to restore databases one by one and read the same table from every database.

I have tried to restore the database with a pre SQL statement but the database stuck in restoring mode.

The same SQL statement works fine in SSMS but not with Alteryx. I have a feeling that Alteryx does not wait long enough that restore finishes and that fails to process.

Does anyone have a solution or idea?

Database Connection

Accepted answers

esar

@rkapoor

Now it works. Looks like there is some limitations or problem with ODBC driver, which Alteryx uses.

When I used the latest OleDB driver Restore worked well.

All comments

rkapoor

@esar can you share what your Alteryx workflow looks like and what error you are receiving?

esar

@rkapoor Here is the file.

Alteryx sends a pre SQL statement to the server but the database gets stuck in a restoring state. So Alteryx gives an error that the database does not respond.

Same Restore code works fine in SSMS.

Copy of SQL restore macro.yxmc

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703