I do not really understand when it is necessary to set database offline .

I have googled and I did find any information or examples where and why should I set database offline.

I will be perfect if you will give me some example and cases when I need to make my database offline.


When you want to remove access but not DROP the database

Example 1: migrate one database to a new installation

  • Prepare new server
  • Backup/restore to new server
  • Update application connection srings
  • Take DB off-line on old server
  • Test thoroughly
  • Drop DB on old server

Without taking the DB off line, you limit rollback options

Example 2: Cleaning up a messy SQL Server (e.g. developers created DBs)

  • Take offline, see who calls...

I would like to add some more points.

You can also take database offline if you are removing that database. Often we have scenario where we have some old databases lying and we want to know whether it is actually accessed or not in that case you first drop mail to respective stakeholders that you are taking database offline and then after some time you do it. If some one or some process really needs it the query or job would fail and people would come to you with their issue. I have found this very useful.

Other is when you simply want to move database related files from one physical drive to other, after you have run alter database command to move data or log files.

Sometimes in emergency situation where you can see some virus/bug coming from your app and to safeguard data you either disable login through which app is connecting or take database offline so that you can stop data compromise.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.