I want to move database files to another folder, should I stop SQL Server service or not?

SQL Server uses at least two files for maintaining a database: a data file (extension mdf) and a log file (extension ldf). If the database is an OLTP database, it is not uncommon to see a large data file filling the partition used for holding it. If there is no more space to grow within the partition, either a new file in a different location needs to be added or file should be moved to a larger partition that has enough space. If we need to move the file, do we really have to shut down the service bringing all databases attached to the instance offline or can we do it without stopping the service?

As you know, database files cannot be copied or moved while they are being used, hence you will not be able to move files when the services is running. (There is a way of copying files while they are being used, read this for more info: ). However, in certain scenario, this is not always true. You do not need to shut down the service if you need to move a file within the instance (not detaching and attaching to another instance).

Here is an example;

-- Create a sample database
USE master;
GO

CREATE DATABASE [TestDatabase100]
ON  PRIMARY 
( NAME = N'TestDatabase100'
 , FILENAME = N'D:\My Documents\Database Folder\TestDatabase100.mdf')
LOG ON 
( NAME = N'TestDatabase100_log'
 , FILENAME = N'D:\My Documents\Database Folder\TestDatabase100_log.ldf')
GO

-- Bring the database offline before moving
ALTER DATABASE [TestDatabase100] SET OFFLINE;

/*
Now move files to new location manually
*/

-- Change file paths of the database using
-- ALTER DATABASE
ALTER DATABASE [TestDatabase100]
MODIFY FILE (NAME = N'TestDatabase100'
   , FILENAME = N'D:\My Documents\New Database Folder\TestDatabase100.mdf');

ALTER DATABASE [TestDatabase100]
MODIFY FILE (NAME = N'TestDatabase100_log'
   , FILENAME = N'D:\My Documents\New Database Folder\TestDatabase100_log.ldf');

-- Bring the database online
ALTER DATABASE [TestDatabase100] SET ONLINE;

-- Cleaning up
DROP DATABASE [TestDatabase100]
GO