Back up all mssql databases

BackUpAllDatabases.sql

-- NOTE: Backs up to the default backup directory

DECLARE @prefix VARCHAR(20)     -- a prefix for the filename, set below
DECLARE @name VARCHAR(50)       -- database name, set automatically
DECLARE @fileName VARCHAR(256)  -- filename for backup, set automatically
DECLARE @fileDate VARCHAR(20)   -- used for file name, set automatically

SET @prefix = 'Autobackup'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @prefix + '_' + @fileDate + '_' + @name + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor