Tuesday, April 1, 2008

FAQ : How to move a physical file (MDF or LDF) from one location to another in SQL Server 2005

In SQL Server 2000, if you want to move a physical file of a database from one location to another , you have to detach and attach the database. In SQL Server 2005, this process has been made very simple, you need to take the database offline, alter the file path with the new one using Alter Database command and copy the database file to new location manually and finally take the database online. Simple

--Step 1 : Create Database
Create Database TestMoveFile and check the database file location
Select *From Sys.master_files where database_id=db_id('TestmoveFile')
--Step 2 : Alter Database and Set the db to offline
Alter Database TestMoveFile Set Offline
-- Step 3 : Move the physical file to new location
--Move the file to new location using dos command or Windows GUI

--Step 4 : Alter the database file path using Alter Database command
Alter Database TestMoveFile Modify File(Name='TestMoveFile',FileName='c:\TestmoveFile.mdf')

-- Step 5 : Set the database Online and check the file path
Alter database TestMoveFile Set Online
Select *From Sys.master_files where database_id=db_id('TestmoveFile')

Drop database TestMoveFile


Anonymous said...

Thank you for pointing out the different process of moving files between versions, big help!

Alexis said...

My friend advised me-.mdf file recovery.As he said this tool recovers sql files and to my surprise tool solved like issues very easy.Moreover tool was free as far as I know and it could compatible with all supported versions of Microsoft Windows operating system, it opens Microsoft SQL databases.

Locations of visitors to this page