I think I already know the answer but... :unsure:

  • We have a prior 64-bit SQL 2008 EE install on a server that has a 14-spindle internal array and an external 12-spindle array. It works well.

    The arrays have been carved out as follows:

    First RAID on Internal array:

    C:\ (OS installed)

    T:\ (Used for Log files)

    Second RAID on Internal array:

    F:\ (use for SQL data files)

    First RAID on External array:

    D:\ (used for SQL data files and this is where SQL Server was physically installed)

    Due to an explosion in growth we purchased a new SAN and are carving out the luns as needed to maximize space/performance.

    Our plan is to move all data/log/tempdb files off the storage arrays and push to the SAN - obviously we will keep the internal storage array in tact

    The issue & 1,000,000 question:

    We need to move everything off the external array as the hardware is going to go to another server, however, the SQL installation was accidentally installed on this piece of hardware. Is there anyway to move a SQL installation over to the internal storage array? Or do I need to completely reinstall SQL Server?

    I am presuming that I need to completely re-install SQL server on the internal array (as it would be a nightmare to do otherwise). If this assumption is correct, can I simply take a FULL backup of each DB, stop the MSSQL Services, uninstall SQL Server, perform a brand-new installation, start it all up, then restore all the DB's to the new installation?

    Kind of praying for a magic solution here...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • The SQL Server binaries are on a disk that you intend to replace?

    Uninstall. Reinstall.

    Backup/restore all databases (don't forget the system ones), or detach/attach (and backup/restore the system ones that can't be detached)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/15/2013)


    The SQL Server binaries are on a disk that you intend to replace?

    yes.

    Uninstall/Reinstall - This is what I thought :ermm:

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • So a workable process would be to:

    1. Take full backups of all User DBs and the Master, MSDB, and Model DB

    2. Completely uninstall SQL Server

    3. Re-install

    4. Restore all User DBs

    5. Restore Master, then model, then MSDB (does the order matter)

    6. Restart and pray everything comes back online

    * What about the Resource DB? Should I make a copy of this before the uninstall and replace the newly created one with the copy, or leave it alone (I do now know much about this)

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (1/15/2013)


    So a workable process would be to:

    1. Take full backups of all User DBs and the Master, MSDB, and Model DB

    2. Completely uninstall SQL Server

    3. Re-install

    4. Restore all User DBs

    5. Restore Master, then model, then MSDB (does the order matter)

    6. Restart and pray everything comes back online

    Easier might be to backup the user databases (just in case), backup the system databases, uninstall SQL, reinstall SQL. Restore master, that will recreate all the user DBs that the old instance had (providing you left the files exactly where they were), then restore model and masb.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail, I sincerely appreciate your insight. One last point to clarify:

    Restore master, that will recreate all the user DBs that the old instance had (providing you left the files exactly where they were)

    Since I am moving the databases to a new physical location, before I begin the uninstall, couldn't I update the locations in the Master DB, perform the Master DB Full backup, "then" continue with uninstalling SQL Server. After performing the new install, I could then copy the DB files to the new locations so when I restore the Master DB from the last backup, it would then recreate all the user DBs pointing to the new locations?

    Or would this not be an ideal approach?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (1/15/2013)


    Gail, I sincerely appreciate your insight. One last point to clarify:

    Restore master, that will recreate all the user DBs that the old instance had (providing you left the files exactly where they were)

    Since I am moving the databases to a new physical location, before I begin the uninstall, couldn't I update the locations in the Master DB, perform the Master DB Full backup, "then" continue with uninstalling SQL Server.

    That could work. Start by backing up the user databases, run the ALTER DATABASE statements (make very sure you get them right), backup master, model, msdb and then shut the instance down.

    Make very, very sure that you get the locations correct in the system catalogs and that the user DB files are there before you restore master on the reinstalled instance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail, new piece of information here: turns out the SQL Server binaries are not installed on this drive, but some of the typical folders are there:

    D:\SQL\MSSQL10.MSSQLSERVER\MSSQL

    >> DATA

    >> FTData

    >> Jobs

    >> Log

    >> repldata

    The Data folder has the master, model, and msdb system databases

    The Log folder has the default trace, errorlog, and sqlagent log files

    So my plan now is to plan a complete maintenance window to take the MSSQL Service down, copy the entire folder structure from the external array to the internal, rename the drive letter, restart the service.

    This should work right? I mean, SQL shouldnt' know the difference as long as everything is still pointing to the proper drive letter it was installed on?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • You'll have references to the LOG folder and this is controlled by a startup parameter which sits in the registry, make sure the errorlog path is set and the default database folder too

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • MyDoggieJessie (1/18/2013)


    This should work right? I mean, SQL shouldnt' know the difference as long as everything is still pointing to the proper drive letter it was installed on?

    As long as the path to the error log location is exactly the same before and after (same drive letter, same folder) and the permissions are set, it'll be fine.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

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