I recently had a request from a client to come up with a routine to do a full compressed database backup of all of the user databases on an instance of SQL Server 2008. They wanted to be able to add and delete databases with having to worry about changing the backup routine. All of their databases are using the Simple recovery model (which meets their stated  RPO objective). After a little thought, I whipped up a stored procedure that is called by a SQL Server Agent job, that seems to work pretty well.

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