Database couldn't restore (not exclusive) - why did this script fix it?

[Rx_]

Maybe someone can explain why this worked?
The Access FE to SQL Test DB (linked tables) worked perfectlly.
Have done this many times - Make a full backup of the Production DB to file. Then go to the Test DB and restore it from file (for newbies, go to 2nd tab and change the restore to - destination file to the Test DB location).

For 2 days, the SSMS Restore DB wouldn't work because the Test SQL DB could not get Exclusive use. Nobody was logged into it (MS Access Linked Tables).
I even did a 2nd full backup of Production DB to insure the backup file wasn't corrupt.

Found a discussion on SQL Server Central that unlocked the Test DB.
use master
Alter database MyTestDB set offline with rollback immediate
-- ran it -- Message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Then the Restore DB to the Test DB worked as usual. Was able to immediatelly connect to the Test DB afterwards using Access Linked Tables.

My DB is set to Simple Transactions to speed things up.
After doing this either manually or by script on a weekly basis for years, this is the first time to have this problem (except if a user was actually connected of course).

My problem is gone, but maybe someone can explain why the exclusive occured? And, advise if this code was proper for the situation.

 

[Rx_]

While my example uses the SSMS interface to replace the Test DB with the copy of the Production DB, this example shows the script.
Found this on Microsoft SQL Server forum. Evidently, it is something used often.
I have never had to use it before. Still don't have a clue why it was necessary this time.

USE [COLOR=#666600][[/COLOR]master[COLOR=#666600]][/COLOR]
GO
ALTER DATABASE [COLOR=#666600][[/COLOR]HMX_TEST[COLOR=#666600]][/COLOR] SET  RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [COLOR=#666600][[/COLOR]HMX_TEST[COLOR=#666600]][/COLOR]
    FROM DISK [COLOR=#666600]=[/COLOR] N[COLOR=#008800]'L:\dwbi\STAGE\unzipped\Main_agk.BAK'[/COLOR] 
    WITH FILE [COLOR=#666600]=[/COLOR] [COLOR=#006666]1[/COLOR]
        [COLOR=#666600],[/COLOR]MOVE N[COLOR=#008800]'Main_Data'[/COLOR] TO N[COLOR=#008800]'J:\dwbi\data\HMX_TEST.mdf'[/COLOR]
        [COLOR=#666600],[/COLOR]MOVE N[COLOR=#008800]'Main_Log'[/COLOR]  TO N[COLOR=#008800]'K:\dwbi\logs\HMX_TEST.ldf'[/COLOR]
        [COLOR=#666600],[/COLOR]NOUNLOAD
        [COLOR=#666600],[/COLOR]REPLACE
        [COLOR=#666600],[/COLOR]STATS [COLOR=#666600]=[/COLOR] [COLOR=#006666]10[/COLOR] 
    GO
ALTER DATABASE [COLOR=#666600][[/COLOR]HMX_TEST[COLOR=#666600]][/COLOR] SET  MULTI_USER WITH ROLLBACK IMMEDIATE
GO

 

[SQL_Hell]

Exclusive access error when restoring a database is because someone using the 'sa' login or someone with sysadmin permissions is connected to your database.

Who has sysadmin?