Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

correcting MS SQL server

Posted on 2009-07-14
22
Medium Priority
?
210 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 300 total points
ID: 24852861
Its going to be a much more involved process than that....
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 1200 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

610 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