File Lock Not Released

nickyzzi

I'm using the SDK to compare a live Database against a BackupDatabaseSource. I'm having trouble getting the file lock on the BackupSet released. What I'm doing is

1) Creating a backup on my live database (DB2) using SQLDMO
2) Synchronizing changes in DB1 to my live database (DB2)
3) Comparing my live database to the backupset I created in step one

The second time I try to run this sequence I get this error when I try to create the backupset:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device '\\nyzzi\dbbackups\MDIPracticeMaster_dev.bak'. Operating system error 32(The process cannot access the file because it is being used by another process.). [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

After running several tests, I determined that the lock is not being released by the SDK in step 3 above. Here is the code for step 3:

public void Compare(string conStr) {
try {
comparisons.Clear();
var fullBackupFilePath = string.Format(@{0}\{1}.bak, BackupFilePath, TargetDatabaseName);
using (BackupSetDatabase backupDB = new BackupSetDatabase())
using (Database liveDB = new Database())
using (ComparisonSession session = new ComparisonSession()) {
IList<string> files = new string[] { fullBackupFilePath };
IList<string> passwords = new List<string>();

//BackupSet
IList<IBackupSet> backupSets = backupDB.GetBackupSets(files, passwords);
IBackupSet backupSetToUse = backupSets[backupSets.Count - 1];
BackupDatabaseSource backupDBSource = new BackupDatabaseSource(files, passwords, backupSetToUse);
backupDB.RegisterForDataCompare(backupDBSource.ToConnectionProperties(), Options.Default);

//Live DB
ConnectionProperties connectionProperties = new ConnectionProperties(ServerName, SourceDatabaseName, UserName, Password);
liveDB.RegisterForDataCompare(connectionProperties, Options.Default);

//Add Table Mappings
TableMappings mappings = new TableMappings();
var kbEntities = ResourceAccess.GetKBEntites(conStr);
kbEntities.ForEach(delegate(KBEntity kbEntity) {
mappings.Add((TableMapping)mappings.Join(backupDB.Tables[kbEntity.TableName], liveDB.Tables[kbEntity.TableName]));
});
session.CompareDatabases(backupDB, liveDB, mappings);

backupDB.Close();
backupDB.Dispose();
liveDB.Dispose();

//Compare tables
foreach (TableMapping mapping in mappings) {
var difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];

if (difference != null) {
foreach (Row row in difference.ResultsStore) {
var comparison = new KBComparison { EntityTableName = mapping.Obj1.FullyQualifiedName.Replace("[dbo].[", "").Replace("]", "") };
if (row.Type != Row.RowType.Same) {
int fieldIndex = 0;
foreach (FieldPair field in difference.ResultsStore.Fields) {
int field1 = field.OrdinalInResults1;
int field2 = field.OrdinalInResults2;

if (field1 != field2) {
object value1 = row.Values[field1];
object value2 = row.Values[field2];
if (value1 == null) value1 = "NULL";
if (value2 == null) value2 = "NULL";

if (row.FieldDifferent(fieldIndex)) {
comparison.FieldChanges += string.Format("{0} changed from '{1}' to '{2}', ", field.Field(false).Name, value1.ToString(), value2.ToString());
};
} else {
comparison.EntityIdFieldDescription = field.Field(false).Name;
comparison.EntityId = Convert.ToInt32(row.Values[field1]);
}
fieldIndex++;
}
comparisons.Add(comparison);
}
}
}
}
backupDB.Close();
backupDB.Dispose();
}
} catch (Exception ex) {
Console.WriteLine(ex.Message);
throw;
}
}

Chris Auckland

Thanks for your post.

I'm not sure what could be locking the file. I was able to perform another backup before I had disposed of the database object.

Can you check what actually has a handle on the backup file using process monitor from sysinternals? (Find >> Find handle or dll)?

nickyzzi

Thanks Chris,

I ended up changing how I'm doing this compare to avoid the problem. IIS is the process retaining the lock on the file (as the code was run from a web project).

hemantgaur

We might be seeing a similar issue where IIS is holding up a red-gate object which is connected to a database.

Request to nickyzzi OR Chris,
nickyzzi, can you please provide some more information on how you ended up avoiding this problem?

Chris,
If nickyzzi is no longer active in this forum, did he mention to you the solution offline? Or have you guys figured out a work around?

Would appreciate any help.
Thanks.

nickyzzi

I never found a solution to this problem. I just stopped doing the backups... sorry I can't be of more help.

hemantgaur

Thank you for replying.

robtaylor82

Hi there,

We have recently started using the SDK component and have ran into exactly the same problem. Every time we load a backup to perform a comparison, the file lock remains indefinitely - even after closing / disposing all of the objects.

Is there a work around for this - we really want to use this functionality?

Here is the code we are using:

RedGate.SQLCompare.Engine.Database targetDatabase = new RedGate.SQLCompare.Engine.Database();
BackupSetDatabase backupSetDatabase = new BackupSetDatabase();
ComparisonSession session = new ComparisonSession();

StringCollection tablesContainingDifferences = new StringCollection();

try
{
    //load backup
    IList<string> backupFiles = new[] { backupPath };
    IList<IBackupSet> backupSets = backupSetDatabase.GetBackupSets(backupFiles);
    IBackupSet backupSetToUse = backupSets[backupSets.Count - 1];

    BackupDatabaseSource backupDatabaseSource = new BackupDatabaseSource(backupFiles, new List<string>(), backupSetToUse);
    backupSetDatabase.RegisterForDataCompare(backupDatabaseSource.ToConnectionProperties());

    //load target database
    targetDatabase.ConnectionProperties = new ConnectionProperties();
    targetDatabase.ConnectionProperties.ConnectionString = database.ConnectionString;
    targetDatabase.ConnectionProperties.DatabaseName = new SqlConnectionStringBuilder(database.ConnectionString).InitialCatalog;
    targetDatabase.RegisterForDataCompare(targetDatabase.ConnectionProperties);

    TableMappings mappings = new TableMappings();

    foreach (string table in targetTables)
    {
        TableMapping tableMapping = (TableMapping) mappings.Join(backupSetDatabase.Tables[table], targetDatabase.Tables[table]);
        mappings.Add(tableMapping);
    }

    //compare databases
    session.CompareDatabases(backupSetDatabase, targetDatabase, mappings);
    session.Options.ComparisonOptions = ComparisonOptions.UseChecksumComparison;

    foreach (TableDifference difference in session.TableDifferences)
    {
        if (difference.DifferencesSummary.DifferenceCount() > 0)
        {
            tablesContainingDifferences.Add(difference.Name);
        }
    }

}
finally
{
    targetDatabase.Dispose();
    backupSetDatabase.Dispose();
    session.Dispose();
}

Cheers,
Rob.