use master
go

if object_id('sp_ABDumpAllDatabases') is not null
        drop procedure sp_ABDumpAllDatabases
GO

CREATE PROCEDURE sp_ABDumpAllDatabases
@DeviceName        varchar(30),
@DatabasesToSkip   char(100) = ' pubs tempdb ',  -- space separated list of databases to skip
@TruncateLog       int = 1

/* Name:    sp_ABDumpAllDatabases
 *
 * Purpose: This procedure will iterate through the list of databases on a server, dumping 
 *          them to a given backup device.  The first database dumped (master unless skipped) 
 *          will initialize the dump device, while subsequence dumps append to it.
 *
 *          This routine is most useful in a development environment, where new databases
 *          may be added before backup is planned for them.  
 *
 *          If you wish to skip databases (tempdb and pubs are always skipped), include the
 *          space separated list in @DatabasesToSkip.  By default, the transaction is also 
 *          truncated after the dump for any databases with a separate log device.
 *        
 * Written By:  Vince Iacoboni   02/11/1998
 *              vince.iacoboni@btalexbrown.com
 *              vbi@writeme.com
 *
 * Modification History: 
 *
 *
 *
 */


AS

DECLARE @DbId int, 
        @DbName varchar(30),
        @InitOption varchar(20),
        @PrintMsg varchar(80)

SELECT @DatabasesToSkip = ' pubs tempdb ' + rtrim(@DatabasesToSkip)

SELECT @DbId = 1, 
       @InitOption = ' WITH INIT'

WHILE @DbId <= (SELECT max(dbid) 
                  FROM master..sysdatabases)
 BEGIN
    SELECT @DbName = db_name(@DbId)

    IF @DbName IS NOT NULL AND @DatabasesToSkip NOT LIKE '% ' + @DbName + ' %' 
     BEGIN
        SELECT @PrintMsg = 'Dumping ' + @DbName + ' at ' + convert(varchar(25), getdate(), 101) + ' ' + convert(varchar(25), getdate(),14)
        PRINT @PrintMsg
        EXEC('DUMP DATABASE ' + @DbName + ' TO ' + @DeviceName + @InitOption)
        IF @TruncateLog = 1 AND EXISTS (SELECT * 
                                          FROM master..sysusages 
                                          WHERE dbid = @DbId
                                            AND segmap & 7 = 4)  -- Log Only Segment
            EXEC('DUMP TRANSACTION ' + @dbname + ' WITH TRUNCATE_ONLY')
        SELECT @InitOption = ' WITH NOINIT'
     END

    SELECT @DbId = @DbId + 1
 END

RETURN 0

GO