Different ways to move Sql Server file location

Different ways to move Sql Server file location

For space issue on drive , performance load balancing or some other reasons, we may have to move physical file of the database to another location.

Following are the different ways to move data/log file physical location:

1. Make database offline move file location alter database with new location.

Find the location of the files

select name, physical_name,*
from
sys.master_files
where database_id=DB_id(‘Vin’)

  • Get the virtual name of the file to be moved.

use Vin

go

ALTER
DATABASE vin MODIFY
FILE (NAME =
‘vin’
,
FILENAME
=
‘c:\vin.mdf’)

 

–you will get below message

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

 

ALTER
DATABASE vin set
offline

Now once you make the database into “OFFLINE” mode you can physically move the file location to the target location(specified in ALTER DATABASE command). Then make database ONLINE again.

ALTER
DATABASE vin set
online

–You are done

2. Detach/Attached

Clear all connection from the database

ALTER
DATABASE Vin SET
single_user
WITH
rollback
immediate

Detach database

Move the file location

Attach database

3. Backup/Restore

Backup/Restore option is good when you want to move your database from one server to another.

Great Reference for this:

http://www.petri.co.il/move-db-files-in-sql-server-taking-db-offline.htm


About these ads
This entry was posted in Backup and Restore, Troubleshooting and tagged , . Bookmark the permalink.

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