Disaster Recovery – SQL Express databases

Posted: February 9, 2013 in Solved, SQL, TMG2010, Windows General
Tags: , , , , ,

The continuing journey in the very slow quest to restore everything.

This post is the recovery of the SQL Express database used by the Threat Management Gateway (TMG 2010) firewall. The SQL Server (ISARS) and (MSFW) instances were failing to start with an eventid:9003 message saying “The log scan number passed to log scan in database ‘master’ is not valid”

Essentially it would appear the MASTER database was corrupted, and as per the other servers the backup was also messed up. :sigh:

SOLUTION

In this case it seems the fix was really simple:

1. Locate the master database files for each instance: e.g. C:\Program Files\Microsoft SQL Server\MSSQL10.ISARS\MSSQL\Data\master.mdf and mastlog.ldf

2. Rename them just for backup in case (although it probably doesn’t matter as they don’t work anyway)

3. In the MSSQL\Template Data folder, COPY the master.mdf and mastlog.ldf files into the DATA directory

4. Repeat for the SQL instance in the other directory (MSSQL10.MSFW)

So that’s sorts out the initial problem with the SQL Express, now for some cleanup for TMG. when the services start, they try to re-create the TMG database because the link to the existing ones no longer exists and they can’t be “trusted”. So we just rename the old TMG databases and let it create new ones. If you need to access those old databases, just copy them off and mount them on another SQL server instance.
1. Locate MSSQL10.ISARS\MSSQL\DATA\ISA_RS_Db.mdf and ISA_RS_Db_log.ldf and rename them (just add _old or something to the name)
2. Locate MSSQL10.MSFW\MSSQL\DATA\CachePerfCounters.mdf and CachePerfCounters_log.ldf and rename them as well

Now when you start the Firewall Service, it will recreate those database and you should be back in action.

Comments
  1. Nicholas says:

    Hi,

    Thanks for the guide. However, after getting the SQL instances up and restarting all the TMG servcies, I still can’t connect to TMG. In the event viewer, I’m getting information event from MSSQL$ISARS with the following message,
    “Login failed for user ‘NT AUTHORITY\SYSTEM’. Reason: Failed to open the explicitly specified database.”

    From the Details view, I can see that the specified database is master.

    Any help would be appreciated

  2. Scott says:

    It sounds like the ISA_RS_DB database files are still corrupted or missing. Is the ISARS instance starting correctly? You might need to give it a minute after starting to see if it has failed and stopped again.

    • Nicholas says:

      ISARS instance started without issue. I checked the trc file and found out that the issue is on the reporting database. Restarting the reporting services added error event stating that database “ReportServer$ISARS” login failed.

      Is there a way to recreate this database?

      Thanks.

      • Nicholas says:

        Ok, I tried to use Reporting Services Configuration Manager to create a new database, but the issue now is that I can’t login to the ISARS service using Integrated account. I tried local admin and domain admin. Both can’t login. Any idea?

        Thanks.

        • Scott says:

          Sorry no. My knowledge of SQL Express is rather limited and only whatever I needed to work out to fix the problems I encounter. I usually try to avoid it whenever I can, but left it on my TMG when I had more issues using an external SQL for logging.
          My strategy now is to just make sure I have a backup at least once a month that I can just restore the DB if it gets corrupted again.

        • Nicholas says:

          I managed to get this issue resolved. After replacing the master database from Data Template, the user roles required are missing. Since my server has some corruption (fixed by chkdsk), I can’t see the SQL instances in SQL Server Configuration. I fixed that by following this, http://www.mssqltips.com/sqlservertip/2492/why-is-sql-server-configuration-manager-missing-services

          Then re-add some roles by following this, http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

          However, I still have one more issue, the Forefront TMG Management cannot connect to the server stating “unable to connect to configuration storage server”, The service is running fine though.

        • Scott says:

          Sound like you’re slowly getting there🙂 Thanks for updating with your fixes, I’m sure mine will fail again at some time and need more fixing as well.

          If you are able to runt he console on the TMG server at all (locally?) then it might just be easier at this point to jump in and do a full export/backup of the TMG config, then wipe and rebuild followed by a restore of the config. I’ve done that before and found it is sometimes much quicker than trying to battle through solving some small obscure issue that leave the server in a working but potentially unreliable config state.

        • Nicholas says:

          I finally resolved the issue connecting to TMG server using TMG Management. As mentioned before, since the databases are reset, the permission settings are also lost. This actually caused the service “ISASTGCTRL” to fail to start. This service is named as such in services.msc, which is not grouped together with the other TMG services. To resolve this issue, go to C:Program Files\Microsoft Forefront Threat Management Gateway\ADAMData and delete (or copy to other location) all the files except adamntds.dit. Please note that adamntds.dit must not be touched or you’ll be in trouble later. Restart the service and the everything would be recreated for you and now TMG Management can connect to the TMG.

        • Scott says:

          Thanks for the follow up Nicholas.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s