I added some interisting trick to the script. Like creating a sub directory for each database (required in my environment) and detecting offline database (it`s impossible to backup).

Have fun!

DECLARE @cursor AS CURSOR

DECLARE @dbname AS VARCHAR(20),

@query AS VARCHAR(100)

SET @cursor = CURSOR SCROLL FOR

SELECT NAME FROM MASTER..Sysdatabases

WHERE NAME NOT IN ('tempdb', 'ReportServerTempDB') AND cast(status as integer) & 512 <> 512

OPEN @cursor

FETCH NEXT FROM @cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @query = 'master.dbo.xp_create_subdir N''D:\Backup\'+ @dbname+ ''''

EXEC(@query)

SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''D:\backup\'+ @dbname+'\'+ @dbname+'.bak '' WITH INIT'

EXEC(@query) FETCH

NEXT FROM @cursor INTO @dbname

END

CLOSE @cursor

DEALLOCATE @cursor

Dominique St-Pierre Boucher

MCDBA, MCT