quote:

---

IF @dbType != ''
IF EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @dbType)
INSERT INTO #WhichDatabase (dbName)
SELECT @dbType

---

...
CREATE TABLE #WhichDatabase(dbName SYSNAME NOT NULL)

-- put the databases to be backed up into temp table
IF @dbType LIKE '-%'
	IF @bkpType = 'TLog' AND DATABASEPROPERTYEX(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)), 'RECOVERY') = 'SIMPLE'
	BEGIN
		SET @rc = 7
		GOTO EXIT_ROUTINE
	END
	ELSE  IF @edition = 3
	BEGIN
		IF @version = '8' AND EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
		BEGIN
			SET @rc = 8
			GOTO EXIT_ROUTINE
		END
		ELSE IF @version = '9' AND EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
		BEGIN
			SET @rc = 8
			GOTO EXIT_ROUTINE
		END
	END
	ELSE
		INSERT INTO #WhichDatabase(dbName)
		VALUES(REPLACE(@dbType, '-', ''))
ELSE IF @dbType = 'All' 
BEGIN
	IF @edition = 3 AND @version = '8'
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.dbo.sysdatabases
		WHERE 
			[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
			[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
			DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
			DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
		ORDER BY [name]
	ELSE IF @edition = 3 AND @version = '9'
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.sys.databases
		WHERE 
			[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
			[name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
			DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
			DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
		ORDER BY [name]
	ELSE IF @version = '8'
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.dbo.sysdatabases
		WHERE 
			[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
			DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
			DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
		ORDER BY [name]
	ELSE -- version is 9
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.sys.databases
		WHERE 
			[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
			DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
			DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
		ORDER BY [name]
END
ELSE IF @dbType = 'System'
BEGIN
	IF @version = 8
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.dbo.sysdatabases
		WHERE [name] IN ('master', 'model', 'msdb')
		ORDER BY [name]
	ELSE
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.sys.databases
		WHERE [name] IN ('master', 'model', 'msdb')
		ORDER BY [name]
END
ELSE IF @dbType = 'User'
BEGIN
	IF @edition = 3 AND @version = '8'
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.dbo.sysdatabases
		WHERE 
			[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
			[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
			DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
			DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
		ORDER BY [name]
	ELSE IF @edition = 3 AND @version = '9'
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.sys.databases
		WHERE 
			[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
			[name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
			DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
			DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
		ORDER BY [name]
	ELSE IF @version = '8'
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.dbo.sysdatabases
		WHERE 
			[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
			DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
			DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
		ORDER BY [name]
	ELSE
		INSERT INTO #WhichDatabase (dbName)
		SELECT [name]
		FROM master.sys.databases
		WHERE 
			[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
			DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
			DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
		ORDER BY [name]
END
ELSE -- no databases to be backed up
BEGIN
	SET @rc = 9
	GOTO EXIT_ROUTINE
END
...

quote:

---

Originally posted by Kristen

What a horrid bodge - you are being too kind Tara!!

jocampo: Add another optional parameter @SingleDatabaseBackupName or somesuch and use that as the name of the DB to back up.

IMHO @dbType is much better constrained to a sensible size for its intended purpose of holding the TYPE - rather than lose constraints and dual-duty storing something which clearly has nothing to do with the TYPE!

Kristen

---

quote:

---

Originally posted by tkizer

I agree. That part of the code that he posted isn't in my original version.

If you really are using @dbType to store the database name instead of how I originally wrote it, then you do need to change the data type. Then you'll need:

IF EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @dbType)
INSERT INTO #WhichDatabase (dbName) VALUES (@dbType)

Get rid of the IF check for @dbType <> ''.

Tara Kizer

---

quote:

---

Originally posted by tkizer

I'm not sure how it will work actually as I don't know what the problem is yet. I made a suggestion to get rid of one of the IF statements. I don't see a reason to check if @dbType equals ''. That isn't in my original version, so someone added that code.

Tara Kizer

---

CREATE PROC isp_Backup
(@Path VARCHAR(100), @dbName sysname, @Retention INT = 2)
AS

SET NOCOUNT ON

DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
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

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

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

RETURN 0
GO

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

quote:

---

Originally posted by Kristen

"That's why i'm asking help ..."

I thought I'll already given some:
I expect the problem is the

@dbType VARCHAR(6)

declaration - which does not allow for a very long database name!!

Changing your originally posted code to:

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

should fix the problem, but I haven't tested it ...

Kristen

---