Advertisements

Archive

Posts Tagged ‘SQL Error’

SqlError: The backup set holds a backup of a database other than the existing database

I got below error, when I was trying restore a database from Azure Blob Storage to my local SQL server using ‘SQL Server Management Studio (SSMS)’.

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘{DB_Name}’ database. (Microsoft.SqlServer.SmoExtended)

Reason:

  • I created a new Database (DB) first and tried to restore the DB from Azure blob to this new DB.

Fix:

Below approach solved the issue

  • Don’t create a new Database before hand.
  • Right click ‘Databases’ and select ‘Restore Database’
  • Pick your ‘Source’ Database
  • In the ‘Destination’ section, provide a new Database name as highlighted below

DB_Restore1

  • Click ‘OK’ to complete the Restore process.

If you want to create a Blank Database first and restore.

  • Set below settings in ‘Options’ tab of ‘Restore Database’ window

DB_Restore2

  • Pick existing Database
  • Click ‘OK’ to complete the Restore process.

🙂

Advertisements

Transaction count after EXECUTE indicates a mismatching number – SQL Error

December 8, 2014 Leave a comment

I was getting the below exception when I execute a stored procedure from my C# console application.

SQL Transaction Error

SQL Transaction Error

My Stored procedure executes in Transaction mode and wrapped in Try/Catch block (Refer below)

CREATE PROCEDURE [Name]

BEGIN TRY

BEGIN TRANSACTION;

— My SQL Script

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0

ROLLBACK TRANSACTION;

END CATCH

Reason

  • We get this error if the transaction started and terminated suddenly without committed or rollback.
  • In my Stored Procedure I had a ‘return’ statement after ‘BEGIN TRANSACTION’ , so the stored procedure terminated neither calling COMMIT nor ROLLBACK.

Fix

  • Put “SET XACT_ABORT, NOCOUNT ON” statement which suppresses the error.
  • So the modified Stored Procedure template is as below

CREATE PROCEDURE [Name]

AS

SET XACT_ABORT, NOCOUNT ON 

DECLARE @starttrancount int

BEGIN TRY

SELECT @starttrancount = @@TRANCOUNT

IF @starttrancount = 0

BEGIN TRANSACTION

      — Your SQL Script

IF @starttrancount = 0

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0 AND @starttrancount = 0

ROLLBACK TRANSACTION

END CATCH

🙂