SQL SERVER – Making Filegroup Read Only

Is it possible to make any table read only? Well, not directly but you can for sure do that by making filegroup read only. I was asked this in my recent  Comprehensive Database Performance Health Check. Let us see the solution.

SQL SERVER - Making Filegroup Read Only fileg-800x412

Here is the script to make any filegroup a read only filegroup.

ALTER DATABASE DBName MODIFY 
FILEGROUP Read_Only READONLY

Once the filegroup is read only, you can read the data from the table on the same filegroup but can’t insert, update, or delete. I hope you like this little trick.

If you want to bring back the filegroup to readwrite, you can execute the following command.

ALTER DATABASE DBName MODIFY 
FILEGROUP Read_Only READWRITE

You can also do this from the database properties and filegroup tab.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL FileGroup, SQL Scripts, SQL Server
Previous Post
Feedback Request – COUNT and SUM Videos
Next Post
SQL SERVER – Making Table Read Only via FileGroup

Related Posts

Leave a Reply