correcting MS SQL server

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????
LVL 1
Stephen YorkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
does the production stuff currently work?
0
Stephen YorkAuthor Commented:
very well, happily!
0
chapmandewCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Stephen YorkAuthor Commented:
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
chapmandewCommented:
your data files getting too large?  or is it the log files?
0
Stephen YorkAuthor Commented:
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
Stephen YorkAuthor Commented:
the programs on the system partition.
0
chapmandewCommented:
Are the dbs connected to a 2000 or 2005 instance?
0
Stephen YorkAuthor Commented:
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
chapmandewCommented:
that doesn't necessarily matter....

what does this return?


select SERVERPROPERTY('ProductVersion')
0
Stephen YorkAuthor Commented:
8.00.2055
0
Stephen YorkAuthor Commented:
BTW - did not mean to be rude - I appreciate your help :)
0
chapmandewCommented:
Its sql 2000...you're good to go...nothing should be 2005 on that instance.
0
chapmandewCommented:
:)  No offense taken.
0
Stephen YorkAuthor Commented:
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
chapmandewCommented:
Its going to be a much more involved process than that....
0
Jim P.Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen YorkAuthor Commented:
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
Jim P.Commented:
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
Stephen YorkAuthor Commented:
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
Stephen YorkAuthor Commented:
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
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.