Database on the Move – Part I

No, I am not talking about the SQL server instance you have on your laptop.  For better or for worse, the location of your database files may need to move, even if the instance stays on the same server.  Why might MDF files need to move you ask? (Yes I am ignoring LDF files for now, there are discussion points specific to them, which I do not want to get into in this series of posts.)

Well here are a few reasons why:

  • Brand new storage is acquired and some, maybe even all, is assigned to your SQL server.  (I hear this actually happens, but not for me, yet.)
  • You inherit a server with all of the user databases on the C: drive.
  • The database files threaten to exceed that maximum storage for the drive they are on.

There are plenty of other reasons, but that provides at least a few.

As with so many things in SQL server, it is possible to relocate a database in more than one way.  Noted SQL Server expert, Pinal Dave (T|B) has an article here on moving by detaching and reattaching.  This method works and there is nothing wrong with it, I simply prefer to be different in my approach.

We are going to move the database, so there are some things we need to consider, hopefully most are obvious.  In order to move the database, the database MUST be offline otherwise Windows locks the MDF and LDF files because they are in use.  Additionally, would you really want to move a database while transactions are happening?  To pull from the blatantly obvious department, the database will be offline the entire time the file is being relocated.

This approach is simple and can be done entirely within T-SQL, as you will see.  I am also assuming the SQL instance and server are not changing.  Here are the steps:

Set the database offline

Alter the database

Enable xp_cmdshell – OK, OK everyone exhale.  I know there are security risks but the shell si only going to be open for as long as it takes to finish the relocation.  Additionally, Midnight DBA, Sean McCown (T|B) blogs here presenting the argument that xp_cmdshell is not evil.

Relocate the MDF file (remember we are not dealing with LDF files in this post)

Disable xp_cmdshell – see it wasn’t on for very long, now was it?

Set the database back on-line

Clean up

Have you noticed I stopped saying move the MDF file and started saying relocate?  There is a reason for that, I do not like move, my preference is copy, make sure it works, then delete.  This is also the reason for the clean-up step.

The use at your own risk, unwarrantied, genericized T-SQL looks like this:

–shut down the database

alter database AdventureWorks2008R2



–tell SQL server where to find the files when post relocation

ALTER DATABASE AdventureWorks2008R2

MODIFY FILE ( NAME = AdventureWorksLT2008R2_data, FILENAME = “F:\Data\AdventureWorksLT2008R2_data.mdf”) — Note driver letters may very, please check this


 — To allow advanced options to be changed.

EXEC sp_configure ‘show advanced options’, 1;


— To update the currently configured value for advanced options.



— To enable the feature xp_cmdshell.

EXEC sp_configure ‘xp_cmdshell’, 1;


 –copy the MDF file

exec xp_cmdshell ‘copy E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_data.mDF” “F:\data\AdventureWorks2008R2_data.mdf”‘

 alter database AdventureWorks2008R2

set online


 — To disable the feature xp_cmdshell

EXEC sp_configure ‘xp_cmdshell’, 0;


— To update the currently configured value for this feature.



 — To allow advanced options to be changed.

EXEC sp_configure ‘show advanced options’, 0;


— To update the currently configured value for advanced options.



Seeking Mentor(s)

In the relatively short time that I have been involved with the SQL Community, I have become aware that this group is unlike any other which I have run across in technology, as a whole this community wants to help whether they be everyday DBA or a “superstar” of SQL.  Well, it is time I seek that help, not in the form of a quick question via the awesome #SQLHelp hashtag, but career help.

I need a mentor, that is the long and short of it.  No less of a stalwart in the SQL Community than Paul Randal (T|B) has commented numerous times on the value of having a mentor.  Obviously, since I am writing this post, I agree.

As the person searching for a mentor, perhaps it is time to talk a little bit more about me.  If you simply want to see my work history, my profile on LinkedIn should suffice.  However, if you were to ask how I would describe myself, I would say I am an intelligent, quick learner who wants to know everything possible about SQL Server. There is no question that I am a geek, learning and understanding how SQL works is fun, when I can share my excitement with someone who gets where the excitement comes from.  I realize learning everything is a stretch, but I might I as well aim high and miss by a bit.  I think that I have reached the point of knowing enough to know I do not know very much, though I may be underselling my knowledge.

At the risk of sounding like a bad on-line dating profile, what am I seeking.  You may have noticed that the title of this post implies more than one mentor, that is because I like the idea of having a mentor or two, perhaps even someone local and someone national.  My mentor needs to be passionate about SQL and sharing their knowledge.  However, it is critical that my mentor(s) can help to guide me in terms of what I need to learn next, from a general knowledge and a career step perspective.

Where I think I want to go is to be a data platform engineer who has understanding of the whole data system.

In what do I want to be competent?  In other words, these are skills/technologies that I want to be conversant in, able to troubleshoot a bit, but not have them be my go to skills.

  • Networking
  • Hardware
  • SharePoint
  • Other databases (Oracle, MySQL, etc.)

In what do I want to be functional?  These are skills in which I am capable of being hired to fill the role, even though they are not my primary skills.

  • Virtualizaton
  • Storage
  • Windows Server/AD
  • DNS
  • Data warehouse/big data
  • SQL Development
  • Application development

In what do I want to be rock solid?  These are the primary skills.

  • SQL Dataplatorm
  • SQL Engine
  • SSRS
  • SSIS
  • SSAS
  • PowerShell
  • Database Design
  • HA/DR
  • Monitoring tools (RedGate/SQLSentry/SCOM/Diagnostic Manager/etc.)

If you are interested in talking more about mentoring me, please use the Contact page to get in touch.