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

5 years ago

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.

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

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');

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.

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.

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)

File format, SQL Scripts, SQL Server

What Part of the SQLDump Suggest Database Corruption? – Interview Question of the Week #188

Which is Optimal – TOP 0 OR WHERE 1 = 2? – Interview Question of the Week #190

4 Comments. Leave new

Hank Freeman
November 14, 2019 9:25 am
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
abdul hafeez kalsekar
June 25, 2020 2:24 pm
Thanks for this . You contribution is really helpful
Reply
Raj Pawar
October 2, 2020 6:30 pm
After running the last step
ALTER DATABASE [Test_3.6_log] SET ONLINE;
This error comes
Msg 5011, Level 14, State 5, Line 18
User does not have permission to alter database ‘Test_3.6_log’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 18
ALTER DATABASE statement failed.
Reply
SaiMani
January 4, 2022 11:36 pm
Thanks for this . Very helpful info.
Reply