none
Database Offline - Usage Best Practice RRS feed

  • Question

  • One of our production databases has been moved to a new server. But ,we still have the old database on the old server as a backup if the production roll out fails. I am thinking of setting the old database to "offline" just to eliminate any connections to this database by mistake until the time comes to permanently drop this database. If we need to indeed bring this old database as the production database, I would bring it "online".

    Just curious if what I plan to do is something that is accepted as a "good" practice or if I need to correct my practice.

    Thank you.

    +ive

    Wednesday, March 16, 2011 2:11 PM

Answers

  • I personally do not like "offline" databases because they are not accessible.  If you have any processes which do something in all databases, if it does not check the status, it will fail when it gets to the offline database.

    I would suggest simply detaching the database.  Then it won't exist, and you can reattach it if needed.

     

    • Marked as answer by Jeelani Kamal Wednesday, March 23, 2011 6:46 PM
    Wednesday, March 16, 2011 3:25 PM
    Moderator

All replies

  • I would actually kick off all transactions and rename the database

    ALTER DATABASE dbname SET Single_USER
    WITH ROLLBACK IMMEDIATE;

    ----rename database

    ALTER DATABASE dbname SET Multi_USER
    WITH ROLLBACK IMMEDIATE;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 16, 2011 2:17 PM
    Answerer
  • Uri,

    Thanks for sharing your knowledge.

    I was wondering if you could share as to why you would prefer renaming the database in question instead of bringing the database offline.

    Thank you.

    +ive

    Wednesday, March 16, 2011 2:24 PM
  • Just matter of habit  I think :)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 16, 2011 2:56 PM
    Answerer
  • Just matter of habit  I think :)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 16, 2011 2:56 PM
    Answerer
  • I personally do not like "offline" databases because they are not accessible.  If you have any processes which do something in all databases, if it does not check the status, it will fail when it gets to the offline database.

    I would suggest simply detaching the database.  Then it won't exist, and you can reattach it if needed.

     

    • Marked as answer by Jeelani Kamal Wednesday, March 23, 2011 6:46 PM
    Wednesday, March 16, 2011 3:25 PM
    Moderator