SQL Server FAQ - Moving Database Physical Files to New Locations

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How to move database physical files?

If you want to move database physical files to a new location, you can use the "ALTER DATABASE" statements to bring the database offline, and link it to the files at the new location. The following tutorial gives you a good example:

ALTER DATABASE FyiCenterComData SET ONLINE
GO

USE FyiCenterComData
GO

CREATE TABLE Links (Name NVARCHAR(32))
GO

ALTER DATABASE FyiCenterComData SET OFFLINE
GO

Now it is safe to move the database physical files to a new location:

1. Run Windows Explorer
2. Create a new directory: c:\temp\data
3. Drag and drop c:\temp\FyiCenterData.mdf to c:\temp\data
3. Drag and drop c:\temp\FyiCenterLog.mdf to c:\temp\data

Go back to the SQL client program and run:

ALTER DATABASE FyiCenterComData 
   MODIFY FILE (NAME = FyiCenterData, 
      FILENAME = 'C:\temp\data\FyiCenterData.mdf')
GO
The file "FyiCenterData" has been modified in the system 
catalog. The new path will be used the next time the 
database is started.

ALTER DATABASE FyiCenterComData 
   MODIFY FILE (NAME = FyiCenterLog, 
      FILENAME = 'C:\temp\data\FyiCenterLog.ldf')
GO
The file "FyiCenterLog" has been modified in the system 
catalog. The new path will be used the next time the 
database is started.

ALTER DATABASE FyiCenterComData SET ONLINE
GO

USE FyiCenterComData
GO

SELECT type_desc, name, physical_name, size
   FROM sys.database_files
GO
type_desc name         physical_name                   size
ROWS    FyiCenterData  C:\temp\data\FyiCenterData.mdf  1280
LOG     FyiCenterLog   C:\temp\data\FyiCenterLog.ldf    128

SELECT name, type_desc FROM sys.tables
GO
name    type_desc 
Links	USER_TABLE

As you can see the physical files are moved properly, and the "Links" table is still there.

(Continued on next topic...)