Post reply

Rebuilt SQL Server 14 system databases, not functioning, pointing at non-existent files

  • August 29, 2017 at 11:53 pm

    #338899

    I rebuilt system databases for a SQL Server 2014 installation.

    The server will still not restart, it cannot find MSDB and model databases.  (They've been set to point to a place (USB drive) that does not exist.)

    What do I do to rebuild the system databases with correct paths in them.

    Details:
    1.  This is on Windows 10 Pro.
    2.  The rebuild achieved using  setup.exe" /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME="<Name>" /SQLSYSADMINACCOUNTS="<AccntName>" and similar.
    3.  Exception messages similar to   "spid12s  File activation failure. The physical file name "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\modellog.ldf" may be incorrect."
    4.  I can't find the offending path in the Registry, so can't alter it there.

  • August 30, 2017 at 2:14 am

    #1957370

    This should help:
    https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 30, 2017 at 2:37 am

    #1957376

    Thanks GilaMonster.

    I went through that article in my initial research.  My situation isn't covered.

    I've regenerated the system databases and still can't start the server.  The problem is that the regenerated system has some strange paths to at least those two databases.  I'm not sure where the paths come from (template maybe?), and would like to correct them.

    I've tried additional parameters to setup that looked as though they might fix the issue, but none are allowed.

    I've regenerated a number of times and tried a reboot.

  • August 30, 2017 at 2:49 am

    #1957379

    I'd have to check, but I'm pretty sure that I cover the case of incorrect paths to master. Once you change the paths to master, you can start the server in restricted mode, and then restore the other databases fropm backup.
    You have backups?

    If not, then you'll have to get hold of copies of master and model's data and log files, start SQL in single user mode and then use alter database to change the path of model and tempDB

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 30, 2017 at 4:29 am

    #1957400

    I'm no expert in this.
    The history is:
    1)  Problem started when a patch applied (involuntarily) by Win 10.
    2)  Initial errors indicated master damaged.
    3)  Master inspected (using recovery tool) significantly differs from template master.
    4)  Template version copied to working directory, does not work.  Exceptions still indicate master issues.
    5)  Master rebuilt several times, does not work, exceptions indicate that issues are now with the other two databases, not with master.  I take this to mean that there is progress, master is being loaded,  but that it points to incorrect locations.
    6)  I'm now seeking a way to correct what's in master.

    One of the log lines reads:
    "2017-08-30 13:35:53.30 spid12s  The resource database has been detected in two different locations. Attaching the resource database in the same directory as sqlservr.exe at 'C:\...\Binn\sqlservr.exe' instead of the currently attached resource database at 'E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\mssqlsystemresource.mdf'."

    I don't know where that spurious path came from, and I'd like to correct it.

  • August 30, 2017 at 7:14 am

    #1957423

    MikeGale - Wednesday, August 30, 2017 4:29 AM

    One of the log lines reads:
    "2017-08-30 13:35:53.30 spid12s  The resource database has been detected in two different locations. Attaching the resource database in the same directory as sqlservr.exe at 'C:\...\Binn\sqlservr.exe' instead of the currently attached resource database at 'E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\mssqlsystemresource.mdf'."

    I don't know where that spurious path came from, and I'd like to correct it.

    The path is from the SQL Server installer - you won't find it anywhere. It's virtual and used in the installation process so there isn't anything you can do with it. You would find whatever issues happened with the last installs in the \Setup Bootstrap\Log directory for SQL Server. It could be that the installation was bad or screwed up somehow. The log files should indicate what went wrong on the installation.
    The options you have are to follow the article Gail posted for you as your situation is covered in that article as missing or inaccessible files. I'm not sure if it will work as I have no idea what went wrong with the installation. Also the issues with the resource database file issues, have no idea how you would repoint those as the files are just expected to be in the MSSQL\Binn directory. Your other option would be to uninstall and reinstall.

    Sue

  • September 5, 2017 at 5:10 am

    #1958063

    Thanks for that Gail and Sue.

    Progress report.

    I was able to get further using Gail's article.
    1.  It took past the messages I was getting and said the installation was broken, when I used -T3608.  Apologies for not picking that up more quickly.  (Gave message:  "Your SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.). Please uninstall then re-run setup to correct this problem.")
    2.  Repair and other things tried. no change.
    3.  Used uninstall.
    4.  It had a problem "Uninstall had a problem:
    Action Required
    SQL Server Setup failed after the uninstall process removed files for the feature, and therefore the uninstall process cannot be tried again. Contact Microsoft Customer Support for further assistance.
    Feature failure reason:
    An error occurred for a dependency of the feature causing the setup process for the feature to fail."
    5.  I've tried various things but am stuck with this error 1648, ERROR_PATCH_NO_SEQUENCE.
    6.  I checked whether the problem (probably initially caused by Win 10 update mechanism mal-design) had impacted the OS.  OS looks good to System File Check and OS image checks (SFC and DISM).  So it's SQL Server specific.

    Thanks again.  (To others, beware the ugly things Win 10 can do without permitting any control by the user.)

    Now I need to find a way to manually fix this.

  • September 5, 2017 at 7:36 am

    #1958095

    Try following the steps in this article to uninstall:
    Cleanly Uninstalling Stubborn SQL Server Components

    Sue

  • September 11, 2017 at 5:30 pm

    #1959037

    Hi,
    An update.
    I tried the techniques in the "stubborn" article, they didn't work, but I found something that did.
    Here's a brief summary of what worked for me, might be useful to others in the same boat.

    1. Problem caused by uncontrollable updating in Windows 10, that slipped my notice.
    2. Normal "maintenance" features of SQL install (repair) were no use, normal item by item uninstall couldn't see some things that needed uninstall.
    3. Gail's article https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/ gave a technique that immediately revealed the issues hidden by repair etc.  (<your path>\Sqlservr.exe -T3608).
    4. Uninstall a failure, not recoverable, left machine essentially bricked as a SQL Server.  (Error code: 1648, ERROR_PATCH_NO_SEQUENCE).
    5. Sue's link (https://www.mssqltips.com/sqlservertip/4050/cleanly-uninstalling-stubborn-sql-server-components/) gave valuable techniques for identifying GUID's of recalcitrant programs.  (I added logging to the programmatic invocation of msiexec, /Lime+ <yourpath> \<yourfilename>.log.  This was useful.)
    6. I tracked assembly / component dependencies with uninstalls to get the names of the "blocking" items.  These do not appear in the programs+features list, neither will they uninstall using msiexec.
    7. I found a (recent) Microsoft tool that actually uninstalls things borked in this way.  (It's called "MicrosoftProgram_Install_and_Uninstall.meta.diagcab" named "Fix problems that block programs from being installed or removed" I found a copy at "https://support.microsoft.com/en-nz/help/17588/fix-problems-that-block-programs-from-being-installed-or-removed").  It has a tool to uninstall the otherwise inaccessible material.
    8. After using that I was able to re-install and "unbrick" the machine.

    Good luck to anybody else who encounters a horror like this.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply