Cross database queries – or, how to rescue a bad database

Even InaPlex gets it wrong occassionally.

It was late, we were tired, instructions from the client were not clarified properly… and the database was not backed up before a SQL update was issued that created some real problems. The SQL update modified the primary userid of most of the account records in the CRM system.

Of course, the problems were not discovered until the next morning – second last day of the financial year for the client, and the sales people could not see all their accounts in the CRM system because they no longer had appropriate permissions.

The project manager at the client was very polite, but was clearly under substantial pressure to get it fixed.

But the database was live, had been modified, and the most recent backup was a few days old. Rolling back to the backup was out of the question, continuing with the current system was impossible for the sales people.

All in all, not a good situation.

One of the very useful, and relatively unknown, features of SQL in Microsoft SQL Server is that it is possible to execute cross-database queries. This means it is possible to have queries that uses tables from more than one database. Very importantly from our perspective, it means that data in database A can be updated from data in database B.

This gave us a path out of the problem. The old backup was restored to a temporary database, and then a cross database query was used to retrieve the correct field value from the restored database, and update it in the live database. Here’s an example of the type of query used:

update crm.dbo.company 
set 
        crm.dbo.company.comp_primaryuserid = temp.dbo.company.comp_primaryuserid,
        crm.dbo.company.comp_secterr = temp.dbo.company.comp_secterr
from crm.dbo.company inner join temp.dbo.company
      on crm.dbo.company.comp_companyid = temp.dbo.company.comp_companyid
-- where crm.dbo.company.comp_companyid = 59598

This query shows updating the main crm database from the “temp” database, which is the restored backup. The value: “crm.dbo.company.comp_primaryuserid” is the fully qualified name of a field:

database.owner.table.field

Using the fully qualified name, we are able to join the live table and its copy in the backup, and selectively update the damaged fields from the backup.

The commented out “where” clause shows how the query was tested before updating the whole database – a single record ID selected, the query run on that record, and then the results examined.

The cross database query restored the database to a useable stte and, the sales team was able to complete their day. Later, InaPlex very carefully completed the migration – after taking a backup…