I periodically have problems when trying to restore a GP company database over the Test database as SQL reports that the database is currently in use.
One way of resolving is to restart the SQL Server but this is only possible on a stand alone test system which is not being used by other people but this approach is overkill.
The better solution is to change the database to single user, restore the database and change the database back to multiple user. This can be done manually through SQL Server Management Studio but is far easier done via a SQL script.
The below example is restoring over the top of the Fabrikam TWO database (with the elements you would need to change highlighted in yellow);
USE master
GO
ALTER DATABASE
[TWO]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [TWO] FROM
DISK = N'C:\SQL Data\SQL2008R2\Backup\TWO.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
ALTER DATABASE [TWO] SET MULTI_USER
GO
As always don’t run the script unless you’re happy dealing with SQL Server Management Studio and take caution that you don’t overwrite the wrong database.
Pingback: Interesting Findings & Knowledge Sharing » Restoring A Database Without Encountering A Locking Issue