Solved

PC Scale Tower - SQL Server Rebuild

Posted on 2009-07-06
9
291 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Stuck in voice control mode on your Amazon Firestick?  Here is how to turn it off!!!
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
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…
Articles on a wide range of technology and professional topics are available on Experts Exchange. These resources are written by members, for members, and can be written about any topic you feel passionate about. Learn how to best write an article t…

778 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