November 21, 2010 by pinaldave
I have previously written about how using T-SQL Script we can convert the database access to single user mode before backup.
I was recently asked if the same can be done using SQL Server Management Studio.
Yes!
You can do it from database property (Write click on database and select database property) and follow image.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Follow @pinaldave
Send +Pinal Dave
an email at pinal@sqlauthority.com
hi Pinal
I can’t see the pic..
would you please right down the structure too???
tnx
[...] Change Database Access to Single User Mode Using SSMS Image says all. [...]
[...] SQL SERVER – Cursor to Kill All Process in Database I indeed wrote this cursor and when I often look back, I wonder how naive I was to write this. The reason for writing this cursor was to free up my database from any existing connection so I can do database operation. This worked fine but there can be a potentially big issue if there was any important transaction was killed by this process. There is another way to to achieve the same thing where we can use ALTER syntax to take database in single user mode. Read more about that over here and here. [...]
[...] FIX : Error : 3702 Cannot drop database because it is currently in use This is a very generic error when DROP Database is command is executed and the database is not dropped. The common mistake user is kept the connection open with this database and trying to drop the database. The database cannot be dropped if there is any other connection open along with it. It is always a good idea to take database in single user mode before dropping it. Here is the quick tutorial regarding how to bring the database in single user mode: Using T-SQL | Using SSMS. [...]
if i have 30 database in single instance, do i need to go one by one and change in to single user mode to take the backup ?
Also how do i take the back of complete 30 database in a single click ? Do we have any way through GUI mean SQL mangement Studio