Solved

correcting MS SQL server

Posted on 2009-07-14
22
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

717 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