Jul 01 2015

How to Move Datafiles to New Disk in MsSQL Server 2008

Category: AdministrationFatih Acar @ 15:38

If database data disk space is full or you have another problem about of disk, you can move datafiles to new disk in MsSQL Server 2008 database.

Show Current Paths and Names

use master
go
select name,physical_name from sys.master.files where database_id = DB_ID(‘DATABASENAME’)
dbf1 D:\OLDLOCATION\disk_1.mdf
dbf1_log D:\OLDLOCATION\disk1_log.ldf
go

Shutdown Database

use master
go
alter database DATABASENAME modify file(dbf1,filename=’E:\NEWLOCATIOB\disk_1.mdf’);
go
alter database DATABASENAME modify file(dbf1_log,filename=’E:\NEWLOCATIOB\disk1_log.ldf’);

Move Datafiles to New Disk

Copy Paste datafiles to new location

Open Database

use master
go
alter database DATABASENAME set online

You can look database file locations with sql query or SQL Server Management Studio interface.

10,150 total views, 5 views today

Tags: Database Administration, MsSQL Server

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.