By definition SQL Server uses the install directory to store its data, unless you’ve taken care of that aspect during installation. The default directory is

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\DATA

Initially, the out of the box solution is absolutely fine, until you realize that SQL Server and its rapidly expanding databases are slowly eating up the disk space dedicated for your OS. That’s when you want to move your databases to another location. Luckily, that’s a rather easy procedure, easier than moving your iTunes database. Which is a complete pain. Take note, Apple.

The steps:

1. In SSMS, run the following:

USE MASTER;
GO
ALTER DATABASE MyAwesomeDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'MyAwesomeDatabase'
GO

This will detach your files from the database.

2. Move the database files from their current location to their new home. You’ll want to copy all files associated with the database, in our case the two files are:

MyAwesomeDatabase.mdf
MyAwesomeDatabaselog.ldf

3. To relink the files with your database, run the following:

CREATE DATABASE [MyAwesomeDatabase] ON
( FILENAME = N'D:\Data\MyAwesomeDatabase.mdf' ),
( FILENAME = N'D:\Data\MyAwesomeDatabase.ldf' )
FOR ATTACH
GO

4. Ta-dah! That’s it.

p.s. – the Internetz is a redundant space. There are two tutorials on this topic, one on Technet, the other one on SQLAuthority. As one would expect, the Microsoft tutorial is absolutely confusing and impossible to follow. The latter on the other hand is an excellent tutorial, as matter of fact you should spend some time reading some of the articles you find there. Full of good stuff.

p.p.s – Do not, I repeat, DO NOT run the re-attach script with a typo in the path or it WILL bring down the entire server. Ask me how I know.

Conclusion: Now you know how to move your database files.