Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to move physical location of all my databases (around 20) to new location.

sys.master_files (physical name) column gives me path of databases.
  1. If I copy physical files to new location and change path here then databases will be moved to new locations?

  2. If above is not possible then what is the best way to move databases in bulk?

Thanks.

share|improve this question

Yes it is possible, please check this way (Source: TechNet)

Thank a Full backup first!

Issue an ALTER DATABASE command using the SET OFFLINE option to take the database off-line.

ALTER DATABASE database_name SET OFFLINE;

Move the files to their new locations. Issue the appropriate ALTER DATABASE commands using the MODIFY FILE option to tell SQL Server where the new locations are.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

Issue an ALTER DATABASE command using the SET ONLINE option to bring the database back on-line.

ALTER DATABASE database_name SET ONLINE;

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.