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
set offline WITH ROLLBACK IMMEDIATE
GO
–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
GO
— To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1;
GO
— To update the currently configured value for advanced options.
RECONFIGURE;
GO
— To enable the feature xp_cmdshell.
EXEC sp_configure ‘xp_cmdshell’, 1;
GO
–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
GO
— To disable the feature xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 0;
GO
— To update the currently configured value for this feature.
RECONFIGURE;
GO
— To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 0;
GO
— To update the currently configured value for advanced options.
RECONFIGURE;
GO
Shore SQL 