ALTER PROCEDURE sa_BackupWebDatabases

--Name:  		sa_BackupWebDatabases
--
--Purpose:		Creates a nightly backup of all web databases, including any newly created.
--					--Creates directories for any newly created databases.
--						--The account running this must have the appropriate permissions to do this or it will break.
--					--Makes a backup.  Should be scheduled to run nightly.
--
--Type:			System Administration Utility
--
--Format:		EXEC sa_BackupWebDatabases @location
--
--Example_1:	EXEC sa_BackupWebDatabases '\\OFFICECOMPUTER\D$\mssql\backup\'	
--					****This base location MUST already be built.****
--
--Author:			Date:		Type:		Description:
--Derrick Leggett	01/27/2004	Created		Creates backups of all web databases, including any newly created.
--													Will dynamically create the directories needed.
--

@location NVARCHAR(256)

AS

--Record the time procedure started.
SELECT 'Backup Process Starting::' + CONVERT(VARCHAR,GETDATE(),22)

--Declare needed variables.
DECLARE
	@min INT,
	@max INT,
	@sql NVARCHAR(4000),
	@database_name NVARCHAR(256),
	@directory_name NVARCHAR(256),
	@error NVARCHAR(256)

--Create error handle.
SELECT @error = 'sa_BackupWebDatabases::'

--Table to hold the database names.
DECLARE @database TABLE(
	ident INT IDENTITY(1,1) PRIMARY KEY,
	database_name NVARCHAR(256))

INSERT @database(database_name)
	SELECT name 
	FROM master.dbo.sysdatabases
	WHERE name <> 'tempdb'

IF @@ERROR <> 0
	BEGIN
		SELECT @error = @error + 'Failure on @database insert.'
		GOTO ERROR_HANDLE
	END

--Record the time directory process started.
SELECT 'Directory Process Starting::' + CONVERT(VARCHAR,GETDATE(),22)

--Table to hold the directory names.
DIRECTORY: --This section verifies that the appropriate directories exist.  If they do not, it creates them.

IF (SELECT OBJECT_ID('tempdb..#directory')) IS NOT NULL
	BEGIN
		DROP TABLE #directory
	END

CREATE TABLE #directory(
	ident INT IDENTITY(1,1) PRIMARY KEY,
	directory_name NVARCHAR(256))

IF @@ERROR <> 0
	BEGIN
		SELECT @error = @error + 'Failure on #directory create.'
		GOTO ERROR_HANDLE
	END

SELECT @sql = 'DIR/A:D /B ' + @location

INSERT #directory(directory_name)
	EXEC master..xp_cmdshell @sql

IF @@ERROR <> 0
	BEGIN
		SELECT @error = @error + 'Failure on #directory insert.'
		GOTO ERROR_HANDLE
	END

DELETE #directory
WHERE directory_name IS NULL

IF @@ERROR <> 0
	BEGIN
		SELECT @error = @error + 'Failure on #directory delete.'
		GOTO ERROR_HANDLE
	END

SELECT @sql = (
	SELECT TOP 1 
		d1.database_name 
	FROM
		@database d1 
		LEFT OUTER JOIN #directory d2 ON d1.database_name = d2.directory_name
	WHERE
		d2.directory_name IS NULL)

IF @@ERROR <> 0
	BEGIN
		SELECT @error = @error + 'Failure on @sql population with database_name.'
		GOTO ERROR_HANDLE
	END

IF @sql IS NOT NULL
	BEGIN
		SELECT @sql = 'MD ' + @location + @sql
		EXEC master..xp_cmdshell @sql
		GOTO DIRECTORY
	END

IF @@ERROR <> 0
	BEGIN
		SELECT @error = @error + 'Failure on make directory statement.'
		GOTO ERROR_HANDLE
	END

--Record the time directory process ended.
SELECT 'Directory Process Ended::' + CONVERT(VARCHAR,GETDATE(),22)

--Loop through the database names and backup each database.
SELECT
	@min = 1,
	@max = (SELECT MAX(ident) FROM @database)

WHILE @min <= @max
BEGIN

	--Capture the database name.
	SELECT @database_name = (SELECT database_name FROM @database WHERE ident = @min)

	--Record the database backup started.
	SELECT 'Database Backup Started::' + CONVERT(VARCHAR,GETDATE(),22)

	SELECT @sql = '
		BACKUP DATABASE ' + @database_name + '
		TO DISK = ''' + @location + @database_name + '\' + @database_name + '.bak''
		WITH
			INIT,
			STATS = 1'

	PRINT @sql

	EXEC master..sp_executesql @sql

IF @@ERROR <> 0
	BEGIN
		SELECT @error = @error + 'Failure on database backup.'
		GOTO ERROR_HANDLE
	END

	--Record the database backup ended.
	SELECT 'Database Backup Ended::' + CONVERT(VARCHAR,GETDATE(),22)

SELECT @min = @min + 1
END

--Record the time procedure ended.
SELECT 'Backup Process Ended::' + CONVERT(VARCHAR,GETDATE(),22)
RETURN 0

--If an error has occurred RAISERROR, and exit out of the procedure with failure.
ERROR_HANDLE:

SELECT @error = @error + CONVERT(VARCHAR,GETDATE(),22)
RAISERROR(@error,16,1)
RETURN 1

CREATE PROC isp_Backup
(@Path VARCHAR(100), @dbType VARCHAR(6), @Retention INT = 2)
AS

SET NOCOUNT ON

DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @DBName SYSNAME -- stores the database name that is currently being processed
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file

CREATE TABLE #WhichDatabase
(
	dbName SYSNAME NOT NULL
)

-- Get the list of the databases to be backed up
IF @dbType = 'All'

	INSERT INTO #WhichDatabase (dbName)
	SELECT [name]
	FROM master.dbo.sysdatabases
	WHERE [name] <> 'tempdb' 
	ORDER BY [name]

ELSE
BEGIN

	IF @dbType = 'System'

		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.dbo.sysdatabases
		WHERE [name] IN ('master', 'model', 'msdb')
		ORDER BY [name]

	ELSE
	BEGIN

		IF @dbType = 'User'

			INSERT INTO #WhichDatabase (dbName)
			SELECT [name]
			FROM master.dbo.sysdatabases
			WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb') 
			ORDER BY [name]

		ELSE
		BEGIN

			DROP TABLE #WhichDatabase

			RETURN -1

		END

	END

END

-- Get the database to be backed up
SELECT TOP 1 @DBName = dbName
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN 

	-- Get the current date using style 120, remove all dashes, spaces, and colons
	SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

	-- Build the .BAK path and file name
	SELECT @filename = @Path + @DBName + '\' + @DBName + '_' + @Now + '.BAK'
	
	-- Build the dir command that will check to see if the directory exists
	SELECT @cmd = 'dir ' + @Path + @DBName

	-- Run the dir command, put output of xp_cmdshell into @result
	EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

	-- If the directory does not exist, we must create it
	IF @result <> 0
	BEGIN
		
		-- Build the mkdir command		
		SELECT @cmd = 'mkdir ' + @Path + @DBName

		-- Create the directory
		EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

	END
	-- The directory exists, so let's delete files older than two days
	ELSE
	BEGIN

		-- Stores the name of the file to be deleted
		DECLARE @WhichFile VARCHAR(1000)

		CREATE TABLE #DeleteOldFiles
		(
			DirInfo VARCHAR(7000)
		)

		-- Build the command that will list out all of the files in a directory
		SELECT @cmd = 'dir ' + @Path + @DBName + ' /OD'

		-- Run the dir command and put the results into a temp table
		INSERT INTO #DeleteOldFiles
		EXEC master.dbo.xp_cmdshell @cmd

		-- Delete all rows from the temp table except the ones that correspond to the files to be deleted
		DELETE 
		FROM #DeleteOldFiles
		WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @Retention

		-- Get the file name portion of the row that corresponds to the file to be deleted
		SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
		FROM #DeleteOldFiles

		
		SET @RowCnt = @@ROWCOUNT
		
		-- Interate through the temp table until there are no more files to delete
		WHILE @RowCnt <> 0
		BEGIN
		
			-- Build the del command
			SELECT @cmd = 'del ' + @Path + + @DBName + '\' + @WhichFile + ' /Q /F'
			
			-- Delete the file
			EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
			
			-- To move to the next file, the current file name needs to be deleted from the temp table
			DELETE
			FROM #DeleteOldFiles
			WHERE SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))  = @WhichFile

			-- Get the file name portion of the row that corresponds to the file to be deleted
			SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
			FROM #DeleteOldFiles
		
			SET @RowCnt = @@ROWCOUNT
		
		END
		
		DROP TABLE #DeleteOldFiles

	END

	-- Backup the database
	BACKUP DATABASE @DBName
	TO DISK = @filename
	WITH INIT

	-- To move onto the next database, the current database name needs to be deleted from the temp table
	DELETE
	FROM #WhichDatabase
	WHERE dbName = @DBName

	-- Get the database to be backed up
	SELECT TOP 1 @DBName = dbName
	FROM #WhichDatabase

	SET @RowCnt = @@ROWCOUNT
	
	-- Let the system rest for 5 seconds before starting on the next backup
	WAITFOR DELAY '00:00:05'

END

DROP TABLE #WhichDatabase

SET NOCOUNT OFF

RETURN 0







GO

SELECT bs.database_name AS DatabaseName, MAX(bms.physical_device_name) AS FullBackupName
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms 
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s 
ON bs.database_name = s.name
WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND
      s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind') AND
      bs.type = 'D'
GROUP BY bs.database_name