SQL Server 2000 Backups

  • 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...'

  • 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...'

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply