Move SQL DB to new Drive

• Glen Porter

  Newbie

  Points: 1

  More actions

  August 24, 2006 at 10:30 pm

  #61085

  I'm not an expert but what i'm trying to do is move to move a couple of SQL db from from drive to another so SQL will look to the DB on the new drive.  If I try and restore and set the path to a different drive I get a ODBC-SQL State 420000 Access denied as it can't get exclusive access.

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  August 24, 2006 at 11:55 pm

  #656769

  Detach database, move files to a new location and attach it again.

  _____________
  Code for TallyGenerator

• Jpotucek

  SSCoach

  Points: 18583

  More actions

  August 25, 2006 at 6:18 am

  #656803

  use master

  go

  exec sp_detach_db 'YourDB'

  go

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

  Move the data and log files to their new locations.

  use master

  go

  exec sp_attach_db 'YourDB'

     , 'PATH_TO_YourDB_DATAFILE\YourDB_datafile.mdf'

  , 'PATH_TO_YourDB_LOGFILE\YourDB_logfile.ldf'

  go

  You can find lots of usefull info here too:

  http://www.cryer.co.uk/brian/sqlserver/sqlsvrhowto.htm