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