Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

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.