[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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

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
Dale Fye
Asked:
Dale Fye
  • 6
  • 5
  • 5
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you want to do it without code, try this link: http://plataan.typepad.com/microsoftdynamics/2008/06/how-can-i-move.html
0
 
Dale FyeAuthor Commented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You may also want to do a FULL backup before you start.
0
 
Scott PletcherSenior DBACommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Dale FyeAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Dale FyeAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Dale FyeAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Dale FyeAuthor Commented:
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
 
Dale FyeAuthor Commented:
Thanks, guys.  I appreciate the recommendations.  The renaming worked great.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 6
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now