Best way to move databases to new drive - Detach or Offline??


  •  I know this question is all over, but I get mixed results - some say the best way is to detach, other's say to bring the databases offline after moving the location of the files with TSQL..

    I have a database server in which it was set up with about 7 different databases (Citrix, an small Sharepoint, some other software) question is, what is the best way to move the database to a different drive? Users cannot be connected executing statements, so how does SQL server handle that? Do I have to do something special or will SQL allow them to finish their transactions and then kick them?

    What is the benefit versus the other? If I do detach, will that mess up the DB such as citrix from functioning because it isn't registered with the server briefly and then re-registered? Just need some guidance on the best way - I know how to do each way..


    Friday, November 15, 2013 2:28 AM


All replies

  • Hi Tim,

    When we set the database offline, SQL Server waits for all the current connections or sessions on the database to go off i.e even if a Db is having a connection which is in sleeping state and not executing any thing on the Db then also SQL will not be able to bring the database offline till that session is killed or the session dosen't go off.

    While detatching the database SQL gives an option to choose to set the database to Single_User mode immediately where as while setting a database offline SQL dosen't give any such option to choose. So for a database ot be set offline, by default it will wait of all the connections with the database to go off. Till the connections are there with the database SQL won't be able to bring the database offline.

    So from the time perspective Detatching the database will be more immediate than setting the database offline.

    When the database is detatched the DB dosen't get messed up. Any transaction that was running on the database will get rolled back as soon as the database will be reattached. This is done by the database initialization.

    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Friday, November 15, 2013 2:50 AM
  • Hello,

    If you are moving data files within same machine /box on different drive best method is using TSQL command .Movement of data files will require SQL server service restart

    Alter database modify file command given in below link will be helpful

    I seldome use attch detach method for moving files on same machine

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, November 21, 2013 2:24 PM