using SMO: RESTORE cannot process database xxxx because it is in use by this session.

Question

an VB application migrated from Visual studio 2013 to 2019.

Replace SMO V12 with SMO V15.0 (together with another bunch of DLLs)

getting : RESTORE cannot process database 'VirtuoTest ' because it is in use by this session. It is recommended that the master database be used when performing this operation. RESTORE DATABASE is terminating abnormally.

Same with SMO V15.0.18224, V14, V13 (V12 not accepted by VS2019 anymore).

There is no other session using the database, the restore session trace shows login, restore and error). The connection string used has master as initial catalog.

Any help ?

Answer 1

Hi @Dan Trifan

I found this thread for you that talks a lot about "RESTORE cannot process database...": https://social.msdn.microsoft.com/Forums/sqlserver/en-US/37ee8e24-7aaa-472b-861a-fc0cc513338a/unable-to-restore-database-quotbecause-it-is-in-use-by-this-sessionquot?forum=sqltools.

There is also a document on backing up and restoring databases and transaction logs with SMO here: https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/backing-up-and-restoring-databases-and-transaction-logs?view=sql-server-ver16.

Hope these can help you.

Best regards,

Aniya

Answer 2

This error message typically occurs when you try:

USE mydb
RESTORE mydb FROM ...

You need to be in another database when you run the RESTORE command.

Yes, I see that you say that you have master in the connection string, but at the time when you run the RESTORE command, you are in the database you are trying to restore. Since I don't see your code, there is no way I can tell you where you have gone wrong.

If you can share a piece of code that repros the problem we may be able to give further help.

Answer 3

indeed, previous sqls are in the target database. Problem is that with smo.dll V12 was functioning ok, seems that restore component of the SMO was changing the current database itself. The new DLL(V13,14,15) not, so I had to add an explicit use master before activating the restore.

Thanks