SQL Server Restricted Access

So, here’s the scenario.  The dev team wanted me to do a DB refresh from UAT to DEV.  Sounds simple enough (I won’t take you through the hours of issues i had with vmware and disk space).  So i took a SQL backup of the DB and then proceeded to restore.

Of course the first attempt failed (as expected) due to being lazy and hoping that today was the day that the app wasn’t running and there were no DB connections, but alas… of course there were, so i proceeded to put the DB into single user mode:

ALTER DATABASE SET single_user WITH ROLLBACK immediate

Then I attempted again:
RESTORE DATABASE <DB NAME>
FROM DISK = ‘<FILE_NAME>’

and BOOM…

Error… Error…

Okay, so I am a Jr… dare I say, Baby DB Admin? So i had to take it to google.  I found a nifty piece of information that stated “If you just set it to single user mode there must be a chance that a new connection gets made just before the restore happens which would then stop the restore from happening.”

Presuming this was my issue, the suggested method was to set the db to “Restricted” user. This would restrict the user to members of the db_owner, dbcreator or sysadmin roles. So I tried the following:

RESTORE DATABASE <DB NAME>
FROM DISK = '<FILE NAME>'
WITH RESTRICTED_USER

It actually worked!!

I later found this article that explains the different types of restricted user options that I will incorporate to my script.
http://www.blackwasp.co.uk/SQLRestrictedUser.aspx

Don’t forget to change the DB back to multi user mode:

ALTER DATABASE <DB_NAME> SET MULTI_USER