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]

        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 @databaseName sysname;

        DECLARE curDatabaselist CURSOR

            -- 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
                    -- 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;
        CLOSE curDatabaselist;
        DEALLOCATE curDatabaselist;
About these ads
This entry was posted in SQL Server 2008, SQL Server 2008 R2. Bookmark the permalink.

8 Responses to Dynamically Backing Up All User Databases in SQL Server

  1. Andrew Wickham says:

    We do something very similar for several remote servers (running SQL Server Express), and one issue we came across down the road was offline databases. We had to make sure we were looking at user databases, and the state was online.

    SELECT db.[name] AS [DatabaseName]
    FROM sys.databases AS db
    WHERE db.database_id > 4 AND state_desc = ‘ONLINE';

  2. Uri Dimant says:

    Hi Glenn

  3. Naomi says:

    I have a similar script, but I don’t use a loop. Check this blog post How to get information about all databases without a loop
    the particular script is very close to the bottom of this blog.

  4. Another problem with this script — and SQL-based solutions in general — is that there will be no concurrency. Each backup will wait for all the backups before it to complete, and only one will run at a time. Depending on the available hardware, this might not be a real concern. If not enough bandwidth or space is available to do multiple backups concurrently, then there would be no performance gain and the contention might actually cause an overall slowdown. On the other hand, if the hardware is substantial and the database aren’t that demanding, then there’s benefit in running backups concurrently.

    • Glenn Berry says:

      In this case, the client has a number of very small databases. The compressed, full backups take only a few seconds each. Running the backups serially does not cause any issues here. If we had very large databases, I would not use a solution like this either.

  5. Shaun says:

    Why even expend a little thought on comping up with this? Why wouldn’t you just make a maintenance plan that backs up all user databases? That handles offline databases and compression as well as handling newly created databases.

  6. KenJ says:

    You’ll also want to watch out for database snapshots. One more thing for the where clause:

    SELECT db.[name] AS [DatabaseName]
    FROM sys.databases AS db
    WHERE db.database_id > 4
    and state_desc = ‘ONLINE’
    and source_database_id IS NULL;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s