This one is nice and easy.
If you ever want to backup all your databases on an instance use the following script bellow.
I picked the original script from here and changed it to suite my needs.

I use it on a SQL Job and save the backup files to a folder under my DropBox… pure magic!

Implementation

  • The Job runs every day
  • Full backup every month and Diffs every day
  • The file name is the database name + year + month
  • Excludes all the system databases

The Script

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 

DECLARE @fileDate VARCHAR(20) -- used for file name 
DECLARE @fileYear VARCHAR(20) -- used for file name 
DECLARE @fileMonth VARCHAR(20) -- used for file name 

SET @path = 'DESTINATION PATH' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT @fileYear = CAST(YEAR(GETDATE()) AS VARCHAR)
SELECT @fileMonth = RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR), 2)


DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + '_' + @fileYear + @fileMonth + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName WITH DIFFERENTIAL

FETCH NEXT FROM db_cursor INTO @name 
END

CLOSE db_cursor;
DEALLOCATE db_cursor;