Unique identifier per database "copy" on Azure?



  • Every few weeks I delete our Test database and make a new copy from Production.

    I do want to run some scripts (from our application code) the first time one of these "freshly" created test copies are detected on startup of our application.

    Say that every "copy" has a real Unique Identifier (a GUID or whatever) that is NOT copied along but generated with the new copy then I could do something similar like this:

    • save this value in a normal table (SavedValue)
    • this value WILL be copied along from Production when I create a new Test database
    • if ( SavedValue != GUID ) { SavedValue = GUID; RunCleanupScripts(); }

    So - is there any value like that I can extract from an Azure database? One that will keep consistent with the lifetime of an "Azure SQL Database"?

    Some notes:

    • I want to keep the name of the Test database the same when I make a fresh copy. The format in our architecture is always XXX.ProductionDb and XXX.TestDb
    • I am sure there are a lot of alternative ways of doing this, but because of a lot of complicated issues out of this scope I am right now looking for a solution where I can "detect" a fresh Test database copy from our application code.
    • This script will only run in our Test environments (no risk for bugs on Production environments)


  • Azure SQL Database https://docs.microsoft.com/en-us/azure/azure-sql/database/features-comparison#features-of-sql-database-and-sql-managed-instance , however, databases are Broker Enabled when deployed to an Azure SQL Server. This means there is a unique Service Broker Guid assigned to each database, including copied, restored or manually created databases.

    You can fetch it with this query:

    SELECT name, service_broker_guid FROM sys.databases
    

    The query can be run from master for all databases, or from the database itself for only that database's guid value.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2