August 24, 2010 at 9:11 am
Little Background about my job before I pose a question
I have a Sql sever agent job that has been created and scheduled on weekly basis to create a database backup files on a remote server but before creating the backup files it should remove (old) backup files prior to 2 days from current date.
I wrote a Stored procedure to perform the above mentioned task.
-------------------------------------------------------------------------------
Create PROCEDURE [dbo].[P_DeleteDBBackupFiles] @in_path varchar(255) , @days_prior int
AS
BEGIN
DECLARE @retstat Int,
@now datetime,
@server_name varchar(50),
@bcp_command varchar(255),
@format_path_dir varchar(255),
@dir_string varchar(255),
@errmsg varchar(100),
@current_date varchar(40),
@time_stamp Varchar(14)
SET NOCOUNT ON
SELECT @retstat = 0
SELECT @now = GETDATE()
SELECT @server_name = @@servername
Print 'Begin P_DeleteDBBackupFiles'
SELECT @current_date = CONVERT(varchar(40), GETDATE() - @days_prior, 121)
SELECT @time_stamp = SUBSTRING(@current_date, 1, 4) + SUBSTRING(@current_date, 6, 2) +
SUBSTRING(@current_date, 9, 2) + SUBSTRING(@current_date, 12, 2) +
SUBSTRING(@current_date, 15, 2) --+ SUBSTRING(@current_date, 18, 2)
print @time_stamp
SELECT @bcp_command = 'dir /B "' + @in_path + '\*.bak" > "' + @in_path + '\directory_listing.txt"'
Print @bcp_command
EXEC @retstat = master..xp_cmdshell @bcp_command, no_output
CREATE TABLE #DeleteDBBackupFiles_dirlist_temp(Directory_Listing varchar(255))
IF @@ERROR <> 0
GOTO Exit_Procedure
SELECT @bcp_command = 'bcp #DeleteDBBackupFiles_dirlist_temp in "' +
@in_path + '\directory_listing.txt" -c -T -S ' + @server_name
insert into #DeleteDBBackupFiles_dirlist_temp values ('Customer_profile201008222210.bak')
select * from #DeleteDBBackupFiles_dirlist_temp
print @bcp_command
EXEC @retstat = master..xp_cmdshell @bcp_command, no_output
IF @retstat <> 0
begin
print 'in error'
GOTO Exit_Error
end
SELECT Directory_Listing FROM #DeleteDBBackupFiles_dirlist_temp
DECLARE dir_cursor CURSOR
STATIC
FOR
SELECT Directory_Listing FROM #DeleteDBBackupFiles_dirlist_temp WHERE REPLACE(RIGHT(directory_listing,16),'.bak','') < @time_stamp
OPEN dir_cursor
DROP TABLE #DeleteDBBackupFiles_dirlist_temp
FETCH dir_cursor INTO @dir_string
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'Deleting ' + @in_path + '\' + @dir_string
SELECT @bcp_command = 'DEL "' + @in_path + '\' + @dir_string + '"'
--PRINT @bcp_command
EXEC @retstat = master..xp_cmdshell @bcp_command, no_output
IF @retstat <> 0
BEGIN
SELECT @errmsg = 'Unable to delete ' + @in_path + '\' + @dir_string
RAISERROR(@errmsg, 17, 2)
END
--END
--END
FETCH dir_cursor INTO @dir_string
END
CLOSE dir_cursor
DEALLOCATE dir_cursor
IF @retstat = 0
GOTO Exit_Procedure
Exit_Error:
DROP TABLE #DeleteDBBackupFiles_dirlist_temp
Exit_Procedure:
END
-------------------------------------------------------------------------
Inputs to above Stored Procedure:
@in_path = 'F:\MSSQL\User\DBBackup_MP\Customer_Profile', @days_prior = 2
But here comes the problem When I execute the above stored procedure it executing fine but its not deleting the files on the remote server.
Stored procedure Steps?
Step1:- Declaring all the variables
Step2:- creating @time_stamp that choose date 2 days prior from current date
Step3:- @bcp_command that choose's the files > directory_listing.txt file in that folder
Step4:- Then EXEC @retstat
Step5:- creating a temp table to store records
Step6:- Created a cursor on Temp table to delete backup files older than 2 days from current date
Step7:- Droping the Temp table after deleting the .bak files
I'm suspecting I'm doing a mistake at SELECT @bcp_command = 'bcp #DeleteDBBackupFiles_dirlist_temp in "' +
@in_path + '\directory_listing.txt" -c -T -S ' + @server_name
I mean Temp table with bcp could a problem. I don't know?
Please help me to fix this issue. Thank you
August 24, 2010 at 9:23 am
Try .BAK rather than .bak. I had a similar weirdness based on that case. If this doesn't work, let me know as I have an activex script that I use on old 2000 boxes that should work on 2008, or a 2005 script using xp_deletefile.
August 24, 2010 at 9:58 am
No its not working either with .BAK. I don't think it would be a problem with .bak but some thing I'm doing wrong with temp table. Any how it would be great if you share your logic/script to perform this task. I'm working on 2008 box.
August 24, 2010 at 10:25 am
What happens when you copy the printed command and run it in command prompt?
print @bcp_command
August 24, 2010 at 10:33 am
It say's can't find the file
August 24, 2010 at 10:39 am
Try using UNC path instead of F:\
August 24, 2010 at 11:03 am
I run this activex script within a sql job (as the step before the backups) on a SQL 2000 box. Haven't tested on 2005 or 2008. You'll have to stick your backup directory in (including sub folder) and put your retention in - I was using minus 1.
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
iDaysOld =1
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "<insert your backup directory with drive letter included>"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
For each oFile in oFileCollection
if Right(oFile.Name,4) =".BAK" Then
If oFile.DateLastModified < (Date() - (iDaysOld-1)) Then
oFile.Delete(True)
End If
End if
Next
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
August 24, 2010 at 12:38 pm
Yaahhh huuu... I Got it.. I found where I'm doing mistake. The culprit is local temp table. Yes while we are using BCP command it create a new connection because of that it was uanble to read the Local temp table so i create global temp table instead ( Which will be avialable in all sessions).
Lesson From this mistake: Whenever you use BCP command and wanna store records temporarly always use ##temp table not #temp table and then drop table at the end of your task
Thanks for all your response
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply