USE DBAdmin
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE WITH OVERRIDE
GO
CREATE PROCEDURE asp_backupdatabases @directory VARCHAR(256), @backuptype CHAR(1) = 'F',
@verify BIT = 0, @compress BIT = 1
AS
--This procedure will backup all of the databases on an instance based on the 
--backup type that you specify F=Full, D=Differential, L=Tran Log.
--It will skip any databases that cannot be backed up.
--It will also create the specified directory if it does not always exist.
--All backup file names are created as instance_database_yyyymmddhhmm.extension.
--By default backups will be compressed as well as verified.
DECLARE @dircmd     VARCHAR(400),
        @cmd        NVARCHAR(max),
        @filename   VARCHAR(400),
        @return     INT,
        @msg        VARCHAR(400),
        @database   SYSNAME,
        @counter    INT = 1,
        @numrows    INT = 0

DECLARE @dblist     TABLE (
                        ID             INT     IDENTITY(1,1),
                        DatabaseName    SYSNAME NOT NULL)

--Test for valid backup types
IF @backuptype NOT IN ('F','D','L')
BEGIN
    PRINT 'You must specify a valid backup type of F, D, or L'
    RETURN
END

--Create backup directory if it does not already exist 
SELECT @dircmd = 'dir "' + @directory + '"' 

EXEC @return = master.dbo.xp_cmdshell @dircmd, NO_OUTPUT 

IF @return <> 0 
BEGIN 
    SELECT @dircmd = 'md "' + @directory + '"' 

    EXEC @return = master.dbo.xp_cmdshell @dircmd, NO_OUTPUT 

    IF @return <> 0 
    BEGIN 
        SELECT @msg = ' Error - Unable to create backup directory (' + @directory + ')' 

        RAISERROR(@msg,16,1) 

        RETURN 1 
    END 
END 

IF @backuptype = 'L'
BEGIN
    INSERT INTO @dblist
    (DatabaseName)
    SELECT name 
    FROM sys.databases
    WHERE source_database_id IS NULL
    AND DATABASEPROPERTYEX(DB_NAME(database_id), 'Recovery') IN ('FULL','BULK_LOGGED')
    AND DATABASEPROPERTYEX(DB_NAME(database_id), 'Status') = 'ONLINE'
    AND DATABASEPROPERTYEX(DB_NAME(database_id), 'IsInStandby') = 0
    AND DATABASEPROPERTYEX(DB_NAME(database_id), 'Updateability') = 'READ_WRITE'
    AND name != 'tempdb'
END
ELSE
BEGIN
    INSERT INTO @dblist
    (DatabaseName)
    SELECT name 
    FROM sys.databases
    WHERE source_database_id IS NULL
    AND DATABASEPROPERTYEX(DB_NAME(database_id), 'Status') = 'ONLINE'
    AND DATABASEPROPERTYEX(DB_NAME(database_id), 'IsInStandby') = 0
    AND DATABASEPROPERTYEX(DB_NAME(database_id), 'Updateability') = 'READ_WRITE'
    AND name != 'tempdb'
END

--Example of using a table variable and WHILE loop instead of a cursor
SELECT @numrows = COUNT(*) FROM @dblist
 
WHILE @counter <= @numrows
BEGIN
    SELECT @database = DatabaseName
    FROM @dblist
    WHERE ID = @counter
    
    --Build the Backup File Name in the form of <dir><servername><databasename><date+time>.<extension>
    SELECT @filename = @directory + '\' + REPLACE(@@SERVERNAME,'\','_') + '_' + REPLACE(@database,'.','_')
                    + '_' + CONVERT(varchar(12),GETDATE(),112) + REPLACE(CONVERT(varchar(12),GETDATE(),8),':','') 
                    + CASE WHEN @backuptype = 'L' THEN '.trn'
                            WHEN @backuptype = 'F' THEN '.bak'
                            WHEN @backuptype = 'D' THEN '.diff' END

    --Build the backup command
    SET @cmd = 'BACKUP ' + CASE WHEN @backuptype = 'L' THEN 'LOG ' ELSE 'DATABASE ' END +
        @database + ' TO DISK = ''' + @filename + '''' + 
        CASE WHEN @backuptype = 'D' AND @compress = 1 THEN ' WITH DIFFERENTIAL, COMPRESSION'
            WHEN @backuptype = 'D' AND @compress = 0 THEN ' WITH DIFFERENTIAL'
            WHEN @backuptype IN ('F','L') AND @compress = 1 THEN ' WITH COMPRESSION'
            WHEN @backuptype IN ('F','L') AND @compress = 0 THEN '' END
    
    EXEC sp_executesql @cmd
    
    IF @verify = 1
    BEGIN
        SET @cmd = 'RESTORE VERIFYONLY FROM DISK = ''' + @filename + ''''

        EXEC sp_executesql @cmd
    END
    
    SET @counter += 1;
END
GO

--Test
--No verify and compress
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'F', 0, 1
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'D', 0, 1
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'L', 0, 1
--No verify and no compress
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'F', 0, 0
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'D', 0, 0
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'L', 0, 0
--Verify and compress
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'F', 1, 1
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'D', 1, 1
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'L', 1, 1
--Verify and no compress
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'F', 1, 0
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'D', 1, 0
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup', 
    'L', 1, 0
--Verify, compress, and make new directory
exec asp_backupdatabases 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test', 
    'F', 1, 1
