For one of my archival databases, I am looking to make it READ ONLY and would like to know what steps I should take.

In this tip I go over some best practices before setting a database to READ ONLY.

Databases can be set to READ ONLY mode and back using T-SQL and SSMS.

So based on this, certain changes should be made to optimize the performance of a READ ONLY database.

You may add or remove certain steps in the script according to your specific requirements.

This Cannot Run From Within A Read-Only Dmg File Full Backup As

The script assumes that your database is in the FULL recovery mode and also you have a prior full backup as a base for transactional log backups.

If that is not the case then you may skip step 1 and 2 in the following script.

GO -- Step 1. Assuming that DB has recovery model FULL and has a prior full backup.

GO -- Step 7. Modify permissions if required and addremove users appropriately -- Step 8.

After completing tasks in script 1, we are ready to change the status of AdventureWorks to READ ONLY.

This task may be accomplished through the system stored procedure spdboption or through and ALTER DATABASE command.

Click on Options in left panel and scroll to state related options at end.

If the database is changed to READ ONLY through T-SQL then a refresh of your databases may be required to show the color change in SSMS.

However considering the READ ONLY state you may want to have a different backup plan than that of a READ WRITE database.

Consider using simple recovery mode along with only full backups.

As there will be no DML operations on db in read only mode statistics will provide good performance for read operations.