Search This Blog

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,
MOVE the files TO the new location and map it .

Here is an example


 

EXEC
sp_helpDB
myDB


 

Note the filenames AND the physical paths


 

ALTER
DATABASE MyDB SET
ONLINE

GO

ALTER
DATABASE MyDB SET
OFFLINE

GO


 

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


 

1. Run Windows Explorer

2. MOVE the files TO new location


 


run:


 

ALTER
DATABASE MyDB


MODIFY
FILE (NAME = MyDB,


FILENAME
=
'C:\newpath\data\MyDB.mdf')

GO

The file "MyDB" has been modified in the system
catalog. The new path will be used the next
time the database
is
started.


 

ALTER
DATABASE MyDB


MODIFY
FILE (NAME = FyiCenterLog,


FILENAME
=
'C:\newpath\data\FyiCenterLog.ldf')

GO

The file "MydbLog" has been modified in the system
catalog. The new path will be used the next
time the database
is
started.


 

ALTER
DATABASE MyDB SET
ONLINE

GO


 

Now verify the new path


 

EXEC
sp_helpDB
myDB


 

No comments: