David Fowler shows how you can change file paths for all of your databases in one fell swoop:
You’ve got a SQL Server with a few hundred databases on it (to be honest it doesn’t even need to be quite that many) and you need to move all the data and log files to a new location. Perhaps you’re going to be migrating onto a new, shiny SAN or maybe your disks are just about full and you need to shift a bunch of the files off somewhere else.
The first thing that you’re going to need to do is change the paths of the files in SQL. That’s easy enough to do with an ALTER DATABASE statement.
ALTER DATABASE SQLUndercover MODIFY FILE (NAME SQLUndercover_Log, FILENAME = 'F:\SQLLogs\SQLUndercover_Log.ldf'But that’s going to get very tedious very quickly if you’ve got to do that for a whole lotta databases. So to help out, I thought I’d share a little script that I’ve been using for a while (or a variation on it at least) to make the process far easier and generate all the ALTER statements for you.
Click through for the script as well as a bit of advice around the actual moving of the files.