Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2000 Backups


SQL Server 2000 Backups

Author
Message
Fred Stemp
Fred Stemp
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 219
Hey gang!

Kinda stuck here and hoping for a fresh view point. I have a SQL Server 2000 instance that has 70+ DBs on it. My goal is this; I need to run a daily incremental of the DBs, excluding system DBs, then check the DB backup locations for BAK files 8 days or older and delete them to help me manage space on this server. I have the Backup portion, CURSOR to identify DBs and exclude System DBs as well as run the backup, of my script running smoothly. It's the dynamic SQL statement that creates a xp_cmdshell command, parameter is identified as @FileDelete at the bottom of the script, that is giving me fits and continually failing within the Job. Any thoughts or insights would be greatly appreciated!



USE master

DECLARE @DBName VARCHAR(100)

-- CURSOR to lookup DBNames in master DB excluding system DBs in results set
DECLARE DBBackup_Cursor CURSOR FOR

-- Get DBNames for CURSOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY CATALOG_NAME

-- Start CURSOR
OPEN DBBackup_Cursor

FETCH NEXT FROM DBBackup_Cursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM DBBackup_Cursor INTO @DBName


-- Incremntal Backup Process
DECLARE @date VARCHAR (14),
@DBName_Path VARCHAR (255),
@File VARCHAR (260),
@cmd VARCHAR (255),
@database VARCHAR(100),
@FileDelete VARCHAR(100)

set @date = CONVERT (VARCHAR(12) , GETDATE(), 112) + SUBSTRING (CONVERT (VARCHAR(12) , GETDATE(), 114),1,2) + SUBSTRING (CONVERT (VARCHAR(12) , GETDATE(), 114),4,2) + SUBSTRING(CONVERT (VARCHAR(12) , GETDATE(), 114),7,2)
set @DBName_Path = 'F:\BACKUP\DATA\' + @DBName + '\' + @DBName + '_Incremental_' + @date + '.BAK'
set @database = @DBName

-- This will display path and filename during a test
-- select @DBName_Path, @database

-- For testing path, comment out the below line prior to executing script
BACKUP LOG @database TO DISK = @DBName_Path

-- To test comment out the below line prior to executing script
SET @FileDelete = 'xp_cmdshell forfiles /p F:\BACKUP\DATA\' + @DBName + '\ /m *.bak /c "cmd /c del @file " /d -8'
EXECUTE (@FileDelete)

FETCH NEXT FROM DBBackup_Cursor INTO @DBName

-- Uncomment to test xp_cmdshell command is formated fcorrectly
-- SET @FileDelete = 'xp_cmdshell forfiles /p F:\BACKUP\DATA\' + @DBName + '\ /m *.bak /c "cmd /c del @file " /d -8'
-- SELECT @FileDelete

-- CURSOR END
END

CLOSE DBBackup_Cursor
DEALLOCATE DBBackup_Cursor




Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC

'...if they take my stapler then I'll set the building on fire...'
Fred Stemp
Fred Stemp
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 219
Persistence pays off! lol I belive I have figured it out. Seems to work in my test environment. Darn Quote Marks get me every time lol

So for those curious, I had to alter the string in @FileDelete as such:


SET @FileDelete = 'xp_cmdshell' + '''' + ('forfiles /p F:\BACKUP\DATA\') + @DBName + '\ /m *.bak /c "cmd /c del @file " /d -8'''



the entire script looks like this:


USE master

DECLARE @DBName VARCHAR(100)

-- CURSOR to lookup DBNames in master DB excluding system DBs in results set
DECLARE DBBackup_Cursor CURSOR LOCAL STATIC FOR

-- Get DBNames for CURSOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN ('master', 'tempdb', 'model', 'msdb', 'WSS_AdminContent', 'WSS_Search_SIUALPH-SPT01', 'WSS_Search_SIUALPH-SPT1')
ORDER BY CATALOG_NAME

-- Start CURSOR
OPEN DBBackup_Cursor

FETCH NEXT FROM DBBackup_Cursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM DBBackup_Cursor INTO @DBName


-- Incremntal Backup Process
DECLARE @date VARCHAR (14),
@DBName_Path VARCHAR (255),
@File VARCHAR (260),
@cmd VARCHAR (255),
@database VARCHAR(100),
@FileDelete VARCHAR(100)

set @date = CONVERT (VARCHAR(12) , GETDATE(), 112) + SUBSTRING (CONVERT (VARCHAR(12) , GETDATE(), 114),1,2) + SUBSTRING (CONVERT (VARCHAR(12) , GETDATE(), 114),4,2) + SUBSTRING(CONVERT (VARCHAR(12) , GETDATE(), 114),7,2)
set @DBName_Path = 'F:\BACKUP\DATA\' + @DBName + '\' + @DBName + '_Incremental_' + @date + '.BAK'
set @database = @DBName

-- This will display path and filename during a test
-- select @DBName_Path, @database

-- For testing path, comment out the below line prior to executing script
BACKUP LOG @database TO DISK = @DBName_Path

-- To test comment out the below line prior to executing script
SET @FileDelete = 'xp_cmdshell' + '''' + ('forfiles /p F:\BACKUP\DATA\') + @DBName + '\ /m *.bak /c "cmd /c del @file " /d -8'''
EXECUTE (@FileDelete)

FETCH NEXT FROM DBBackup_Cursor INTO @DBName

-- Uncomment to test xp_cmdshell command is formated fcorrectly
-- SET @FileDelete = SET @FileDelete = 'xp_cmdshell' + '''' + ('forfiles /p F:\BACKUP\DATA\') + @DBName + '\ /m *.bak /c "cmd /c del @file " /d -8'''
-- SELECT @FileDelete

-- CURSOR END
END

CLOSE DBBackup_Cursor
DEALLOCATE DBBackup_Cursor



Thanks to all who took a look at this one!

Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC

'...if they take my stapler then I'll set the building on fire...'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search