Solved

correcting MS SQL server

Posted on 2009-07-14
22
200 Views
Last Modified: 2012-05-07
I have a dedicated Win 2K3 server for my SQL databases.  I was running SQL 2K and using production DB.  We had some outside consultants come in a install some different apps while I was gone.  Nobody paid any attention... The consultants are gone and these "additioanl" apps have all been reinstalled on new servers with their own SQL server/DB, but my production server now has SQL 2K, SQL 2K5 and I think maybe even SQL express...  adding insylt to injury, I am not sure if some of the good production DB that need to stay on this server are in 2K, or 2K5...

My guy feel is I need to move the "good" dabases off of this server, reload it (and install our newer license of 2K5), and then put the good DB back on to the server.  Problem is (obviously), I am not sure what would be the best way to move forward.  I have some extra hardware hanging out that I can temporarily use at this to make the correction.

Can somebody provide me a road map of what I need to do to determine what I have where, and how to correct this mess????
0
Comment
Question by:Stephen York
  • 11
  • 8
  • 3
22 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852429
does the production stuff currently work?
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24852443
very well, happily!
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852451
you can check the database to see if they are in a 2000 or 2005 compatability mode...80 is 2000, 90 is 2005.

I think since they are working, there isn't an urgent need to change anything around...know what I mean?
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24852511
Well.... yes, they work, but I am continually running out of space and need to get some of the "unkown" files off the server.  When I set this up way back when, I made the system partition acording to MS suggestions, but this was too small (in hindsight). I also want to update all of the really old BIOS/fireware and have a slight twinge about losing data/access to the data if something bad happens, dispite backups.

This time of the year is good for me as most of my staff are out on vacation.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852523
your data files getting too large?  or is it the log files?
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24852624
If I look at the compatibility mode on my databases, they are all at 80 (choices range 60-80).  Does that mean that they are all 2K DB?  If so, could I uninstall the SQL 2K5?  What do I do with the almost empty 90 folder in the "C:\program files\microsoft sql" folder?

Is there a way to put the DB on hold, unistall the MSSQL from the C drive and reinstall on the huge D drive (which is where the DB files are located) and then bring the DB filles back off-hold?
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24852629
the programs on the system partition.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852656
Are the dbs connected to a 2000 or 2005 instance?
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24852679
how do I know?  Actually, It looks like the 2K5 is uninstalled anyway as it does not show in the installed programs under the control panel.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852704
that doesn't necessarily matter....

what does this return?


select SERVERPROPERTY('ProductVersion')
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24852760
8.00.2055
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Author Comment

by:Stephen York
ID: 24852773
BTW - did not mean to be rude - I appreciate your help :)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852780
Its sql 2000...you're good to go...nothing should be 2005 on that instance.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852785
:)  No offense taken.
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24852842
Thanx!  OK, if I install SQL 2K5 on the machine in the D drive, will the "old" settings move from C drive and then I could uninstall the 2K SQL?
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 100 total points
ID: 24852861
Its going to be a much more involved process than that....
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 400 total points
ID: 24852962
The best bet is to look at your running services for SQL Server and variants that contain "SQL" in the title. (Start --> Run --> services.msc) Right click and properties and track the paths down.

The the next thing is to look at your registry (standard warning about messing with the registry apply) and find the two hives below. You  can track from there as well.
----------------------------------------------------
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#\Setup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
----------------------------------------------------

As far as reinstalling -- when you shutdown, you can make copies of the master and msdb mdf/ldf files.

And to retain the user info -- you can script out the users using the sp_help_revlogin as described in the transfer logins.

How to transfer the logins and the passwords between instances of SQL Server 2005

You may also want to glance at the following two articles to see what you can do in the re-install while trying to keep stuff active. If you can have a working solution that allows the users access, at the same time giving you a chance to take your time and do it right -- you are so much better off. I've done enough weekend crams. ;-)

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
http://support.microsoft.com/kb/224071

How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24853633
Thank you - I had read the MS articles on moving the DB and that is what prompted me to come here first looking for a better answer like moving the entire SQL system to a new location so that I could reformat/reload...

I am actually OK if I need to take the system down for a couple of days, if that is what is required.  But I do need to be able to reload the beast somehow, I have a 12 GB system partition awith only 600KB free right now...

I found this article on moving the SQL server to a new loacation: http://vyaskn.tripod.com/moving_sql_server.htm.  I would assume that I could do this, then reload the old box and then move the databases back...
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24853957
If you only have one instance running and just need to shift the databases you could actually pull it off without doing a reinstall.

That article was written for SQL 7 and SQL 2000 -- which you are running. But renaming a SQL Server after install has always had issues.

I always have reservations about having databases on the system partition. But you can avoid a re-install using the move methods in the MS articles above.

The biggest helper (and a good tool for the toolbox) is Beyond Compare from Scooter Software (http://scootersoftware.com/).

My best suggestion is to detach a database. Fire up Beyond Compare -- do a copy from the C: to D: and then reattach. The only four databases you really have to worry about are the system databases (master, model, msdb, and tempdb). Those are covered in the second link. The basic script is below.
-- Highlight this and hit <F5>

USE MASTER

GO

SELECT * 

FROM [MyDbName]..sysfiles

GO
 

-- copy the results for file name

-- and paste them into the sp_attach_db 

-- statement. Change the drive letter.
 

-- Then highlight this and hit <F5>

exec sp_detach_db 'MyDbName'

GO
 

-- Then use Beyond Compare to move/copy the .mdf/.ldf files.

-- After the copy do the attach
 

exec sp_attach_db 'MyDbName',

	'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDbName.mdf',

	'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDbName.ldf'

GO

Open in new window

0
 
LVL 1

Author Closing Comment

by:Stephen York
ID: 31604396
Thank you both for your help.  I ended up backing ujp the databases, creating a temporary SQL server, moving/restoring them to the temp server, pointing my applications to the temp server, kill/rebuild the real SQL server, backup/restore the DB back to the rebuilt server, reloading..., repointing the applications, and moving forward.

Lesson learned - hire consultant, place shock colar around his neck, smash own fingers in drawer to stay awake and remiond of pain endured, watch consultant, press collar button often and for no apparent reason to remind them who they work for,...
0
 
LVL 1

Author Comment

by:Stephen York
ID: 24873070
Thank you both for your help.  I ended up backing ujp the databases, creating a temporary SQL server, moving/restoring them to the temp server, pointing my applications to the temp server, kill/rebuild the real SQL server, backup/restore the DB back to the rebuilt server, reloading..., repointing the applications, and moving forward with a functional SQL server

Lesson learned - if hiring a consultant, place shock colar around his neck, smash own fingers in drawer to stay awake and remind self of pain endured the last time, watch consultant, press collar button often and for no apparent reason to remind them who they work for,fully understand what we are doing in the first place and be sure the consultant understands, start with good backup (smash something else in drawer if forgetting this step...
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24873170
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now