Recently I was applying Service Pack on one of the instances on my Virtual Server. It is not uncommon for my laptop to run out of memory because it is *loaded* beyond its capacity. That day as well the installer froze on me and I had the Power Off the VM. When the Server was restarted, SQL Server greeted me with this error.
Error: 5173, Severity: 16, State: 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup
Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatamssqlsystemresource.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Error: 945, Severity: 14, State: 2
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details
The unfinished SQL Server Service Pack setup had corrupted the Resource database. It had not partially upgraded the instance but got hung while the Resource database was being upgraded. As the error message states, the version of the data file did not match that of the transaction log file for the resource database. This left the instance and the resource database running in different versions.
What next? I applied the Service Pack on another instance which completed successfully. Then I copied the
mssqlsystemresource.ldf files from that instance and overwrote those files in the
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder of the instance having trouble starting. And SQL Server started without any issues.
This approach worked out well since only the resource database of the older version. Often DBA takes the backup of all databases before applying hotfixes/service packs but forgets about mssqlsystemresource database. Backing up this databases is nothing more than copying the data and transaction log file to a different location. Restoring the resource database is also overwriting the existing copy of the files with the *backed up* files.