Post reply

Move Database to a new drive

  • September 2, 2011 at 9:41 am

    #242648

    I have a 280gb database that I would like to move to a new drive on my server.

    What is the best and safest way to do this. This is peoplesoft and I am also wondering if this will cause a problem with that.

    Thanks

  • September 2, 2011 at 11:22 am

    #1376589

    Issue the following command to move the data file location within SQL Server. Obviously change the logical name, drive, path and filename as necessary

    ALTER DATABASE MyDB MODIFY FILE (NAME = mydb_data,

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_Data.mdf')

    [/CODE]

    If you want to move the log too repeat the statement above and change it for the log file details. Once done set the database offline and copy the files to the new folders.

    Bring the database online and when everything is running ok delete the files from the old locations

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • September 2, 2011 at 11:31 am

    #1376593

    then I would set the database off line and copy to the new location and bring online? If copy fails then I could just reset sql back to old location.

  • September 2, 2011 at 11:38 am

    #1376598

    You could also detach the database, copy the database file(or files if you are moving the logs too) to the new location(s) then reattach the database with the relocated files.

    Once it's back up and running and you've verified that everything works you should be able to delete the old files.

  • September 2, 2011 at 12:20 pm

    #1376619

    http://support.microsoft.com/kb/224071

    Gives steps, troubleshooting hints and some cautions.

  • September 2, 2011 at 12:40 pm

    #1376629

    the detach sounds like it my cause a lot of problems I have a very small windows to do this move in as we run 24/7 I need it to just work. does the alter database have as many short falls?

    Thanks

  • September 2, 2011 at 12:50 pm

    #1376641

    Either way your downtime will be the time it takes to copy the database files to the new location.

Viewing 7 posts - 1 through 6 (of 6 total)

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