Archive
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
- 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
- Pick existing Database
- Click ‘OK’ to complete the Restore process.
🙂
Transaction count after EXECUTE indicates a mismatching number – SQL Error
I was getting the below exception when I execute a stored procedure from my C# console application.
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
🙂
Rajeev Pentyala - Microsoft Power Platform 