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

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


Advertisements

One thought on “Database on the Move – Part I

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s