Changing Locations of DataFile

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 18, 2012 at 5:55 am

  #1539028

  Try it out in a test/dev environment first. Even if it's just your local machine.

  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

  September 18, 2012 at 5:57 am

  #1539031

  GilaMonster (9/18/2012)

  ---

  Try it out in a test/dev environment first. Even if it's just your local machine.

  yes Gila, thats the idea 🙂

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

• OnlyOneRJ

  Hall of Fame

  Points: 3894

  More actions

  September 24, 2012 at 3:24 am

  #1541306

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

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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 24, 2012 at 4:00 am

  #1541321

  It might. It might not. Depends on the current bottlenecks, the drive architecture and a whole lot of other factors.

  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

• saidapurs

  Hall of Fame

  Points: 3477

  More actions

  September 24, 2012 at 4:05 am

  #1541323

  Hi Peery & Gila,

  I test it in my local server getting error while executing below query

  Query :-

  ALTER DATABASE Test MODIFY FILE(name=Test_data,

  FILENAME='D:\Test\Data\Test.mdf')

  Error :- MODIFY FILE failed. File 'Test_data' does not exist.

  But log file is moved successfully...

  Please let me know where i did mistake.

  Thanks & Regards

  Satish Saidapur

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 24, 2012 at 4:15 am

  #1541327

  Is that the correct name of the data file?

  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

• saidapurs

  Hall of Fame

  Points: 3477

  More actions

  September 24, 2012 at 4:28 am

  #1541330

  Hi Gila,

  I gave correct name of database and now file has moved but after restart the database getting error

  Database not accessible....

  Thanks & Regards

  Satish Saidapur

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 24, 2012 at 4:40 am

  #1541332

  The location you set for either data or log does not match where you actually moved them to.

  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

• saidapurs

  Hall of Fame

  Points: 3477

  More actions

  September 24, 2012 at 5:12 am

  #1541344

  Hi Gail,

  I observed the files are not moved particular path, currently Data & log files are in 'C' drive itself.....

  When i executed below query get successful message

  Query :-

  ALTER DATABASE Test MODIFY FILE(name=Test,

  FILENAME='D:\Test\Data\Test.mdf')

  Message :- The file "Test" has been modified in the system catalog. The new path will be used the next time the database is started.

  Could you help me where i did mistake...

  Thanks & Regards

  Satish Saidapur

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 24, 2012 at 5:22 am

  #1541345

  No the files aren't moved for you. You have to go and move them yourself.

  As Perry said previously...

  • get the logical name and current path\filehame for each databases files that you wish to move. You may use this query

    select db_name(database_id), name, physical_name from sys.master_files

  • Create the statements that you will use to move the database files. Use this code as a template and fill in with the info from the query above (you need an alter statement for each database file)

    ALTER DATABASE mydb MODIFY FILE(name=logicalname,

    FILENAME='new path and drive\the originalfilename.extension')

  • Take a last check at your move statements and when happy execute them. The new paths will not take effect until the database next restarts.

    Note: Be extra careful here as sql server will accept whatever path\filename you type. If the path\filename doesn't exist when you start the database it will not come online!!

  • Take the database offline
  • copy not move the disk files to their new locations
  • bring the database online
  • once the database comes online remove the old disk files

  With tempDB, SQL recreates tempDB at the new location, but it can't exactly recreate your user DBs.

  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

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  September 24, 2012 at 5:56 am

  #1541356

  saidapurs (9/24/2012)

  ---

  Hi Gail,

  I observed the files are not moved particular path, currently Data & log files are in 'C' drive itself.....

  When i executed below query get successful message

  Query :-

  ALTER DATABASE Test MODIFY FILE(name=Test,

  FILENAME='D:\Test\Data\Test.mdf')

  Message :- The file "Test" has been modified in the system catalog. The new path will be used the next time the database is started.

  Could you help me where i did mistake...

  Thanks & Regards

  Satish Saidapur

  First run this query and post the results

  select name, physical_name from sys.master_files

  where database_id = db_id('test')

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

  "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:10 am

  #1541359

  saidapurs (9/24/2012)

  ---

  Hi Gail,

  I observed the files are not moved particular path, currently Data & log files are in 'C' drive itself.....

  When i executed below query get successful message

  Query :-

  ALTER DATABASE Test MODIFY FILE(name=Test,

  FILENAME='D:\Test\Data\Test.mdf')

  Message :- The file "Test" has been modified in the system catalog. The new path will be used the next time the database is started.

  Could you help me where i did mistake...

  Thanks & Regards

  Satish Saidapur

  I Think, that after you fire the query it replies that (The new path will be used the next time the database is started)

  Yes this is done.... now u see that the files are still in ur C drive yes.... they will be in C drive only..

  You have to take the Database Offline & then Move the files manually from C Drive to the new location & then start the DB..

  I hop this is the way it is dione..

  Gila & Perry - Please correct me if i am wrong:-)

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

• saidapurs

  Hall of Fame

  Points: 3477

  More actions

  September 24, 2012 at 6:15 am

  #1541364

  Hi Gail,

  Thanks, yes its working fine after move the database files but could you tell me what is the difference on Detach/attach process and above process.....??

  Thanks & Regards

  Satish Saidapur

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 24, 2012 at 6:28 am

  #1541369

  Just that you don't have to detach the DB (which you can't do in some cases)

  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

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  September 24, 2012 at 6:28 am

  #1541370

  saidapurs (9/24/2012)

  ---

  could you tell me what is the difference on Detach/attach process and above process.....??

  Thanks & Regards

  Satish Saidapur

  Detach removes the database from the system. Offli9ning the database keeps it attached to the server but allows manipulation of the database files.

  Under sql server 2000 detach was your only option for moving a user database

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

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