I have such workflow: one target applies sql change scripts on database. If some script fails, I want to restore database in another target (Sql2008.Database Restore task).
But, if the script fails, the connection leaves and database cannot be restored
error : SmoException: System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on C:\Backups\1.0.22352.1\Database_new_20140114_1945.bak'.\r
I tried different properties from SqlServer.SqlExecute task, but nothing helped. I see the connection in SQL Activity Monitor till the full exit from script.
Call of SqlExecute looks like:
<MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="Execute" Retry="true" Files="@(AllFiles)" ConnectionString="$(OpusDatabase)" Condition="'@(AllFiles)' != '' "/>
<OnError ExecuteTargets="RestoreDataBases" Condition="'$(BackupExists)' == 'true'"/>
And trial to restore is so:
<MSBuild.ExtensionPack.Sql2008.Database TaskAction="Restore" DatabaseItem="$(OPUSDataBaseMainName)" DataFilePath="$(BackupDirectoryPath)\$(OPUSDataBaseMainName)_$(BackupTime).bak"/>
I tried to alter database before restore with SqlExecute, but it also fails because of opened connection.
How can this be fixed?