Is there a way in SQL Server to uniquely identify a database?

Question

Is there any way to uniquely identify a database?

If we were to copy a database to another machine, this instance is assumed to be different. I checked on master tables, but could not identify any information that can identify this.

Answer 1

You could make a table in it with a unique name, and simply do a query on that. It's a bit of a hack, sure, but it'd work...

Answer 2

Create a scalar function that returns an ID/Version number:

create function fnGetThisDBID() returns varchar(32) as begin
    return ('v1.1,origin=server1')
end 

select 'version is: ' + dbo.fnGetThisDBID()

Answer 3

You could put the information in an extended property associated with the database itself:

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'AdventureWorks2008R2 Sample OLTP Database';
GO

http://msdn.microsoft.com/en-us/library/ms190243.aspx

In your case, I would use something like this:

EXEC sys.sp_addextendedproperty 
@name = N'UniqueID', 
@value = N'10156435463';

select objname, [name], [value] 
from fn_listextendedproperty (null, null, null, null, null, null, null)

Answer 4

service_broker_guid in sys.databases comes pretty close to what you ask. It is a uniqueidentfier generated when the database is created and is preserved as the database is moved around (detach and attach, backup and restored, server rename etc). It can be explicitly changed with ALTER DATABASE ... SET NEW_BROKER;.