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?
 
Mark WillsConnect With a Mentor Topic 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.