relocating datafiles

• Michael Yukas

  Grasshopper

  Points: 11

  More actions

  February 2, 2006 at 8:58 am

  #112373

  I am new to MSSQL200 and would like to know how to relocate a datafile I can not seem to find any good documentation on how to do this.

   

  Thanks

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  February 2, 2006 at 9:18 am

  #618429

  Hello Michael,

  What do you mean by relocating?

  You mean to move the files from one directory to another directory. if so, you need to detach the database and then copy over the files to the new directory and then re-attach them.

  Thanks and have a nice day!!!

  
  Lucky

• Michael Yukas

  Grasshopper

  Points: 11

  More actions

  February 2, 2006 at 9:26 am

  #618431

  that is exactly what i would like to do.  what is the code to do that

   

  thanks

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  February 2, 2006 at 9:44 am

  #618435

  look up sp_Detach_db, and sp_Attach_db in books online.

• bledu

  SSChampion

  Points: 12335

  More actions

  February 3, 2006 at 5:30 am

  #618618

  or just go onto enterprise manager.right click on the actual database you want to move, goto all tasks -> Detach Database

  when you have moved it, right click on the databases node ->all tasks ->Attach Database.

  makesure to specify the correct database owner on the screen that comes up

  ---

  Everything you can imagine is real.
  

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  February 3, 2006 at 10:55 am

  #618745

  Here's a good link that may help you as well:

  http://support.microsoft.com/kb/224071/en-us

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  February 3, 2006 at 12:45 pm

  #618779

  Which database do you want to move? Moving system databases (especially MASTER) is not like moving user databases.

  -SQLBill

• Michael Yukas

  Grasshopper

  Points: 11

  More actions

  February 3, 2006 at 12:48 pm

  #618780

  i have move the database thank you all

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  February 3, 2006 at 12:50 pm

  #618781

  Just for your records...here are good resources/directions for moving your system databases:

  By Steve Jones

  Moving TEMPDB:

  http://www.sqlservercentral.com/columnists/sjones/atemporarymove.asp

  Moving MASTER:

  http://www.sqlservercentral.com/columnists/sjones/moveyourmaster.asp

  Moving MSDB:

  http://www.sqlservercentral.com/columnists/sjones/movingmsdb.asp

  By Chris Hedgate

  http://www.sqlservercentral.com/columnists/chedgate/movingsystemdatabasesachecklist.asp

  -SQLBill