Solved

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

Posted on 2014-11-10
16
196 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
 
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:ScottPletcher
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:ScottPletcher
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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:ScottPletcher
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:
ScottPletcher 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:ScottPletcher
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 HA Cluster, Utility Controller No Data 11 59
Complex SQL 10 33
Azure SQL DB? 3 16
Test a query 23 15
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now