3

is there an option to close existing connections when doing a database restore in transact sql (equivalent to the box that we can check in SSMS)?

can we use with restricted user to do it an if so, what should we do to remove this option once the restore operation finish

Thanks in advance

6

You will have to set the database to single user with roll back immediate, do the restore , then set to multi user in the same batch

ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [test] FROM  DISK = N'V:\MSSQL2016\Backup\test.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [test] SET MULTI_USER

You could get this script by setting all your options in SSMS and instead of clicking OK, Click Script at the top of the window.

enter image description here

| improve this answer | |
4

I had issues setting the database in single user e.g. a sql agent connection might make it first and then you will struggle to take that database out of single_use.

Instead, I prefer OFFLINE.

Alter database your_db_name set OFFLINE with rollback immediate
-- do your restore with replace and recovery
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.