Moving User-Defined SQL Server Databases files - within the instance

Sometime we need to move database files created for our databases from existing location to a new location. The reason could be; 1. Not enough space in the existing volume. 2. For performance improvement. Whatever the reason, methods for moving files are different based on the database type, whether it is system or user-defined and the scope, whether it is within the instance or not. Note that this post talks about moving files of a user-defined database within the instance.

What are the steps for doing it. First of all, you need to cancel all transactions that are being run. Then you need to bring the database offline. It can be done using one statement.

USE master;
GO

ALTER DATABASE AdventureWorks SET OFFLINE
WITH ROLLBACK IMMEDIATE;

Once it is done, move files manually and modify the database, changing the file path of its files.

-- You need to execute this for each and every file you need to move
-- Name is logical name, Filename is physical name.
-- If you do not know the logical name
-- execute sp_helpfile against your database.
ALTER DATABASE AdventureWorks MODIFY FILE 
(Name = AWDataFile, Filename = '\AWDataFile.mdf');
ALTER DATABASE AdventureWorks MODIFY FILE 
(Name = AWLogFile, Filename = '\AWLogFile.ldf');

Then all you have to do is, bring the database online.

ALTER DATABASE AdventureWorks SET ONLINE;

You may need to take a copy of these file without taking the database offline and then attach them to another instance. As you know, database files cannot be copied while they are being used but you can use something like HoboCopy for taking the copy. Read more at http://dinesql.blogspot.com/2015/05/how-to-take-copy-of-database-files.html.