newbie question re: sp_msforeachdb

[IT247]

Hello
I am new to admin of sql. will be responsible for backups etc. I am trying out a script from a FAQ. and it requires me to run sp_foreachdb with a created stored proc:

CREATE procedure usp_BackupDatabase
@DatabaseName varchar(100)
as
declare @date varchar(10)
declare @BackupPath varchar(255)
declare @BackupFile varchar(300)

set @BackupPath = 'c:\temp\'
set @date = convert(varchar(10), getdate(), 112)
set @BackupFile = @BackupPath + @DatabaseName + '_' + @date + '.bak'
backup database @DatabaseName to disk=@BackupFile
GO

what i don't understand is:
1)what is the sp_msforeachdb doing
2)How does the name of each db get passed to the variable @databasename

sorry , i am new to this

thx

 

[SQLSister]

sp_MSforeachdb will pull the name of each database on your server. It is what will get the db names for your proc to run.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[IT247]

thx for replying
is @databasename some sort of global var understood by stored procedures?

 

[SQLSister]

what is you exec statment to execute the stored proc? How are you using sp_MSforeachdb?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[mrdenny]

No @databasename is not a global variable. When you run sp_MSforeachdb you put the code that you want to run as a text string after the procedure name. Where you want the database name you put a "?". It will replace the ? with the name of the database when it runs the command.

exec sp_MSforeachdb 'exec usp_BackupDatabase ''?'''

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[IT247]

aha!
i see now
thx very much