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

Published by

Derek Dai

focusing DB, Big Data and BI tech.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s