I created a query that takes a database backup at certain specified location.
I want to use it as a stored procedure but this should act as a global stored procedure so that whenever this SP is called. Then database backup is taken.
It uses DB_Name() to take database backup of owner database.
Is it possible to create any such SP or Function.
I am using sql server 2005
If you create your sp in the master database and mark it as a system object and prefix it with 'sp_' then a single copy will exist that will be shared by all databases.
and second solution from msdn:
Private and global temporary stored procedures, analogous to temporary tables, can be created with the # and ## prefixes added to the procedure name. # denotes a local temporary stored procedure; ## denotes a global temporary stored procedure. These procedures do not exist after SQL Server is shut down.
an example :
USE master CREATE TABLE test (c1 VARCHAR(50)) INSERT test VALUES('master') go CREATE PROC sp_test AS SELECT * FROM test GO 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'