How to Dynamically Backup and Compress User Databases (SQL 2008)

A simple script to dynamically backup simple recovery mode databases (with compression) under SQL 2008. Using this script, users are able to create and remove databases without needing to update backup maintenance plans.

The SP gets a list of all of the database names and puts that in a FAST_FORWARD cursor. Then, for each database name, it builds an ad-hoc SQL string for the BACKUP DATABASE command, which then runs the backup for each user database. These are pretty small databases, but there are a lot of them. I could probably improve this by using sp_executesql and using parameters for some of the hard-coded values.

CREATE PROCEDURE [dbo].[BackupAllUserDatabases]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CompleteCommand nvarchar(1000);
DECLARE @PartOne nvarchar(20) = N'BACKUP DATABASE [';
DECLARE @PartTwo nvarchar(20) = N'] TO  DISK = ';
DECLARE @FilePathPrefix nvarchar(20)= N'''D:SQLBackups';
DECLARE @FilePathSuffix nvarchar(30)= N'FullCompressed.bak''';
DECLARE @PartThree nvarchar(100) = N' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;';
DECLARE @databaseName sysname;
DECLARE curDatabaselist CURSOR
FAST_FORWARD
FOR
-- Get list of user databases
SELECT db.[name] AS [DatabaseName]
FROM sys.databases AS db
WHERE db.database_id > 4;
OPEN curDatabaselist;
FETCH NEXT
FROM curDatabaselist
INTO @databaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build dynamic SQL string
SET @CompleteCommand = @PartOne + @databaseName + @PartTwo + @FilePathPrefix;
SET @CompleteCommand = @CompleteCommand + @databaseName + @FilePathSuffix + @PartThree;
PRINT @CompleteCommand;
-- Run the completed command
EXECUTE (@CompleteCommand);
FETCH NEXT
FROM curDatabaselist
INTO @databaseName;
END
CLOSE curDatabaselist;
DEALLOCATE curDatabaselist;
END

To capture only online databases, add the following to the above code logic;

SELECT db.[name] AS [DatabaseName]

FROM sys.databases AS db

WHERE db.database_id > 4 AND state_desc =