use master;
go
DECLARE UserDB_Cursor Cursor
FOR
-- Selecting user database names.
select name as DatabaseName
from sys.sysdatabases
where ([dbid] > 4) and ([name] not like '$')
OPEN UserDB_Cursor
DECLARE @dbName varchar(100);
DECLARE @dbbackupPath varchar(100);
DECLARE @dbbackupQry varchar(500);
-- make sure that the below path exists
set @dbbackupPath = 'c:\SQLBackupFolder\'
Fetch NEXT FROM UserDB_Cursor INTO @dbName
While (@@FETCH_STATUS <> -1)
BEGIN
-- Backup SQL statement
set @dbbackupQry = 'backup database ' + @dbName + ' to disk = ''' + @dbbackupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak'''
-- Print SQL statement
print @dbbackupQry
-- Execute backup script
EXEC (@dbbackupQry)
-- Get next database
Fetch NEXT FROM UserDB_Cursor INTO @dbName
END
CLOSE UserDB_Cursor
DEALLOCATE UserDB_Cursor
GO