2

There are many SQL Server databases created from the same .BAK file. I need to get the unique ID-value of each one. Getting UID of the DBfiles returns everywhere the same value.

Different situation with Oracle. It has a unique ID of each DB service.

Please, let me know what trick or method would be used in my case? It should be just one SQL query.

Thanks.

TT.
  • 15,774
  • 6
  • 47
  • 88
  • 2
    Talk about vague....you can look in sys.databases to get these values. This would be after the restore is complete. You could do a restore headeronly to get the database names that are in the backup set. It is so unclear what you are trying to do I can't begin to offer much real advice. – Sean Lange Mar 09 '16 at 14:57

2 Answers2

0

This should do it:

Select * from Sys.Databases

In your case I think you need name, database_id and owner_sid columns, not sure if they're all relevant to your question.

geco17
  • 5,152
  • 3
  • 21
  • 38
  • Hi, I can see service_broker_ guid and owner_sid, which one is for sure unique? –  Mar 09 '16 at 15:11
  • will check it up morrow –  Mar 09 '16 at 20:58
  • @VitaliPetrov unfortunately I can't check now but the database id will be unique for sure. What version of SQL server are you using? What columns do you see? If you check the msdn link at https://msdn.microsoft.com/en-us/library/ms178534.aspx it gives a detailed description of which columns might be of interest to you (name and database id are both be unique). The service_broker_guid is listed as "uniqueidentifier". – geco17 Mar 09 '16 at 21:26
  • database id is numbered incrementally from 1 upwards, it is the opposite of unique. Better to get **database_guid** value from **sys.database_recovery_status** as answered [here](https://stackoverflow.com/questions/10916991/obtaining-a-unique-database-identifier-for-sql-server-2005-and-later) – pholcroft Dec 17 '20 at 15:23
0

I needed to get a unique database ID for product licensing reasons and eventually went with service_broker_guid. Eg

SELECT db.service_broker_guid FROM sys.databases db WHERE db.name = 'MyDatabase';

Result: ED2E8477-CDC3-49DF-ABEE-341B3E9277DF

NOTE: This value is usually 00000000-0000-0000-0000-000000000000 for databases where is_broker_enabled = 0. In our case, though, we just hunted for the first value that wasn't zeros:

SELECT TOP(1) service_broker_guid FROM sys.databases WHERE service_broker_guid <> '00000000-0000-0000-0000-000000000000' ORDER BY database_id;

I think you're fairly safe with that unless the database is moved or recreated but I haven't exhaustively tested that. (We'd just issue a new license key in the event of a customer issue.)

SteveCinq
  • 1,920
  • 1
  • 17
  • 22