problem in creating and restoring a database within a transaction scope?
I have a function in which i have called a stored procedure to create a new db and restore an existing db in to that newly created db..It is working fine without transaction scope but inside a transaction scope it is giving error like
Cannot perform a backup or restore operation within a transaction.
BACKUP DATABASE is terminating abnormally.
Cannot perform a backup or restore operation within a transaction.
RESTORE DATABASE is terminating abnormally
sample code:
using (Transactions.TransactionScope scope = new Transactions.TransactionScope(TransactionScopeOption.Required, new TimeSpan(2, 0, 0)))
{
if (flag == "Yes")
{
SqlCommand cmddb = new SqlCommand("crt_crm_db_details", con);
cmddb.CommandType = CommandType.StoredProcedure;
cmddb.CommandTimeout = 0;
cmddb.Parameters.Add("@custname", SqlDbType.NVarChar).Value = ccode;
cmddb.Parameters.Add("@blank_dbname", SqlDbType.NVarChar).Value = _blankdbCrm;
cmddb.Parameters.Add("@blank_bakpath", SqlDbType.NVarChar).Value = bakRestorePath;
cmddb.Parameters.Add("@blank_mdfpath", SqlDb开发者_如何学CType.NVarChar).Value = mdfRestorePath;
cmddb.Parameters.Add("@blank_ldfpath", SqlDbType.NVarChar).Value = ldfRestorePath;
cmddb.Parameters.Add("@usercnt", SqlDbType.NVarChar).Value = userCountdb;
SqlParameter Typedb = new SqlParameter("@errorid", SqlDbType.NVarChar);
Typedb.Direction = ParameterDirection.Output;
Typedb.Value = null;
Typedb.Size = 50;
cmddb.Parameters.Add(Typedb);
try
{
con.Open();
cmddb.ExecuteNonQuery();
}
catch (Exception ex)
{
VWLogger.LogMessage("Exception in crt_crm_db_details:", TraceEventType.Critical);
VWLogger.LogMessage(ex, TraceEventType.Critical);
return ex.Message;
throw new CustomSoapException(CustomSoapException.ExceptionCode.AuthenticationException, ex.Message);
}
con.Close();
}
scope.Complete();
return "Success";
}
Any suggestion?
Well, the error is pretty clear that this isn't going to work... "Cannot perform a backup or restore operation within a transaction."
isn't very ambiguous; options:
- execute the tsql outside of a transaction
- if you might already have a nested transaction, use a nil-transaction
TransactionScope
(i.e.TransactionScopeOption.Suppress
)
Just u remove " begin tran", " Commit tran " in your query, then it will working fine..
精彩评论