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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » I think I already know the answer but......

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

Rate Topic

Display Mode

Topic Options

Author

Message

MyDoggieJessie

Posted Tuesday, January 15, 2013 10:17 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1407363

GilaMonster

Posted Tuesday, January 15, 2013 10:25 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 37,739, Visits: 30,012

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 2008, MVP
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

Post #1407366

MyDoggieJessie

Posted Tuesday, January 15, 2013 10:27 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1407367

MyDoggieJessie

Posted Tuesday, January 15, 2013 11:17 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1407392

GilaMonster

Posted Tuesday, January 15, 2013 11:30 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 37,739, Visits: 30,012

MyDoggieJessie (1/15/2013)

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 2008, MVP
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

Post #1407397

MyDoggieJessie

Posted Tuesday, January 15, 2013 1:23 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1407461

GilaMonster

Posted Tuesday, January 15, 2013 1:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 37,739, Visits: 30,012

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)

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 2008, MVP
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

Post #1407467

MyDoggieJessie

Posted Friday, January 18, 2013 9:08 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1408963

Perry Whittle

Posted Monday, January 21, 2013 5:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:53 AM
Points: 5,204, Visits: 11,158

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" Wink

Post #1409506

GilaMonster

Posted Monday, January 21, 2013 5:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 37,739, Visits: 30,012

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 2008, MVP
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

Post #1409511

« Prev Topic | Next Topic »

Permissions