Move DB files

For some reasons, you may need to change DB file location from one to another.
As i can think of, there are at least 3 wways.
1.detach DB, move files, attach
2.backup/restore(indicate a new location when restoring)
3.This is a way I prefer to use but seems it does not have a formal name.
scripts for your reference
use USSut003
go
–tell SQL server, you wanna change the physical place for one file(could be log or data)
ALTER DATABASE USSut003
MODIFY FILE (NAME = ‘USSut003_PG_DAT_1’, FILENAME = ‘J:\BIILAND\MSSQL\DATA\USSut003.MDF’)

use master
go

ALTER DATABASE USSut003
SET offline

–Physical move data from one place to another

ALTER DATABASE USSut003
SET online

All done.

Advertisements