Today I needed to move some databases. Data files, technically, but it’s essentially the same concept. Instead of moving both the data file and the log file to another machine, I just moved the data file to the other drive, as having the data files on one drive and the log files on another speeds the access a bit. Everything else is identical to moving from one machine to another.
Perhaps most surprisingly, Microsoft actually came through on this one with a very useful Knowledge Base article.
The thing that notice over and over again is that I can’t seem to come up with the article with Microsoft’s on search – but I can find it without a problem through Google. But at least I was able to find it eventually, and that is the important part!
Comments
3 responses to “Moving Databases with SQL Server”
I had to move a SQL database today. The client dropped a large drive into the server and I wanted to move the data file to the larger drive. I left the log file on the previous drive.
The sp_detach_db and sp_attach_db worked fine. I found the sp_attach_db at least a minute. I also disovered that when you specify the database name, it is case sensitive.
True, not hard at all. I just like to give myself pointers to figure out how to do it again when I’m in need – so adding it to the blog can certainly help in that regard!
I’ve done this a couple of times and each time I need to refresh my memory on how it is done. It turns out to be pretty easy.