CREATE PROCEDURE BACKUP_SP AS


DECLARE 	@NAME	VARCHAR(100),
		@DBNAME	VARCHAR(100)


DECLARE  BACKUP_CUR  CURSOR  FOR SELECT name FROM sysdatabases where name not in('model','pubs','tempdb')

OPEN BACKUP_CUR

FETCH NEXT FROM BACKUP_CUR INTO @DBNAME

WHILE @@FETCH_STATUS=0

     BEGIN

	SELECT @NAME='\\ip-address\share_name\'+@@SERVERNAME+'_'+@DBNAME+'_'+SUBSTRING(DATENAME(DW,getdate()),1,3)+'Dump.bak'

	BACKUP DATABASE @DBNAME TO  DISK = @NAME  WITH INIT ,  NOUNLOAD ,  NAME = @DBNAME,  NOSKIP ,  STATS = 10,  NOFORMAT 

	FETCH NEXT FROM BACKUP_CUR INTO @DBNAME

     END

CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR