How to move datafiles to a new drive in SQL Server

For one of the databases that I have to manage we were running out of space, so we got a shiny new 10.9 TB sized drive.

I was asked to move some files used by one database to this new drive. I decided to write up a little blog post just in case you ever need to do this so that you don't backup and restore (with move) because there is another way.

First create this test database with 3 data files and 1 log file, the data files will be in the C:\DB_Files directory

USE master
GO

CREATE DATABASE [TestMove] ON  PRIMARY
( NAME = N'TestMove', FILENAME = N'C:\DB_Files\TestMove.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
( NAME = N'TestMove2', FILENAME = N'C:\DB_Files\TestMove2.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
( NAME = N'TestMove3', FILENAME = N'C:\DB_Files\TestMove3.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestMove_log',
FILENAME = N'C:\MSSQL\DATA\TestMove_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
GO

Read more: Less than dot

Posted via email from .NET Info