sp_BackupDatabases on SQL Express

sp_BackupDatabases.sql

USE [master] 
GO 
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

IF OBJECT_ID('sp_BackupDatabases') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[sp_BackupDatabases]  
END

GO

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200) 
AS 
	-- ============================================= 
	-- Author: Microsoft 
	-- Create date: 2010-02-06
	-- Description: Backup Databases for SQLExpress
	-- Parameter1: databaseName 
	-- Parameter2: backupType F=full, D=differential, L=log
	-- Parameter3: backup file location
	-- =============================================
         
	-- Example: EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'
	-- Example: EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='D'
	-- Example: EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='L'
        
	SET NOCOUNT ON; 
                   
    DECLARE @DBs TABLE
    (
        ID int IDENTITY PRIMARY KEY,
        DBNAME nvarchar(500)
    )
                   
    -- Pick out only databases which are online in case ALL databases are chosen to be backed up
    -- If specific database is chosen to be backed up only pick that out from @DBs
    INSERT INTO @DBs (DBNAME)
    SELECT Name FROM master.sys.databases
    where state=0
    AND (name=@DatabaseName OR @DatabaseName IS NULL)
    ORDER BY Name
                   
    --Filter out databases which do not need to backed up
	--IF @backupType='F'
	--BEGIN
	--    DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
	--END
	--ELSE IF @backupType='D'
	--BEGIN
	--	DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
	--END
	--ELSE IF @backupType='L'
	--BEGIN
	--    DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
	--END
	--ELSE
	--BEGIN
	--    RETURN
	--END

	DELETE @DBs where DBNAME IN ('master','model','msdb','tempdb','Northwind','pubs','AdventureWorks')
                   
    -- Declare variables
    DECLARE @BackupName varchar(100)
    DECLARE @BackupFile varchar(100)
    DECLARE @DBNAME varchar(300)
    DECLARE @sqlCommand NVARCHAR(1000) 
	DECLARE @dateTime NVARCHAR(20)
    DECLARE @Loop int                  
                               
    -- Loop through the databases one by one
    SELECT @Loop = min(ID) FROM @DBs
         
	WHILE @Loop IS NOT NULL
	BEGIN
         
	-- Database Names have to be in [dbname] format since some have - or _ in their name
	SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
         
	-- Set the current date and time n yyyyhhmmss format
	SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
         
	-- Create backup filename in path\filename.extension format for full,diff and log backups
	IF @backupType = 'F'
		SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
	ELSE IF @backupType = 'D'
		SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
	ELSE IF @backupType = 'L'
		SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
         
	-- Provide the backup a name for storing in the media
	IF @backupType = 'F'
		SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
	IF @backupType = 'D'
		SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
	IF @backupType = 'L'
		SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
         
	-- Generate the dynamic SQL command to be executed
         
	IF @backupType = 'F' 
    BEGIN
        SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
    END
	IF @backupType = 'D'
    BEGIN
        SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
    END
	IF @backupType = 'L' 
    BEGIN
        SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
    END
         
	-- Execute the generated SQL command
	EXEC(@sqlCommand)
         
	-- Goto the next database
	SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
         
END