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????
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????
chapmandew
does the production stuff currently work?
ASKER
very well, happily!
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?
I think since they are working, there isn't an urgent need to change anything around...know what I mean?
ASKER
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.
This time of the year is good for me as most of my staff are out on vacation.
your data files getting too large? or is it the log files?
ASKER
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?
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?
ASKER
the programs on the system partition.
Are the dbs connected to a 2000 or 2005 instance?
ASKER
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.
that doesn't necessarily matter....
what does this return?
select SERVERPROPERTY('ProductVer
what does this return?
select SERVERPROPERTY('ProductVer
ASKER
8.00.2055
ASKER
BTW - did not mean to be rude - I appreciate your help :)
Its sql 2000...you're good to go...nothing should be 2005 on that instance.
:) No offense taken.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
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.
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
ASKER
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,...
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,...
ASKER
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...
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...
Glad to be of assistance. May all your days get brighter and brighter.