Moving DB file

• Nita Reddy

  SSCarpal Tunnel

  Points: 4177

  More actions

  October 27, 2020 at 5:28 pm

  #3801778

  I have Always ON setup between Server_A and Server_B (Primary server is Server_A and DR server is Server_B)

  I have DB called ManagementDB which has 3 Data file 2 on D drive and 1 on C drive.

  I need to moved Data file from C to d Drive. Since it has Always on Setup I am not able to Detach the DB.

  So how can I can move the data file from C drive to D drive??

  The DB size is 500 GB.

   

  Regards,

  Nita

• ScottPletcher

  SSC Guru

  Points: 100957

  More actions

  October 27, 2020 at 5:38 pm

  #3801792

  You wouldn't want to detach the db just to move a file.  Just ALTER the file location in SQL Server, take the db offline, copy the file, then bring the db back online.

  However, I don't think you can take a primary db offline that's in an AG either.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Michael L John

  One Orange Chip

  Points: 27045

  More actions

  October 27, 2020 at 6:29 pm

  #3801799

  1. Run the alter file location.  ALTER DATABASE XXXX MODIFY FILE( NAME = filename, FILENAME = 'D:\YourData.mdf')
  2. Fail it over to the secondary
  3. Stop SQL on the now secondary, formerly the primary.
  4. Copy and paste the file from the old to the new location
  5. Rename the file in the old location, just in case!
  6. Start SQL Server
  7. Make sure the database comes online and is syncing.
  8. Fail the AG back

   

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/