Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2000 Backups Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 6:27 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:13 AM
Points: 17, Visits: 145
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
Southern Insurance Underwriters, Inc.

'...if they take my stapler then I'll set the building on fire...'
Post #1505155
Posted Wednesday, October 16, 2013 7:39 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 10:13 AM
Points: 17, Visits: 145
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
Southern Insurance Underwriters, Inc.

'...if they take my stapler then I'll set the building on fire...'
Post #1505199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse