SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



----------------------------------------------------------------------------------------------------
-- OBJECT NAME	        : isp_FullBackup_UserDBs
--
-- AUTHOR               : Tara Duggan
-- DATE			: December 18, 2003
--
-- INPUTS		: @Path - location of the backups
-- OUTPUTS		: None
-- DEPENDENCIES	        : None
--
-- DESCRIPTION	        : This stored procedure performs a full backup on all of the user databases
--
-- EXAMPLES (optional)  : EXEC isp_FullBackup_UserDBs @Path = 'G:\MSSQL\Backup\'
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- 
----------------------------------------------------------------------------------------------------
ALTER    PROC isp_FullBackup_UserDBs
(@Path VARCHAR(100))
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 @SQL VARCHAR(7000) -- stores the dynamically created xp_backup_database command
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @NumProcs INT -- stores the number of processors that the server has registered

-- Get the list of the databases to be backed up, does not include master, model, msdb, tempdb, Northwind, or pubs
SELECT name
INTO #WhichDatabase
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'pubs', 'tempdb', 'Northwind', 'DTTS_PANIC_distribution')
ORDER BY name

-- Get the number of processors that the server has
EXEC master..xp_regread 
  @rootkey = 'HKEY_LOCAL_MACHINE', 
  @key = 'SYSTEM\CurrentControlSet\Control\Session Manager',
  @value_name = 'RegisteredProcessors',
  @value = @NumProcs OUTPUT

--  We want n - 1 threads, where n is the number of processors
SELECT @NumProcs = @NumProcs - 1

-- Iterate throught the temp table until no more databases need to be backed up
WHILE (SELECT COUNT(*) FROM #WhichDatabase) <> 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), '-', ''), ' ', ''), ':', '')
	
	-- Get the database to be backed up
	SELECT TOP 1 @DBName = name
	FROM #WhichDatabase

	-- 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

	-- 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		

	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() - 2
		
		-- Interate through the temp table until there are no more files to delete
		WHILE (SELECT COUNT(*) FROM #DeleteOldFiles) <> 0
		BEGIN
		
			-- 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
		
			-- Build the del command
			SELECT @cmd = 'del ' + @Path + + @DBName + '\' + @WhichFile + ' /Q /F'
			
			-- Delete the file
			EXEC master.dbo.xp_cmdshell @cmd
			
			-- 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
		
		END
		
		DROP TABLE #DeleteOldFiles

	END

	-- Build the xp_backup_database command dynamically
	SELECT @SQL = ''
	SELECT @SQL = @SQL + 'EXEC master.dbo.xp_backup_database ' + CHAR(10)
	SELECT @SQL = @SQL + '@database = ''' + @DBName + ''', ' + CHAR(10)
	SELECT @SQL = @SQL + '@filename = ''' + @Path + @DBName + '\' + @DBName + '_LS_Full_' + @Now + '.bkp'', ' + CHAR(10)
	SELECT @SQL = @SQL + '@backupname = ''' + @DBName + ''', ' + CHAR(10)
	SELECT @SQL = @SQL + '@desc = ''Full backup of ' + @DBName + ' - ' + CONVERT(VARCHAR(50), GETDATE()) + ''', ' + CHAR(10)
	SELECT @SQL = @SQL + '@init = 1, ' + CHAR(10)
	SELECT @SQL = @SQL + '@threads = ' + CONVERT(CHAR(1), @NumProcs) + CHAR(10)

	-- Backup the database using xp_backup_database
 	EXEC (@SQL)

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

	-- 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

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO