Skip to content

Scripts to Move File Location of MDF and LDF in Microsoft SQL Server Management Studio

by on November 10, 2014

Occasionally, logs or databases may outgrow their original location and need to be moved, or perhaps were never in the proper location to begin with. This often happens when you install SQL Server with defaults, and everything is created on the C: drive.

Here is a quick set of scripts to help move the files for the database and point the database to the new file locations.

–Detach the database

–first
USE master;
GO
— Important! We need to drop the existing connections.
ALTER DATABASE TestMovingMDF SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db @dbname = N’TestMovingMDF’;
GO

–Copy the files

–second – copy mdf/ldf to new location

–Reattach .mdf and .ldf files in new location

–second————————————————–
USE master;
EXEC sp_attach_db @dbname = N’TestMovingMDF’,
@filename1 = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test\TestMovingMDF.mdf’,  –path to .mdf
@filename2 = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test\TestMovingMDF_log.ldf’;  –path to .ldf
GO

Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: