How to Move SQL Server MDF and LDF Files? – Interview Question of the Week #189

Question: How to Move SQL Server MDF and LDF Files?

Answer: I recently received this question during my Comprehensive Database Performance Health Check. If you read the older blogs or search on the internet, you will find a solution where people are talking about Detach and Attach method. However, it is an old method and it is not necessary to follow the same method now.

How to Move SQL Server MDF and LDF Files? - Interview Question of the Week #189 physicalrename-1

I personally use the following method to move the data and log files for my customers. Let us learn that step by step.

Before continuing to this blog post, you should read the following two blog posts as they will be useful to get logical and physical file names.

Let us assume that we want to move database SQLAuthority from their D drive to E drive on my machine.

Step 1: Original Location

Solarwinds

First, we will get the location of the physical files using the following command.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'SQLAuthority');

How to Move SQL Server MDF and LDF Files? - Interview Question of the Week #189 physicalmove1

Step 2: Take Database Offline

You can take the database offline running following command.

ALTER DATABASE SQLAuthority SET OFFLINE;

Step 3: Move Files – MDF and LDF

Now in this step, you will have to move your database files from the original location to the new location.

How to Move SQL Server MDF and LDF Files? - Interview Question of the Week #189 physicalmove2

Step 4: Change the file location inside SQL Server

Running following command will change the location of the file for SQL Server.

ALTER DATABASE SQLAuthority
MODIFY FILE ( NAME = SQLAuthority_Data, FILENAME = 'E:\data\SQLAuthority.mdf' );
ALTER DATABASE SQLAuthority
MODIFY FILE ( NAME = SQLAuthority_Logs, FILENAME = 'E:\data\SQLAuthority_Logs.ldf' );
GO

Step 5: Bring Database Online

Now run following command to bring the database online.

ALTER DATABASE SQLAuthority SET ONLINE; 

Step 6: Check the database file location

You can verify your database move by running following command.

How to Move SQL Server MDF and LDF Files? - Interview Question of the Week #189 physicalmove3

Well, that’s it. You do not have use older method of Detach or Attach anymore if you want to move your database.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

Solarwinds
, ,
Previous Post
What Part of the SQLDump Suggest Database Corruption? – Interview Question of the Week #188
Next Post
Which is Optimal – TOP 0 OR WHERE 1 = 2? – Interview Question of the Week #190

Related Posts

1 Comment. Leave new

  • Note to Team: For your review.
    This was a process/preventive strike on a database and server almost out of space on a server this schedule to be migrated.

    In this automated world we work in with DBA Tools, It is sometimes nice to review an issue resolution that works well and can be shared. In the past moving database file with DETACH and ATTACH has been fret with errors. While backing up a database and restoring the MDF and NDF files to another location also works. The below is a lessoned that should be a good review for anyone on the team. Needless to say it does not rise to the occasion of all the good work done on OLB, systems with mounting points or AO-AG, but the below technique should be at the very least a good basic read/review.
    Such is the case with below Ctask CTASK0261896 for Co# CHG0132034

    The issue: SPPATLSQL06 L-drive was 94% utilized and during the Freeze the server would have run out of operational space thus bringing down all database on that heavily used BI-Server.
    SystemName Description VolumeName DeviceID MediaType Size(GB) Free Space(GB) Free (%) Used (%)
    ———- ———– ———- ——– ——— ——– ————– ——– ——–
    before SPPATLSQL06 Local Fixed Disk Data3 H: 12 1331 316 24 % 76 %
    after SPPATLSQL06 Local Fixed Disk Data3 H: 12 1331 528 40 % 60 %
    before SPPATLSQL06 Local Fixed Disk DATA L: 12 1450 92 6 % 94 %
    after SPPATLSQL06 Local Fixed Disk DATA L: 12 1450 304 21 % 79 %
    name CurrentLocation state_desc
    Access_BA_Data L:\MSSQL\DATA\Access_BA.MDF ONLINE
    Access_BA_Log M:\MSSQL\LOGS\Access_BA_2.LDF ONLINE
    Access_BA_Index L:\MSSQL\DATA\Access_BA_1.NDF ONLINE
    — Inventory report of script from Tempdb
    Name FileSize(mb) SpaceUsed(mb) FreeSpace(mb) PCT_Used PCT_Free Disk Location and Filename
    Access_BA_Index 2,052 2,031 21 99 1 L:\MSSQL\DATA\Access_BA_1.NDF
    Access_BA_Data 215,489 192,664 22,825 89 11 L:\MSSQL\DATA\Access_BA.MDF
    Access_BA_Log 1,301 101 1,201 8 92 M:\MSSQL\LOGS\Access_BA_2.LDF
    The solution: Move an appropriately sized database to another data drive where it would fit on another drive thus freeing up NNN GB of space and in this case the [Access_BA] database MDF and NDF files. 217 GB.
    The technique:
    1. Put database [Access_BA] in Single user mode.
    ALTER DATABASE Access_BA SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    2. Backup database [Access_BA] as a safety precaution 30 mins elapsed time
    3. ALTER DATABASE Access_BA SET OFFLINE;
    4. Move Access_BA.MDF and Access_BA_1.NDF from L-drive to H-drive (229 GB file move) Elapse time 25 mins.
    5. ALTER DATABASE Access_BA
    MODIFY FILE ( NAME = Access_BA_Data, FILENAME = ‘H:\data\Access_BA.MDF’ );
    ALTER DATABASE Access_BA
    MODIFY FILE ( NAME = Access_BA_Index, FILENAME = ‘H:\data\Access_BA_1.NDF’ );
    –Message: The file “Access_BA_Data” has been modified in the system catalog. The new path will be used the next time the database is started.
    6. ALTER DATABASE Access_BA SET ONLINE;
    7. ALTER DATABASE [Access_BA] SET MULTI_USER WITH NO_WAIT
    8. Validate access to database.
    end.

    Hank Freeman | Senior SQL Server DBA
    Elavon – U.S.Bank

    Reply

Leave a Reply

Menu