We help IT Professionals succeed at work.

Unable to connect to recovered SQL database

Andrew N. Kowtalo
Andrew N. Kowtalo asked
on
I have a grievous situation .   One of our clients who is HIPPA has lost access to their old EMR database.   They were running Centricity CPS prior to moving over to their new EMR.   They could not fully migrate all their database data so they have stil been using their old EMR software for legacy data.   HOWEVER the centricity EMR software stopped working because the database within MS SQL 2012 had been degraded to suspect.   Not matter what I googled and tried to repair it, nothing worked.   We had backups of the actual database and I was able to retrieve the actual file, disconnect the suspect database and restore the recovered database in SQL.  

The backup database has been renamed to the database file in SQL that was suspect, and is showing as connected.   However when logging into the EMR's web interface which locally connects to the suspect database, the software can no longer connect to the recovered database.   The login is throwing an error and I am starting to panic.   Peoples jobs are on the line here.   Can anyone first before I go into severe detail lend a hand?  This is critical.
Comment
Watch Question

David ToddSenior Database Administrator

Commented:
Hi

Question:
When you restored the database - you mention that you renamed the database files. Why not restore your backup over the top of the suspect database as the same name as the suspect database?

Regards
  David
David ToddSenior Database Administrator

Commented:
PS
If you can, rename the suspect database - and old or suspect to the database name

Then restore the backup as the correct database name - you may need to rename the database files at this stage as the suspect database still exists and has files with the original names.
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
The suspect database would not let me restore over it..  I tried that...  When right clicking everything was greyed out.
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
I couldnt, I even had the SQL guy come up and try he couldnt either.
David ToddSenior Database Administrator

Commented:
Hi Andrew,

What other critical databases are on this server?

Do you have the option of restoring this database to another server and pointing the application at the new location? So if you can do this on the application, then surely as you've restored the database you can change the application config to connect to the new database name?

How big is the database? What version SQL?

Regards
  David
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
David this is a very old legacy app, were talking 2015.   I have 2 different logins however both are throwing the same error with an exception ID, I do not think its the database as of yet.   I attached a screen shot error.  

We do not have SQL running on another server there are 2 different servers involved.  1 hosts the SQL dbase the other runs the engine.   I have rebooted the SQL host server however have not rebooted the connecting server.
centricity-error.JPG
David ToddSenior Database Administrator

Commented:
David ToddSenior Database Administrator

Commented:
Hi Andrew

Can you show me the version and database info from SSMS?

Many thanks
  David

PS Where in the world are you?
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
Here is what the SQL dev is telling me  i would need to know far more about the application to even begin to determine how you would do that
​ as far as a sql context is concerned, the user wouldnt be within that security list we were looking at
​  they would be in a table in that database

    which would mean the only way to restore that user would be to restore a newer copy that had them
​    or recreate the user from within the app

    that user never directly interacts with the database so they wouldnt be in the security list we saw on the admin side
​    even if theyre using their domain creds

    if my assumption is correct, then its likely youll need to try that reattach method using the mdf and ldf files
​to restore the database

I have it backed up Do you think this may work?
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
David Upstate new york.  I am not a SQL expert.  Can you advise what you mean by Can you show me the version and database info from SSMS?
David ToddSenior Database Administrator

Commented:
Hi,

Reattaching database files isn't the same as restoring the database.

Do you have room to restore a good known backup alongside the existing suspect database?

HTH
  David
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
Is this what you are asking?
centricity-info.JPG
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
Hi David the backup currently connected was successfully backed up and restored.   it was renamed after the restore was done to match the correct file name.
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
Since I know nothing about this centricity application and its LONG before I got here I can not even tell for sure if this is related to SQL.   Can anyone take a look at that error perhaps and see if they can find a resolution?  Maybe the database is fine.
David ToddSenior Database Administrator

Commented:
Hi Andrew,

I really really don't understand exactly how and what you did to rename the restore to match the correct file name. That just isn't right or good or anything!

I like the screenshot of SSMS.

Is demo the name database you are concerned with? What did you restore the backup as? Can you run queries against databases using SSMS? If no to the last question, find someone who can immediately!

I know you want this resolved 5 mins ago, but what are the real parameters? Ie a pot of metal this application controls will solidify in 3 hours and will then take jackhammers to break it out?

Regards
  David

PS I'm in New Zealand
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
The database i am concerned with is CentricityPS.  When I ran the restore I added a new database as centricity_temp.   I restored a .bak file to that temp database.   After I stopped the suspect database then deleted it.    I then renamed the _temp file to the name of the database within SQL.   I rebooted the server and it shows the database connected with the same name as before, same data.
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
Attached is an activity monitor.  It shows me the application is talking to the database.
activity-monitor.JPG
David ToddSenior Database Administrator

Commented:
Hi Andrew,

So your database CentricityPS shows as online and nothing abnormal in the SSMS - Object Explorer Window screenshot.

If the application won't connect, then you should restart the application.

HTH
  David
David ToddSenior Database Administrator

Commented:
Answered via private session. Duration 00:00:07
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
David the session cut out can you resend?
Distinguished Expert 2017

Commented:
Usually, if you detach and mice the old files into a subfolders
Then restore the bavkup, you shoukd retain the prior sevurity affiliations.
I glanced quickly at the distinction and possibly missed other similar suggestions.

I think that restore to a new name and then renaming it back may have severed the SQL login affiliation or if your emr software is running using NS Windows, integrated..

The error you get shoukd shed light on the issue.
With that said. Drop the existing. Make sure remnants of the DB/ldb are not in the root.
Restore the DB from backup while the emr web site and app pool if any is stopped.
Restore the DB from backup.
Start the site,spp poll and see what happens. You nay need to go through the emr DB if your login does not work,mor go through the config of the app to reset the SQL login credentials after updating them on the SQL server.
I.e. The emr SQL account's password changed.
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
@Arnold the location of the database and all its folders were restored to the exact same location from top folder down.   We had a full datto backup of the application dbase.  

One thing the user mentioned to me is that the software failed after the domain administrator password was changed.  So it may be possible the software is using services that run off that old UN and PW.   I noticed on the SQL dbase server an ODBC connection that failed and it would not allow me to repair it.   It throws an error when I try to open it.   We can not change the domain admin pw back unfortunately.   I am wondering if this may take affect on it.   This software is so old that it can not be easily repaired and I am starting to panic a little.
Distinguished Expert 2017

Commented:
Things that stop functioning following admin password change deal with a service that fails to start. Updating the service with new service credentials or updating the current credentials with new password should get service started and software .......

Look for logs to confirm.
Andrew N. KowtaloSupport Center Engineer

Author

Commented:
THE ODBC connection setup on the SQL DBASE server wont even open.   Also there is a SQL Authentication user that connects within the web app itself.   We renamed that user and now the web page wont even load.  It's getting worse and worse.

I dont know what the old password was so I have no idea how to see any users login.   Perhaps it has to be changed within the EMR website itself?  Ugh!
Distinguished Expert 2017

Commented:
Web based. Check application pool to see if it was configured to run under the admin account.

Not clear what you are changing. With usernames

Windows login credentials are commonly are not stored In an application.
It is either used on a service, web based application pool
A change ...

Changing users, could mean the user does not have rights to access files, in SQL the new user likely does not have rights to connect to the SQL server. And/or the database ...

Emr web logs ?

Odbc which 32bit or 64bit?

Emr might be a 32 bit app...
Support Center Engineer
Commented:
I got a hold of the vendor.  They were able to remote in and fix it.  

The good news is the dbase was 100% good.  

The software problem was affected by the pw change.
Distinguished Expert 2017

Commented:
to avoid this issue in the future, create a service account, and use it instead of an account whose password might be changed down the road.