Do you need to move one or more of your SQL Server database files? For example, are the two SQL Server files (mdf and ldf) located on a C: drive and you wish to move them to a D: drive? Perhaps your C: drive has limited space. Perhaps you want to ensure that your database is being backed up properly by having the database files located in a folder that is being backed up regularly.

There is a YouTube video on this topic. It is called Move user Database (.mdf and .ldf files) to another drive in SQL Server. It’s in a series called SQL with Manoj. He also has a website with the same information. The article is called SQL DBA – Move user Database (.mdf & .ldf files) to another drive.The general steps are as follows, assuming you had a database called myDataB. You can substitute your actual database name into the SQL script. You need to execute the first parts of this script by selecting the code with the mouse and clicking Execute in SQL Server Management Studio. To see if the sata files are on the right drive, substitute your database name into the first line of code below. Select that line with the mouse and Execute it with the Execute button. If the files are in the wrong directory (drive and folder) then follow the lines of code below. Set it offline and then manually cut and paste the two files with Windows Explorer. Don’t forget to stop and move the two files when you get to — move files ! in the script below.

select * from myDataB.sys.database_files
-- physical_name column shows database in the C drive.

ALTER DATABASE myDataB SET OFFLINE
GO

-- move files !

ALTER DATABASE myDataB
MODIFY FILE (
	NAME = 'myDataB',
	FILENAME = 'D:\MyData\MSSQL\Data\myDataB.mdf'
)

ALTER DATABASE  myDataB
MODIFY FILE (
	NAME = 'myDataB_log',
	FILENAME = 'D:\MyData\MSSQL\Data\myDataB_log.ldf'
)

ALTER DATABASE myDataB SET ONLINE
GO

select * from myDataB.sys.database_files

For more information have a glance at our post called SQL Server Recovery Pending.

There are some more instructions for moving your databases from one location to another. Have a look at the article called SQL Server Move Database Files Step By Step over at mssqltips.com.