Moving Databases with the ALTER Command

  • imSQrLy

    Ten Centuries

    Points: 1335

    Comments posted to this topic are about the item Moving Databases with the ALTER Command

    Jimmy

    "I'm still learning the things i thought i knew!"
  • Private Gripweed

    SSChasing Mays

    Points: 632

    The script works well with one exception. If your logical name for the database files contains spaces, it will cause issues.

    If you alter the stored procedure and enclose the logical name for the database file in double quotes, this fixes the issue. See below:

    set @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = "' + @LogicalName + '", FILENAME = "' + @NewPath + '")'

  • Johan van Tonder

    Old Hand

    Points: 371

    Very good method.

    I'm just not sure if I would want to be any one of the users connected to the DB while the script is run, since I will get killed 😉

    Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Yes, I know what's being meant, but that gave me laugh though 🙂

  • Private Gripweed

    SSChasing Mays

    Points: 632

    LOL that is funny, I did not think about it that way.

  • rondad

    SSC-Addicted

    Points: 423

    Has anyone tested the script with using a unc path for the new target location rather than a "hard-coded" drive letter?

    Ron

  • Private Gripweed

    SSChasing Mays

    Points: 632

    Easily tested. But I have not

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    EDIT: Brian needs to read more closely. 🙂

    K. Brian Kelley
    @kbriankelley

  • imSQrLy

    Ten Centuries

    Points: 1335

    Johan van Tonder (8/26/2009)


    Very good method.

    I'm just not sure if I would want to be any one of the users connected to the DB while the script is run, since I will get killed 😉

    Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Yes, I know what's being meant, but that gave me laugh though 🙂

    Bang your dead!

    I haven't laughed like this all week.

    Jimmy

    "I'm still learning the things i thought i knew!"
  • imSQrLy

    Ten Centuries

    Points: 1335

    Private Gripweed (8/26/2009)


    The script works well with one exception. If your logical name for the database files contains spaces, it will cause issues.

    If you alter the stored procedure and enclose the logical name for the database file in double quotes, this fixes the issue. See below:

    set @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = "' + @LogicalName + '", FILENAME = "' + @NewPath + '")'

    Thanks for pointing that out. I will update my script.

    Jimmy

    "I'm still learning the things i thought i knew!"
  • imSQrLy

    Ten Centuries

    Points: 1335

    rondad (8/26/2009)


    Has anyone tested the script with using a unc path for the new target location rather than a "hard-coded" drive letter?

    Ron

    I have not tested it like that.

    Jimmy

    "I'm still learning the things i thought i knew!"
  • imSQrLy

    Ten Centuries

    Points: 1335

    K. Brian Kelley (8/26/2009)


    You may want to add a warning that your script is going to turn on xp_cmdshell in order to perform the move. This may balk someone's configuration, especially if policy management is on.

    At the bottom of the article it states:

    Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE. Anyone connected will be kicked off. The script does set the db back to multi user when it brings the database back online after the move.This script also turns on and uses the xp_cmdshell advanced option. It will set this option back to its original setting (enabled or disabled) at the end of the script.

    Jimmy

    "I'm still learning the things i thought i knew!"
  • K. Brian Kelley

    SSC Guru

    Points: 114465

    imSQrLy (8/26/2009)


    At the bottom of the article it states:

    Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE. Anyone connected will be kicked off. The script does set the db back to multi user when it brings the database back online after the move.This script also turns on and uses the xp_cmdshell advanced option. It will set this option back to its original setting (enabled or disabled) at the end of the script.

    Yup, I see that now on a second read-through. I apologize.

    K. Brian Kelley
    @kbriankelley

  • george sibbald

    SSC Guru

    Points: 104200

    just wanted to point out that if using the backup\restore method to move the database files you do not need to delete\rename the database, just use the 'with move' clause in the restore.

    ---------------------------------------------------------------------

  • Ray Herring

    SSCertifiable

    Points: 5309

    You should add a check for AutoStats. If you set the database to single user and AutoStats gets started you may have problems regaining access. I used a similar approach to merge two databases into one (at 100+ customer sites) and I did run into the problem.

    Also, IMHO if you do this often enough to justify writing a stored procedure then you really should have a lot more error checking built into the code.

  • el-slo

    SSC Rookie

    Points: 35

    Works well in SQL Server 2005, but doesn't in 2000 which returns the error:

    EXECUTE cannot be used as a source when inserting into a table variable.

    Apparently 2000 doesn't allow the output of a stored procedure to be inserted to a table variable. Would be good to have mentioned this in the article, and would be even better if a solution was suggested!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply