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
