I produced a question that can take a database backup at certain specified location. I wish to utilize it like a saved procedure but this will behave as a worldwide saved procedure to ensure that whenever this SP is known as. Then database backup is taken.

It uses DB_Title() to consider database backup of owner database.

Can you really create such SP or Function.

I'm using sql server 2005

first solution:

Should you make your sp within the master database and measure the level like a system object and prefix it with 'sp_' a single copy will exist that'll be shared by all databases.

and second solution from msdn:

Private and global temporary saved methods, similar to temporary tables, could be produced using the # and ## prefixes put into the process title. # denotes a nearby temporary saved procedure ## denotes a worldwide temporary saved procedure. These methods don't exist after SQL Server is shut lower.

a good example :

    USE master
    CREATE TABLE test (c1 VARCHAR(50))
    INSERT test VALUES('master')
    CREATE PROC sp_test AS
    SELECT * FROM test

USE northwind
    CREATE TABLE test (c1 VARCHAR(50))
    INSERT test VALUES('northwind')

USE pubs
    CREATE TABLE test(c1 VARCHAR(50))
    INSERT test VALUES('pubs')

USE pubs
    EXEC sp_test --returns 'master'

USE master
    EXEC sp_MS_marksystemobject sp_test

USE pubs
    EXEC sp_test --returns 'pubs'

USE northwind
    EXEC sp_test --returns 'northwind'