Set a Database To Read Only Mode, Take Database Offline or Detach a Database


Sometimes we need to move the database from one instance to another instance. But we don’t dare to erase our old database right away. We keep the old database as a backup in the old instance for a while. It is a good way to use the old database if there is a problem in the new instance. But there is a risk that the application will accidentally go to the old database. Even if you disable the login on the old instance, you need to make sure that the old database cannot be used. You can disable all the logins that use the application on the instance where the old database is, but¬†sometimes this can be difficult.

In such a case,

  • You can set the database To Read Only Mode.
  • You can detach the database.
  • I prefer to take database offline and in my opinion it is the safest way.


Method 1: You can use the following script to change the database name and set the database To Read Only Mode.

Method 2: To detach the database, you can right-click on the database and click Tasks-> Detach on SSMS.

But before detach the database, you must learn the paths of the database files with the following query and save this information anywhere in order to be able to attach it.

You can learn more about detach-attach from the below articles.

How To Change The Disk Of Database Files On SQL Server(Detach Attach & Backup Restore Methods)“,

How To Detach and Attach a Database Containing FileStream Data

Method 3: You can take the database offline. You can perform this operation with the help of the following script. I’m using this method.

If you want to take it online again, you can use the following script.

Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *