SQL Server RESTORE BACKUP via pre SQL statement

[esar]

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?

[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.

[rkapoor]

@esar this might be a timeout thing between Designer and the Database. Have you tried this using the in-db tools or via a stored proc in the workflow? 

Can you upload a snapshot of your workflow log when this error comes about?

[esar]

@rkapoor  Thanks for your response.

Time Out is my guess too, I don't know if it's build-in or can I fix it?

Without a restore statement that same pre SQL statement works well.

I was not able to use that Restore statement with in-db tools either.

SP is my next option.

[esar · Accepted solution]

@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.

[rkapoor]

@esar I was thinking drivers earlier when I messaged you. Im glad its working however