SQL Server – move database files

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, that 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

That will detach your database from the files.

2. Move the database files from their current location to their new home. You’ll want to copy both 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 [CRM] ON
( FILENAME = N’D:\Data\MyAwesomeDatabase.mdf.mdf’ ),
( FILENAME = N’D:\Data\MyAwesomeDatabase.mdf.ldf’ )
FOR ATTACH
GO

4. Ta-dah! That’s it.

ps – 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.

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

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.