True story
A long time ago, I had to actually do stuff to databases. One thing I had to do was move data files around. Maybe some knucklehead had put system databases on the C: drive, or a LUN was filling up, or we got a new LUN. You know, whatever. Natural curiosity oft leads one to the internet. If one does not succumb to food and cats, one may find useful information. Or anonymous message boards. Sort of a toss up. What I found was this article. Weird, right? 2009. Brent said to use ALTER DATABASE. It’s new and pretty and smart people do it. What Brent didn’t do was explain how it’s done. Or link to how it’s done. I felt cold and alone. Abandoned. Afraid. “Great post, Brent”, I said sarcastically, and set out to figure out how to work this magic on my own.
I turned to BOL, the destination of all self-loathing people. If you scroll down to the bottom, way down at the bottom, the syntax is there. Of course, moving system databases is a horse of a different color. But hopefully you don’t need that one. For user databases, it’s rather more simple:
- Alter the file metadata to the new path
- Set the database offline
- Physically move the file
- Set the database back online
Easy enough!
Run ALTER DATABASE with the new location. We’re moving the data file. If we were moving the log file, it would probably end in “_log” or something. You can find all this information in sys.master_files, except where you’re moving the file to. Just don’t actually move it to C:\Whatever. You may run into problems later. Also, you need the filename. If you don’t include it, SQL won’t complain until you try to set the database back online. Yay!
|
1 |
ALTER DATABASE [Sample] MODIFY FILE ( NAME = Sample, FILENAME = 'C:\Whatever\Sample.mdf' ); |
This is the part that you need to think through. People have to be cool with the database being offline while you move the physical file. This is not a seamless transition. If you’re moving large enough databases, you may want to consider an alternate method, like Mirroring or Log Shipping. They take more work, but you get the whole near-zero-downtime thing out of it. You may want to stage a mock file move to test LUN to LUN copy speeds. See how many GB you can move per minute. That way you’ll at least be able to estimate how long the outage will last. Assuming all that is cool, go ahead and take the database offline.
|
1 |
ALTER DATABASE [Sample] SET OFFLINE; |
Now you gotta hurry up and get that file moved. How you do that is up to you. You may prefer to just use Windows Explorer, since it has a status bar, and tells you copy speeds. Good stuff to know if people ask for updates, right? Just to fill space, here’s a PowerShell command. I still hate PowerShell.
|
1 |
Move-Item -Path "D:\Data\Sample.mdf" -Destination "C:\Whatever" -Force |
Once that finishes, put your database back online.
|
1 |
ALTER DATABASE [Sample] SET ONLINE; |
If you find yourself having to do this often, or if you have to migrate a group of databases, it’s probably worth scripting out.
There you have it
It’s that easy to do. Just make sure you have adequate backups, in case something goes wrong. I take no responsibility for what happens to your data files when they copy across your SAN, or anywhere else.
Thanks for reading!
20 Comments. Leave new
Great post Eric. I’ve had to do this more times than I’d like to admit for the reasons you mentioned or when I’m setting up a new environment and neglected to set the directories up to company SOP. I have a script, all manual mind you, with comments telling me what to do. I don’t use it often, but when I do, it works like a charm. The best part is showing other DBAs how to do it and watching their heads explode while they exclaim, “Sorcery!”
Why not just email users of when the DB will be down, then at the appointed time, detach it using SSMS, move the files to their new locations using Explorer, re-attach the files using SSMS, and email users that the DB is up again.
doesn’t work on mirrored databases on SQL 2005. did this a few weeks back when moving db files from an old SAN to a newer SAN
Jeffrey – often users aren’t a big fan of downtime, or we’re dealing with multi-terabyte databases.
Erik, thanks for the post.
In the past I’ve passed through this struggle but since the database wasn’t so big and I’ve a free slot in time windows, I’ve performed a full backup with full restore “with move” option.
I will bookmark this post in order to user in the future.
I have done this task in the past by detach-move-file-attach, and by restoring databases from backup using WITH MOVE …
Are particular advantages to the technique you use? Or is it a matter of whatever makes you happy, and gets you home in time for dinner.
Good discussion of that very issue here.
https://weblogs.sqlteam.com/dang/2009/01/18/dont-use-sp_attach_db/
Like you, Eric, I detest Power Shell. I use sqlcmd instead (watch out for permissions issues):
:setvar SourceDir ‘F:\Data\’
:setvar TargetDir ‘L:\Data\’
:setvar DatabaseName Sample
USE [master];
GO
–NOTE: this script can be generated into a file using :out and sys.master_files
RAISERROR( ‘Creating directory –> “$(TargetDir)” (if it does not exist)’, 0, 1) WITH NOWAIT;
GO
EXECUTE xp_create_subdir ‘$(TargetDir)’;
GO
RAISERROR( ‘MODIFYing FILEs for DATABASE –> [$(DatabaseName)]’, 0, 1) WITH NOWAIT;
GO
ALTER DATABASE [$(DatabaseName)] MODIFY FILE (NAME = Sample, FILENAME = ‘$(TargetDir)Sample.mdf’);
ALTER DATABASE [$(DatabaseName)] MODIFY FILE (NAME = Sample_log, FILENAME = ‘$(TargetDir)Sample_log.ldf’);
GO
RAISERROR( ‘SETting DATABASE OFFLINE –> [$(DatabaseName)]’, 0, 1) WITH NOWAIT;
GO
ALTER DATABASE [$(DatabaseName)] SET OFFLINE;
GO
RAISERROR( ‘Copying DATABASE FILEs –> [$(DatabaseName)]’, 0, 1) WITH NOWAIT;
GO
–NOTE: you can use command line flags to delete the original files after robocopy
!!robocopy $(SourceDir) $(TargetDir) sample.mdf
!!robocopy $(SourceDir) $(TargetDir) sample_log.ldf
GO
RAISERROR( ‘SETting DATABASE ONLINE –> [$(DatabaseName)]’, 0, 1) WITH NOWAIT;
GO
ALTER DATABASE [$(DatabaseName)] SET ONLINE;
GO
RAISERROR( ‘Finished Moving DATABASE –> [$(DatabaseName)]’, 0, 1) WITH NOWAIT;
GO
Interesting use of SQLCMD–I always forget about using it. What I’ve done is simply use xp_cmdshell as part of the TSQL script (as long as it is enabled).
*Erik, (beg pardon)
It’s times like these that I’m glad my database is in a clustered VM and my database files are in their own .vhdx drives (Hyper-V Server 2012 r2). You just go into the Failover Cluster Manager and Storage LiveMigrate the .vhdx file in the VM. No downtime; no problem.
Now, if I wanted to move the database files between .vhdx drives or within the drive, yes, I’ve got to either ALTER DATABASE or detach-move-reattach. It makes me wish that the Sql Server devs would have implemented online file moves, something that’s obviously possible right now.
Hey Erik, very nice post. Really enjoy reading your posts, your writing style is very entertaining 🙂
I have used this method a lot in my DBA career with much success, but at one of my latest clients I have found myself in a bit of a interesting situation. This environment is a full 24/7 business with no downtime available, so to move database files around on the server while keeping the database available was a challenge that needed a proper solution.
The solution that I designed assisted with the moving of files while database is still fully online but unfortunately there is some limitations (which can be googled)
Check the script below and feel free to use it. Please test it on dev before trying it on a Production server.
ALTER DATABASE [DB_Name]
ADD FILE
(
NAME = [New_Logical_Name],
FILENAME = ‘I:\Folder\Physical_Name.ndf’, –ndf or mdf, depending on your environment
SIZE = 359210MB, –Current Size of File
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024MB
)
TO FILEGROUP [FileGroup_Name]; –Create the new file in the same File Group
DBCC SHRINKFILE (N’Logical_Name’ , EMPTYFILE); –Move all the data to the newly created file
ALTER DATABASE [DB_Name] REMOVE FILE [Logical_Name]; –Drop old file on original drive
Its funny, I have this exact script in my repository with a subtle tweak. I usually would have a window where I could move the files but invariably a developer left a connection(s) open to the database and it was always fun hunting that down. I also wanted to make sure no connections are trying to be made from some process/old server/etc as well. So I would use the following statement prior to setting the database offline:
ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Keep in mind that once you’ve moved your files and turned your database back online, you need to re-enable multi-user access:
ALTER DATABASE YourDBName SET MULTI_USER;
That’s a great idea!
BOL is my bible 🙂 Every junior DBA coming on board is told to install them locally on their machine as I’ve found the online variants to be too painful (sometimes). There are also times when you don’t have internet access depending on the site you are at.
If I need to move a database without using Detach/Attach, I will do it. I’ve had issues in the deep, dark past where a DB has been detached and then would not allow it to re-attach (if I knew the reasons or could remember them, I would mention them here…).
Powershell has its uses sometimes – especially when trying to perform work on a cluster than you just cannot get it to do via the WFM GUI.
“still hate PowerShell”
I don’t exactly hate it, I just think it was unnecessary. Microsoft cancelled the IronPython project which is still a far more capable language than PS and much friendlier to boot. Not to mention that Python is used for maintaining most Linux distros as well as OS/X! Why on earth invent a new language for Windows only?
It would have been easy (still is, really) to use (Iron)Python for everything PowerShell does today. But without official support, and no IronPython for Python 3.0, it’s a bit of a lost cause at this point.
Thank you for the article, it’s very useful!
It’s also a good idea to change the default database path for new databases. The default path is obtained from the Windows registry.
You can also change with T-SQL, for example, to set default destination to: D:\MSSQLData
USE [master]
GO
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultData’, REG_SZ, N’D:\MSSQLData’
GO
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultLog’, REG_SZ, N’D:\MSSQLData’
GO
Extracted from: http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html
Clear, concise and will help you get the job done. But I cannot emphasize enough how much better off you’ll be if you utilize Robocopy from the command line instead of Move-Item.
Brent, will this work when moving the ssisdb from the system C: drive to another data and log drive off the system drive?
Ben – no clue, I haven’t moved SSISDB, so your best bet would be to ask that at a Q&A site like https://dba.stackexchange.com.