Solved

Moving a mdf and log file from C:\ to another drive

Posted on 2014-11-10
16
199 Views
Last Modified: 2014-11-12
I inherited responsibility for a SQL Server not long ago, and have been migrating a bunch of Access back-ends to the server.  This has been working well.

This morning, the C:\ drive on the server was "full", so I started digging and found an old database that is still in use, but which was installed in the C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA folder rather than on the network server.  Apparently, our accounting department has been adding data to this database and it and the ldf file are taking up nearly 50 on that computer.

How do I go about detaching that mdf and ldf, moving those files to the another drive, and then reattach it?
0
Comment
Question by:Dale Fye (Access MVP)
  • 6
  • 5
  • 5
16 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40432862
You've got the process exactly right - you detach, you move, and you re-attach,

Here's some step-by-step instructions: http://sqlserverzest.com/2013/08/10/sql-server-how-to-move-database-files-data-file-and-log-file-from-one-drive-location-to-the-other/
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40432863
If you want to do it without code, try this link: http://plataan.typepad.com/microsoftdynamics/2008/06/how-can-i-move.html
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40432881
Thanks, Phillip.  I'll give it a try.

It was the step-by-step procedures I was looking for.  

This is not something I want to screw up.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40432886
As long as you don't delete the files, you shouldn't be able to mess it up too badly!

You won't be able to move the files if someone is still accessing them, and you can't re-attach them with the old file names once you have moved them.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40432889
You may also want to do a FULL backup before you start.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40432967
Detach and attach is an obsolete method for moving files within the same instance.  Instead, the newer, preferred method is to update the catalog entries for the db, set the db offline, physically copy/move the files, and bring the db back online.
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40432986
If you want to use that method, the T-SQL code is in this article: http://technet.microsoft.com/en-us/magazine/gg452698.aspx
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40433079
Btw, this statement in that article:
"You can move only one file at a time in this manner."
is false.  You could move every file in a db at one time in that manner if you need to.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40433311
Can I retain the same database name with either of these techniques?

I've got several applications which will need to have the connection strings on all of the linked tables and in code changed if I have to actually rename the database.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40433325
Yes.  The modify catalog technique implicitly requires the same name.  With detach, just be sure to specify the same db name when you attach the db.

Again, I'd urge you not to detach the db.  If there are certain error(s) in the db, it will not attach again, period, barring some specialized software to fix the db.  Also, the security changes that now occur when a db is detached can also prevent reattaching the db files under certain conditions.  To me, it's not worth those risks when I have a much better method available.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40433518
Scott, what had me confused was the syntax at Step 3.

3. Move one file at a time to the new location by typing the following:

ALTER DATABASE Personnel
MODIFY FILE ( NAME = Personnel_Data, FILENAME =
"C:\Data\Personnel_Data.mdf")
GO

I don't understand why it is using "Personnel" as the database name
but using "Personnel_Data" in the Modify File statement.  If I understand your previous comment, I should be able to:

ALTER DATABASE Accounting_Ledger
MODIFY FILE ( NAME = Accounting_Ledger,
                          FILENAME = "D:\Data\Accounting_Ledger.mdf")
MODIFY FILE ( NAME = Accounting_Ledger_log,
                          FILENAME = "D:\Data\Accounting_Ledger_log.ldf")
GO

And that will actually take care of moving all of the data and log files from:

C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL\DATA\
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40433579
The "NAME =" within the modify file is the logical file name, not the db name.  You can get a list of logical file names, and their corresponding physical file, using this command:

EXEC sp_helpfile


Sorry, you can't specify more than new file name in a single ALTER statement, but you can issue multiple ALTER statements before taking the db offline.

ALTER DATABASE Personnel MODIFY FILE ( NAME = LogicalName1, FILENAME = ... )
ALTER DATABASE Personnel MODIFY FILE ( NAME = LogicalName2, FILENAME = ... )
ALTER DATABASE Personnel MODIFY FILE ( NAME = LogicalName3, FILENAME = ... )

ALTER DATABASE Personnel SET OFFLINE

--copy files using your preferred method (Windows interface, from within SQL, batch utility, etc.)
--[you can move them instead if you have to, such as not enough disk space to hold both sets of files]

ALTER DATABASE Personnel SET ONLINE

--verify everything is working, then
--delete original files using your preferred method (Windows interface, from within SQL, batch utility, etc.)
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40437068
Scott,

I successfully moved 4 smaller mdf and log files from the C:\ to D:\ drive using this technique.  The C: Drive is now up to 12GB of free space.  However, not all is well yet.

When I attempt to move several of the larger databases, SQL seems to be locking up on me.  After more than 8 minutes, the SET OFFLINE command is still not working.

Is there a chance that an external application linking to that data is preventing me from taking it off line?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40437631
Yes.  By default, you can't offline a db that is in use.  You'll need to "tell" SQL to kill any active tasks and then do the offline.

ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40437662
I'll just wait until tonight.  Fortunately, this is not an OLTP database.  It is more of a data warehouse and reporting repository, which normally gets updated nightly and queried all day.

Thanks for the help.

Dale
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40439140
Thanks, guys.  I appreciate the recommendations.  The renaming worked great.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question