Please Verify - New Drive For Installation Folder and DB Migration

  • Like2SQL

    SSCommitted

    Points: 1662

    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!

  • spaghettidba

    SSC Guru

    Points: 105673

    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.

  • Like2SQL

    SSCommitted

    Points: 1662

    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

  • Like2SQL

    SSCommitted

    Points: 1662

    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.

  • spaghettidba

    SSC Guru

    Points: 105673

    I would do it one database at a time.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply