Thursday, November 11, 2010

How to Move Sql Server 2005/2008 Mirrored Database datafile

In order to move datafiles of a mirrored database from one location to an other (requires server offline):

1-) Check datafile physical locations:

select * from master..sysaltfiles where filename like '%MYDB_Data%'

2-) Run similar alter database command to rename physical datafile names.

alter database MYDB MODIFY FILE (NAME=MYDB_Data01,FILENAME='D:\mypath\MYDB_Data01.ndf');

Following similar message will come up means change will apply when server is restarted

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

* Shutdown sql service
* copy physical files to new location
* rename old ones to some dummy name
* Start sql services

4-) If everything is fine, you can delete old files.

Note: You can not take mirrored database offline, that is why we shutdown sql services and copy datafiles while system is offline.

No comments: