The ability to restore a SQL Server database over and over can sometimes be handy in a test scenario, for instance when you need to test a schema-update for autodeployment through DbUp/Octopus – or you otherwise need to have your database in a known state prior to testing.
However this is quite often blocked because the database is in use, resulting in the above message.
My solution to this is to script the restore to run in single user mode like so:
To achieve the same in Management Studio then go to Options and check this box:
To check whether your database got correctly reset to Multiuser Mode, go to Database Properties > Options, and look at the “State” subgroup, “Restrict Access” item (you’ll need to scroll down to the bottom of the list to see it):