Post reply

Changing Locations of DataFile

  • September 24, 2012 at 6:31 am

    #1541373

    runal_jagtap (9/24/2012)

    then Move the files manually from C Drive to the new location & then start the DB..

    I always copy the files then remove the originals when the database starts successfully

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

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

  • September 24, 2012 at 6:40 am

    #1541376

    Perry Whittle (9/24/2012)

    runal_jagtap (9/24/2012)

    then Move the files manually from C Drive to the new location & then start the DB..

    I always copy the files then remove the originals when the database starts successfully

    Ya Perry, me too agreee to this...:-)

    ************************************
    Every Dog has a Tail !!!!! :-D

  • September 24, 2012 at 6:53 am

    #1541380

    Hi Gila/Perry,

    Thanks for your response even i observed required less down time for this...

    Thanks & Regards

    Satish

  • September 24, 2012 at 6:59 am

    #1541383

    saidapurs (9/24/2012)

    Hi Gila/Perry,

    Thanks for your response even i observed required less down time for this...

    Thanks & Regards

    Satish

    No problem, the main thing is to not panic. If you get the situation where the database does not start, immediately check sys.master_files and then marry this up to the files in the physical OS.

    Remember, the database engine will accept whatever you type into the alter database command, if that path\filename doesn't exist when the database attempts to restart, you'll get an error 😉

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

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

  • September 24, 2012 at 11:00 am

    #1541526

    runal_jagtap (9/24/2012)

    Hello Everyone 🙂

    Finally i have been assigned this task to do next month..

    but they are still not sure that if i Change the Log File location of the database to other drive & also if i change the Tempdb log file location to other drive will it help improving performance??? :w00t:

    I have no answer as of now.. 🙁

    Using Performance Monitor, check your I/O latency on the drive you have the data, log and tempdb currently running on. If it is really bad, you should expect to see some improvements moving the files to seperate drives. Of course that depends on the drives you move them onto as well. Say the new drive is a 2 physical disk 7.2K raid 1 but the old drive was a 12 physical disk 15K raid 10, you might actually see worse performance after the move. :hehe:

  • October 3, 2012 at 6:52 am

    #1544879

    Can't a Detach/Attach also cause orphaned SQL users?

    Whereas using the alter table statements then offlining the DB and copying the files to the new location doesn't?

    And Wouldn't the Detach/Attach method also change your DB id in sys.databases, so you might lose backup history etc?..

  • October 3, 2012 at 7:04 am

    #1544887

    Paul Clark-418949 (10/3/2012)

    Can't a Detach/Attach also cause orphaned SQL users? .

    Not if detaching and attaching to the same instance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 7, 2012 at 11:49 pm

    #1546564

    Hi Perry Whittle,

    Did you goyt any chance to perform this at your end???:-)

    I am still waiting for the approval :w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • October 8, 2012 at 12:29 am

    #1546566

    Hi Runal,

    What pretty has shared documents that i tested in Test environment and also work on production its working fine....

    Thanks & regards

    Satish

  • October 8, 2012 at 12:34 am

    #1546567

    saidapurs (10/8/2012)

    Hi Runal,

    What pretty has shared documents that i tested in Test environment and also work on production its working fine....

    Thanks & regards

    Satish

    Hi Satish can you share the document here????

    exactly how did you perform it???

    that would help me, if could share it....

    ************************************
    Every Dog has a Tail !!!!! :-D

  • October 8, 2012 at 5:28 am

    #1546650

    runal_jagtap (10/7/2012)

    Hi Perry Whittle,

    Did you goyt any chance to perform this at your end???:-)

    I am still waiting for the approval :w00t:

    Hi, yes my tasks around this have been completed. What files are you moving, just user DBs or system DBs too?

    If system DBs which ones?

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

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

  • October 8, 2012 at 6:05 am

    #1546667

    Perry Whittle (10/8/2012)

    runal_jagtap (10/7/2012)

    Hi Perry Whittle,

    Did you goyt any chance to perform this at your end???:-)

    I am still waiting for the approval :w00t:

    Hi, yes my tasks around this have been completed. What files are you moving, just user DBs or system DBs too?

    If system DBs which ones?

    As of now i am asked to do other tasks.. so this task is on hold...

    Hey Perry can you just attach step by step processs which u did... so that while doing it at my end i wont miss any step..

    Also do let me know the effect after u have done this.. Was it helpfull for you????

    because when i will do it .. i will be asked to justify why i am doing this :w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • October 8, 2012 at 6:18 am

    #1546679

    runal_jagtap (10/8/2012)

    Hey Perry can you just attach step by step processs which u did... so that while doing it at my end i wont miss any step..

    Yes I can, but are you planning to do just user databases or system databases too?

    If system, which ones?

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

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

  • October 8, 2012 at 6:22 am

    #1546682

    Perry Whittle (10/8/2012)

    runal_jagtap (10/8/2012)

    Hey Perry can you just attach step by step processs which u did... so that while doing it at my end i wont miss any step..

    Yes I can, but are you planning to do just user databases or system databases too?

    If system, which ones?

    Was planning to move Log files of User databases & only TempDB ... what say??? i am a fresher so not sure whether i am correct:-)

    ************************************
    Every Dog has a Tail !!!!! :-D

  • October 8, 2012 at 6:54 am

    #1546700

    Hi Perry,

    This might save some work:

    I have already documented the steps for this in my Blog.

    Let Runal know if the below links are OK

    Moving TempDB Files - Just look at the Tempdb section.

    http://paulmjclark.blogspot.co.uk/2011/10/change-system-database-location-on-sql.html%5B/url%5D

    Moving User Database Files:

    http://paulmjclark.blogspot.co.uk/2012/10/moving-user-database-files.html

    Hope this helps...

Viewing 15 posts - 31 through 45 (of 69 total)

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