Post reply

moving databases

  • February 24, 2011 at 9:32 am

    #231728

    hi guys,

    i am planning to move the 100 databases (E:) one location to (G:)another location due to the disk space issue problems.i want to do at a time is there any way.

    please give me suggestions

    Regards
    venkat

  • February 24, 2011 at 9:43 am

    #1291075

    You should review this MS LINK as it provides a great process for doing a planned move. My recommendation would be to write a script to create the file location change script and then run that just before the planned outage.

    Hope this helps.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • February 24, 2011 at 9:59 am

    #1291086

    thanks for the quick response

    i want to move the all databases at a time is there any solution

    Regards
    venkat

  • February 24, 2011 at 10:26 am

    #1291112

    What are your plans for the migration after reading that document? Can you put together an outline of the steps you are going to follow and reply back with that?

    Can you put together an attempt at a script that will script out the file location changes?

    I'm more than willing to help but don't want to do the work for you. I hope you understand. 🙂

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • February 24, 2011 at 1:37 pm

    #1291264

    i understood .:-)

    thank you for you are suggestion

    Regards
    venkat

  • February 24, 2011 at 1:39 pm

    #1291265

    Do you have a solution then and / or a task outline of the steps you will need to take? If not, post what you have so far and we can work through that. Let me know.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • February 25, 2011 at 4:03 pm

    #1291824

    I hadn't heard back from you on this but thought I would provide this to get you started. This will generate the script that you would need to do that planned move of your files. Please review what I have done and make sure that it is exactly what you need before you run the statements that are generated as the output from this query.

    SELECT 'ALTER DATABASE '+DB_NAME(database_id)+' MODIFY FILE ( NAME = '+name+' , FILENAME = ''G:'+SUBSTRING(physical_name, 3, LEN(physical_name))+''' );'

    FROM sys.master_files

    WHERE database_id > 4

    ORDER BY database_id, file_id

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

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

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