Put PROD databases in Read-Only Mode

We are planning to put all Production Databases into Read-Only mode, this is part of some maintenance. This is SQL Server 2008 R2.

What is the safest way to put all DB's in Read-Only mode.  There are 150 DB's
VijayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
The basic syntax is
USE [master]
GO
ALTER DATABASE [yourdb] SET READ_ONLY WITH NO_WAIT
GO

Open in new window


for databases on the same server, you can use the undocumented stored procedure MS_FOREACHDB
EXEC sp_MSforeachdb 'USE master
GO
ALTER DATABASE [?] SET READ_ONLY WITH NO_WAIT
GO'

Open in new window

the ? is a placeholder for the DB Name
and when finished your maintenance job, dont forget to set back to read_write e.g.
ALTER DATABASE [yourdb] SET READ_WRITE WITH NO_WAIT

Open in new window


Be Very Careful and test a few times on (say) a dev server, then on one DB after user hours to test access (etc) before you let loose on production....

If you get an error because of existing connections, you can try setting it into single user mode first :
ALTER DATABASE [yourdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Open in new window


And, Be very, very careful doing any of the above. Make sure you have full backups and develop a step by step plan before you begin.

Naturally, start by giving users significant advanced notice, even if doing it after hours, communication ahead of time is one of the critical success factors as far as I am concerned.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
During scheduled maintenance you shouldn't even let nobody to connect to the databases. Even read only permissions can interfere with your maintenance tasks.
I would recommend to schedule the necessary maintenance tasks for a period that no users is expected to connect (usually during night or weekends) and then perform the maintenance tasks without changing database status.
0
VijayAuthor Commented:
During mainteance before we taking DB's read-only we are completly shutting down application. once we get the confirmation from ap's team.
then we go ahead and take db's into read only mode.

Actually this we are doing part of Cutover(Migration). If cutover successfull we never put Db back to read-write on source.
0
VijayAuthor Commented:
Thank you Mark.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.