Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dynamically Backing Up All User Databases in SQL Server

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

Comments

Posted by Jason Brimhall on 17 November 2010

I use a similar routine - but what I use relies on info stored in a table.  In the table, one can set the number of backup files for the db, the retention, compression, and the frequency of backup.  It works pretty well - but does require a little admin time.

Posted by Glenn Berry on 17 November 2010

Jason,

That is a more complete solution. My client wanted something that required no admin work at all. I considered using a table that I could dynamically populate and update, but this seemed simpler.

Leave a Comment

Please register or log in to leave a comment.