Solved

PC Scale Tower - SQL Server Rebuild

Posted on 2009-07-06
9
286 Views
Last Modified: 2012-05-07
I lost my SQL server a couple of weeks ago to a lightning storm.  I realized after the fact that my backups were backing up files, but no SQL Server agent to save all my SQL database/connection info.  This SQL server has only one purpose... to house the databases for an application we pull historical data from called PC Scale - Tower.  It seemed like a simple phone call to the "Tower" people to help set it back up.  Well, they are refusing to help us.

So, I have no idea how any of this was setup before and I'm having no luck making it work.  Does anyone out there use PC Scale Tower that might be able to lead me through some specific setup issues, or even provide some documentation on the SQL Server setup?  We're willing to pay for phone/remote support, but I can't find anyone to take our money.

Your help is greatly appreciated.
0
Comment
Question by:nwadatarecovery
  • 5
  • 3
9 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 24788382
Can you access the disk(s) at all?

Can you access the C:\Program Files\Microsoft SQL Server\MSSQL.#\MSSQL hive?

Or  do you have backups of the master and model databases as well as your data databases?

If so -- there are a number of ways to recreate your SQL databases and get close to your original setup.
0
 

Author Comment

by:nwadatarecovery
ID: 24807148
I have the databases.  I've reinstalled SQL Server 2005 to my newly built server and attached the old databases.  It appears that my users are orphaned, so I can remove the DB_Owner user, "tower", then recreate that user but I don't know the password to assign it.  So, my application fails with a "An error occurred in the Retrieve Client Files Service: Login failed for user 'tower'." message.  It appears that the application is hard coded to access the database via user 'tower' with a password that I do not know.

Is there an easy way to recover the password from an orphaned user?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24807968
You didn't recover the original Master DB I take it?

Do you have a copy or backup of the original master?

If you can restore it -- then it should be as before the crash.  

Or alternatively -- I have not tried this, so I won't guarantee it will work. If you can restore the master DB as something like master_temp. Then copy the sp_help_revlogin from the link below. Where it says master change it to master_temp. Then run the procedure to extract the logins.

How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/default.aspx?kbid=246133
0
 

Author Comment

by:nwadatarecovery
ID: 24808286
I'm sorry, let me explain better/differently... My server's C: drive died, but all of my SQL databases were on the D: drive in a safe place.  I fired up a new C: drive and installed Win2k3 Server, then Backup Exec, then restored my backups from the previous day.  Backup exec 9.1 was set to backup everything on the C: drive, but did not have any SQL Agents or "system state" options.  So, I restore the entire C: drive and see that my SQL Server did not fix itself after the file recovery.  I then installed SQL 2005 and reattached the databases from my D: drive.  That directory on my D: drive still has the "master" and the "model" databases in it along with all the others, but it appears that my most important user, "tower", cannot connect to the database "COMPANYDATA" because ???  I was told by someone better with SQL than I that the users were orphaned.  Does this make any sense?

I'm wodering too... if I installed Win2k3, then SQL 2005, then backup exec and did the recovery if I would get a different result.  
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 24808549
There are two levels of user ids in SQL Server

There is the Login -- which is at the general level of SQL Server. Information about the login, including passwords, is stored in the master database in the sysxlogins table.

Then there is a User which is tied to the individual databases. They are both keyed off a SID (Security IDentifier) that has to match from the master to the individual database.

In SQL 2000 you could reconnect the two easily. In this case, because you don't know the password, you have to recover the login. You can either delete the login and user and totally re-install the Tower app to have recreate the login. In that case it would probably have to detach the db, have it recreate the database. After the new tower user and db were in place, detach the new blank db, re-attach the old and then you could drop the user from the old DB and reassign the login back to the old DB.

But if you have the original master from before the reinstall -- you can use that to get back to where you were. Or in this case the sp_help_revlogin will pull out the original list of logins, their SID and the original password in a hashed value. That way you can cut down on some steps.

Considerations for Backing Up and Restoring System Databases
http://msdn.microsoft.com/en-us/library/ms190190(SQL.90).aspx
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24992556
If the user doesn't respond -- I would say delete -- no refund.

It would have been nice to see if my suggestions would have worked, but to have this lying around in an "un-proven" state -- not a good thing.
0
 

Author Closing Comment

by:nwadatarecovery
ID: 31600309
Jimpen-
You provided some very good information and links to some good resources.  I was not able to get my SQL server back to a running state with this information, but helped me determine that I do not have all appropriate pieces to put this puzzle back together.  Thank you.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24995995
I'm sorry that you couldn't get it back to its original state. Thanks for letting us know.

May all your days get brighter and brighter.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Learn more about the importance of email disclaimers with our top 10 email disclaimer DOs and DON’Ts.
Stuck in voice control mode on your Amazon Firestick?  Here is how to turn it off!!!
The Bounty Board allows you to request an article or video on any technical topic, or fulfill a bounty request to earn points. Watch this video to learn how to use the Bounty Board to get the content you want, earn points, and browse submitted bount…
Saved searches can save you time by quickly referencing commonly searched terms on any topic. Whether you are looking for questions you can answer or hoping to learn about a specific issue, a saved search can help you get the most out of your time o…

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

20 Experts available now in Live!

Get 1:1 Help Now