October 27, 2014 at 4:19 pm
We have a SQL Server 2008 R2 instance with the following features:
C:\ - Installation folder
D:\ - System DB Files, User DB Data files
E:\ - Log Files
We are definitely moving the system and user databases on D:\ and E:\ files to a new better storage (Drives G:\ and H:\ on the same server) and are going to follow the procedures mentioned in BOL/MSDN using ALTER DATABASE.
I have 2 questions:
(a) We want to move drive C:\ onto the new, better storage but our understanding is that since it installation files, we can't just move the drive. Rather, we would have to install SQL Server again on the new storage and then maybe restore the databases. Please confirm if this is correct.
(b) If we just move DATA and LOG Files our other issue is that we have about 75 databases to move. The only way we know is to script out the statements for each database. Is there an easier way to move this number of databases onto the new drive than scripting out for each?
Thanks!
October 28, 2014 at 2:33 am
Like2SQL (10/27/2014)
(a) We want to move drive C:\ onto the new, better storage but our understanding is that since it installation files, we can't just move the drive. Rather, we would have to install SQL Server again on the new storage and then maybe restore the databases. Please confirm if this is correct.
Correct. If you want to move the system partition, you will have to take a block level disk image of the partition and then restore it to the new disk.
(b) If we just move DATA and LOG Files our other issue is that we have about 75 databases to move. The only way we know is to script out the statements for each database. Is there an easier way to move this number of databases onto the new drive than scripting out for each?
Not that I know of. Maybe DMO/SMO has something, but I suspect that T-SQL is the fastest way to do that.
Again, if you can take a block level image, you could backup the current disks and restore them to the new hardware keeping the current drive letters. In this case, there is no need to do anything else from SQL Server.
-- Gianluca Sartori
October 28, 2014 at 11:00 am
Thanks Gianluca, I appreciate the response. Doing any OS activities at this point is not an option since IT team is caught up with major projects. So it would have to be us. I have another follow-up question: So I need to repeat this process of taking db offline, copying files, pointing to new location etc. for 75 databases. Do you recommend:
Do database by database i.e. take first db offline, copy files, alter db, bring online etc. OR
Do in batches:
Take all db's offline
Copy all files to new drives
Run T-SQL to point to new location for all drives
Bring all db's online
October 28, 2014 at 11:02 am
Also Gianluca, just to clarify the new drives G:\ and H:\ are already attached to this server and our requirement is to use the new drive letters.
October 28, 2014 at 11:07 am
I would do it one database at a time.
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply