USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[prc_backup_to_remote_server]    Script Date: 12/12/2011 06:30:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[prc_backup_to_remote_server]
AS
BEGIN

SET NOCOUNT ON

DECLARE DATABASE_BACKUP Cursor 
FOR 

select [name] from master..sysdatabases
where [name] not in(
'master',
'tempdb',
'model',
'distribution'
)

Open DATABASE_BACKUP

DECLARE @Name varchar(100)

Fetch NEXT FROM DATABASE_BACKUP INTO @Name

While (@@FETCH_STATUS <> -1)
BEGIN

DECLARE @month varchar(2)
DECLARE @date varchar(2)
DECLARE @year varchar(4)
DECLARE @SQL varchar(max)

SELECT @month = DATEPART(MM,GETDATE())
SELECT @date = DATEPART(DD,GETDATE())
SELECT @year = DATEPART(YYYY,GETDATE())

SELECT @SQL ='BACKUP DATABASE '+CONVERT(VARCHAR(100),@Name)+'
To DISK='+''''+'\\P.Q.R.S\DBBACKUPS\'+CONVERT(VARCHAR(100),@Name)+''+CONVERT(VARCHAR(1),'_')+CONVERT(VARCHAR(2),@month)+CONVERT(VARCHAR(1),'_')+
CONVERT(VARCHAR(2),@date)+CONVERT(VARCHAR(1),'_')+CONVERT(VARCHAR(4),@YEAR)+'.BAK'''

EXEC(@SQL)

FETCH NEXT FROM DATABASE_BACKUP INTO @Name
END
CLOSE DATABASE_BACKUP
DEALLOCATE DATABASE_BACKUP

END