File Lock Not Released

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

File Lock Not Released

Postby nickyzzi » Tue May 19, 2009 4:58 pm

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;
}
}
nickyzzi
 
Posts: 7
Joined: Tue May 12, 2009 8:59 pm

Postby Chris Auckland » Fri May 22, 2009 2:21 pm

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)?
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby nickyzzi » Fri May 22, 2009 2:43 pm

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).
nickyzzi
 
Posts: 7
Joined: Tue May 12, 2009 8:59 pm

Postby hemantgaur » Thu Feb 18, 2010 4:25 pm

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.
hemantgaur
 
Posts: 20
Joined: Wed Dec 16, 2009 9:15 pm

Postby nickyzzi » Thu Feb 18, 2010 5:35 pm

I never found a solution to this problem. I just stopped doing the backups... sorry I can't be of more help.
nickyzzi
 
Posts: 7
Joined: Tue May 12, 2009 8:59 pm

Postby hemantgaur » Mon Feb 22, 2010 6:52 pm

Thank you for replying.
hemantgaur
 
Posts: 20
Joined: Wed Dec 16, 2009 9:15 pm

Postby robtaylor82 » Mon May 24, 2010 11:04 am

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:

Code: Select all
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.
robtaylor82
 
Posts: 1
Joined: Mon May 24, 2010 10:51 am


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests