Moving SQL Server datafiles

I’ve recently had to work with our IT Department in changing the layout of the SQL Server instance created when they installed Service Manager – at the time of the install the SAN was offline so everything ended up being dumped on the C:\ drive. After some very poor performance from Service Manager they asked me to look at why it was running so slowly. Documented below are the steps necessary to move database and logfile locations in case you are presented with the same issues.

  1. Get the current physical and logical locations

USE master

GO

SELECT name AS LogicalFileName, physical_name AS FileLocation , state_desc AS Status

FROM sys.master_files

WHERE database_id = DB_ID(‘ServiceManager’);

which gave me:

LogicalFileName            FileLocation                                                                                Status
SM_DATA                     C:\Program Files\Microsoft SQL Server\MSSQL11.SCSMR2\MSSQL\DATA\ServiceManager.mdf                                                                                ONLINE
SM_LOG                       C:\Program Files\Microsoft SQL Server\MSSQL11.SCSMR2\MSSQL\DATA\ServiceManagerlog.ldf                                                                              ONLINE

2. Take the database offline:

USE master

GO

ALTER DATABASE ServiceManager SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

3. Move the data and log files physically to their new locations using the Windows OS

4. Use ALTER DATABASE to MODIFY the filename for each file that has been moved – anly one file can be done at a time

USE master

GO

ALTER DATABASE ServiceManager

MODIFY FILE

( NAME = SM_DATA,

FILENAME = ‘E:\Microsoft SQL Server\MSSQL11.SCSMR2\MSSQL\DATA\ServiceManager.mdf’); — New file path

USE master

GO

ALTER DATABASE ServiceManager

MODIFY FILE

( NAME = SM_LOG,

FILENAME = ‘F:\MSSQL11.SCSMR2\MSSQL\DATA\ServiceManagerlog.ldf’); — New file path

5. Set the DATABASE ONLINE

USE master

GO

ALTER DATABASE ServiceManager SET ONLINE;

6.Verify the new physical locations

USE master

GO

SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status

FROM sys.master_files

WHERE database_id = DB_ID(‘ServiceManager’);

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s