Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Moving Database Physical Files to New Locations

By: FYIcenter.com

(Continued from previous topic...)

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...)

  1. What is a database?
  2. What is the simplest way to create a new database?
  3. How to set the current database?
  4. How to delete a database?
  5. Why I am getting this error when dropping a database?
  6. How to get a list all databases on the SQL server?
  7. Where is my database stored on the hard disk?
  8. How to create database with physical files specified?
  9. How to rename databases?
  10. Why I am getting this error when renaming a database?
  11. What are database states?
  12. How to set a database state to OFFLINE?
  13. How to move database physical files?
  14. How to set database to be READ_ONLY?
  15. How to set database to be SINGLE_USER?
  16. What are system databases?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...