Changing Locations of DataFile

• Perry Whittle

  SSC Guru

  Points: 233917

  More actions

  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" 😉

• OnlyOneRJ

  Hall of Fame

  Points: 3894

  More actions

  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

• saidapurs

  Hall of Fame

  Points: 3477

  More actions

  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

• Perry Whittle

  SSC Guru

  Points: 233917

  More actions

  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" 😉

• scogeb

  SSCrazy

  Points: 2059

  More actions

  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:

• Paul Clark-418949

  Old Hand

  Points: 378

  More actions

  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?..

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• OnlyOneRJ

  Hall of Fame

  Points: 3894

  More actions

  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

• saidapurs

  Hall of Fame

  Points: 3477

  More actions

  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

• OnlyOneRJ

  Hall of Fame

  Points: 3894

  More actions

  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

• Perry Whittle

  SSC Guru

  Points: 233917

  More actions

  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" 😉

• OnlyOneRJ

  Hall of Fame

  Points: 3894

  More actions

  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

• Perry Whittle

  SSC Guru

  Points: 233917

  More actions

  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" 😉

• OnlyOneRJ

  Hall of Fame

  Points: 3894

  More actions

  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

• Paul Clark-418949

  Old Hand

  Points: 378

  More actions

  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...