Moving data file from to a new location

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  December 9, 2015 at 5:59 am

  #309983

  Hi All,

  Need some suggestion from experts.

  We are using Microsoft SQL Server 2012 (SP1) .

  Database has 6 data files and we have decided to move one of the data file to a new physical disk/driver.

  Question is which of below methods is better/safer/faster and what is the difference between below 2 approaches.

  Method-1. dettach & attach

  Method-2.

  ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

  ALTER DATABASE my SET OFFLINE;

  ALTER DATABASE my MODIFY FILE (Name = my_Data,Filename = 'D:\DATA\my.MDF');

  Manually move the file to new location

  ALTER DATABASE my SET ONLINE;

  ALTER DATABASE my SET MULTI_USER;

  Also, is it advisable to run CHECKPOINT; before perform the change.

  Note: we are taking backup before we making the above change.

  Thanks,

  -Sam

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  December 17, 2015 at 6:24 am

  #1846543

  alter database ... modify file is the supported way to move a database file.

  Just be careful what you're typing as the files new location, it will accept whatever you execute 😉

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

  "Ya can't make an omelette without breaking just a few eggs" 😉

• Y.B.

  SSChampion

  Points: 11649

  More actions

  December 17, 2015 at 7:23 am

  #1846570

  I use this simple query for INFO when I need to move files around. Maybe it's just me but I find it easier than digging through menus.

  SELECT name, physical_name, size, state_desc

  FROM sys.master_files

  WHERE database_id = DB_ID(N'Your_DB_Name')

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned