SQL Server Backup job failing with error 0x00000001 Exception has been thrown by the target of an invocation.

I am came across a strange issue. My backup Jobs was continously failing with error "0x00000001 Exception has been thrown by the target of an invocation.". But when I went to backup file path location and checked, the backups were happening but the Job was failing. There was no further error stack trace or no clue in my error logs or in windows event logs. I was totally blank so as to what was happening.

My Backup Job does initially a cleanup task which is done by Script Task in SSIS so as to delete the files which are older than 3 days. When I checked in the backup folder to my surprise there were files which were older than a week. This thing rang a bell to my mind.

Then I simulated the Issue on my Local Desktop. The issue was strange. Actually the databases on this server was sharepoint databases which a uniquie GUID added to its name which makes the database Name way too. This is allowed by SQL Server, and also the SSIS does write a File to location with a Longer name.

But, there is a Windows File system Limitation which is just 255 characters for a Folder Path Length. Now my databases File Path Length was way exceeding 256 characters. This was the issue the Exception was being thrown.

There are two ways to resolve this.

1. Use a custom name and path in your SSIS backup Package and reduce the length of Path Including Backup file name to less than 255 characters.

2. In the Windows API (with some exceptions discussed in the following paragraphs), the maximum length for a path is MAX_PATH, which is defined as 260 characters. To specify an extended-length path, use the "\\?\" prefix. For example, \\?\D:\very long path this needs to be done where you are calling your Delete File function as Pass the \\?\ prefix while sending the path to function. More information on this can be found on microsoft Link :http://msdn.microsoft.com/en-us/library/windows/desktop/aa365247(v=vs.85).aspx

Hope this is a useful information