Post reply

SQL Server - Moving Database Components

  • June 2, 2004 at 9:02 am

    #163673

    Hello Team!  
     
    I know this is probably an easy question for most of you, but for someone just new in the job iit is something I would like more direction on before I start moving on.  So any reply you can provide is appreciated. 
     
    I am running out of space on some drives with Data, logs, and Index's.  We are in a windows 2000 and SQL Server 2000 environment.   I have other drives that have more space.  Questions:

    1.  How do I move a DB to another drive? reloacate and change config .. is there any timing or access restrictions I need to consider?  Any problems I should expect?  Any specific commands or gui interfaces you would recommend I use?

    2.  What is the cost when the DB and log are not on the same drive?

    3.  How do I estimate how long the process will run (Relocate and reconfig)?

    4.  Do I need to lock users out while this is going on?

  • June 2, 2004 at 9:14 am

    #508675

    1. Actually very simple. Get all users and processes in a database out of it, back it up and restore via GUI restore and change to default location to the new drive. Its as simple as that. This will work for all application databases just not the system ones. If you change to default location of the system dbs you have to go in and update the configuration so when SQL Server starts it will know where they are. BUT those dbs are small, master, msdb, model, TEMPDB.

    2. Sometimes there is a cost to pay. If you have a low end db with not much activity I would say it doesn't matter... others may suggest otherwise though.

    3. Length of time, depends on the db size... a backup and then restore...

    4. Yes, you need to keep users out during this process. 

  • June 2, 2004 at 12:08 pm

    #508702

    There's an even easier way to move database files. But you should still have them backed up first.

    Detach the database(s). (You can use either Enterprise Manger - easiest or TSQL commands).

    Move the database files (.mdf and .ldf) to their new location.

    Attach them. Using Enterprise Manger, the GUI will ask you where the files are. Using TSQL tell it where the files are.

    For TSQL commands, check out the Books OnLine (BOL) which comes with SQL Server. It can be found at Start>Programs>Microsoft SQL Server>Books OnLine. Use the index tab and enter DETACHING DATABASE FROM SERVER.

    -SQLBill

  • June 2, 2004 at 12:41 pm

    #508710

    Thank You so much!

  • September 21, 2004 at 5:20 am

    #523774

    Is there a way to do this and keep the database online? Ie not using attach DB?

  • September 30, 2005 at 11:26 am

    #594383

    Nope.

    You have to take the db down in order to do it.

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

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