Site icon Database Tutorials

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,

 

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.

Exit mobile version