SQL Server Restore command not working against SQL Server 2017 database server

I have a SQL Server Restore Database action that has been working fine for years. However, we just upgraded our test environment (where the restore happens) to SQL Server 2017. Now, I am getting an error that the database cannot be restored because the current session is connected to it:

Could not restore backup: RESTORE cannot process database ‘-removed dbname-’ because it is in use by this session. It is recommended that the master database be used when performing this operation

However, I cannot specify that I am connecting to the master database while restoring the test database, and even if I try, I am told that the database needs to be in single-user mode to do the restore, and you cannot set the master database to be in single-user mode. It thinks that I need to restore the master database.

How do I connect to SQL Server 2017 with Automise to do a database restore? I have always configured the database restore the same way, and have never run into this problem before.



Hi Jonathon

Not sure what has changed with SQL Server 2017 but I’m surprised that this ever worked before without specifying the master db as the initial database. The good news that you can set the Initial db to master, it’s just not exposed on the UI - not sure why as the dev that wrote this is no longer here so I can’t ask him!

You can set it via the Action Properties Tab (F12 or Alt+F4 depending on your key bindings)

We’ll expose this in the Action properties dialog in the next update and the initial db will have a default value of master.

That did the trick. Thank you for the fast response! Maybe there was a setting in earlier database servers (default, or set by us) that set the default DB to master so this did not need to be explicitly done.