Search code examples
sqlsql-serverguididentification

How to identify a database when it's being moved to another server/instance

I'm writing a tool which is used to perform several database operations.
But the tool should only be used with one specific database.

Now I'm looking for a way, to securely identify the database, the tool is connected to.

First I thought about just checking a string like SERVERNAME\INSTANCE#Database.
Also I found this question where the solution is to use a GUID, but this GUID changes if the DB is restored on another server.

The DB should be recognized even when it is being moved to another server or instance, or if the database name changes.

Is there a reliable way to achieve this?

Solution

  • You might be able to achieve this with an extended property.

    To create:

    exec sp_addextendedproperty @name = 'dbUniqueIdentifier' @value = 'ABCD1234'

    To confirm:

    select value from sys.extended_properties where name = 'dbUniqueIdentifier'

    In my organization, we use extended properties to identify which build and changeset the database schema came from. The properties survive backup/restore/migration.