November 16, 2009 at 12:34 am
Moreover, Assuming that the backup folder is not shared.
On the same server, nothing is in share , the job is running and unable to trace which other process is accessing the old backup file.
Assuming , there is single machine , on which backup is being done to hard disk, backup is verfied, delete the old backup and before deletion some process or someone else as you have said in a shared folder the backups are available and trying to take a backup on to a tape. How to do tracing using command line so that i can save that information to a text file.
November 16, 2009 at 7:46 am
Mahesh,
When you are backing up multiple databases and deleting them (after retention), it fails even if a single database in the whole list fails and you get a mail saying its failed.
In this case;
1.First check the directory where the actual bak/trn files are saved and see which of them are deleted and which of them are not deleted.
2.Right click on that particluar job and check for the history, it gives you some more info.Drill down on seeing a + sign and see thru all the steps and try to identify.
Its weird to hear that deletion of files (same job) after retention is working fine a day and failing the other day.
Maintenance plan for deletion only looks for the extension of the file .bak and .trn, unless someone rename extension or the file is in use, it should not have any problem.
November 16, 2009 at 2:55 pm
Ok, so If this folder is not being shared out I do not know of a native way to monitor the data files to see where the problem is occuring...
However, you could place the below code in your SSIS package before your SQL scripts run.
I am hoping you are using a SSIS package? If so, you can add this code before your script and remove your deletion code in SQL. This will delete ALL BACKUPS in that directory however. But at least you will be able to tell if it did its job or not and if the backup procedure was the problem.
I didn't feel like using variables, but you could and then it would log the exact filename that caused the error. Hopefully you get the point. Also, the E: drive is a logical drive, not an attached storage device, correct?
'**********************************************************************************************************************
'* This script deletes any files located under E:\SQLDUMP\EDFUSION_CDI\ with a .BAK extension.
'* If the destination folder does not exist it will not work.
'* This script also writes error and information logs to the Windows Event Viewer. Look for WSH in event viewer.
'* ------------------------------------- *
'* Written by: http://www.kendallworks.net *
'* December / 2009 *
'**********************************************************************************************************************
On Error Resume Next
Const OverwriteExisting = True
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set WshShell = WScript.CreateObject("WScript.Shell")
If ObjFSO.FileExists("E:\SQLDUMP\EDFUSION_CDI\*.BAK") Then
ObjFSO.DeleteFile "E:\SQLDUMP\EDFUSION_CDI\*.BAK"
WshShell.LogEvent 0, "E:\SQLDUMP\EDFUSION_CDI\ backup files Deleted Successfully!"
else
WshShell.LogEvent 2, "C:\E:\SQLDUMP\EDFUSION_CDI\*.BAK Does Not Exist!"
End If
November 19, 2009 at 10:43 pm
Hi,
Thanks for the script.
But i am working in SQL Server 2000 environment!
November 23, 2009 at 12:03 pm
Ok, same deal. The script will work in SQL2000 or SQL2005.
However, you should be posting in the SQL2000 forum, not the SQL2005 forum. The advice for that platform would be slightly different.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply