I insert new record to `Project` table and after that I restore DB and inserting some row to that DB.
I want the following:
1. If record was inserted to `Project` table but restoring DB is failed, so I want to rollback everything
2. If inserting record failed I also want to rollback everything
To achieve this I'm using transaction, but I get the following error
`RESTORE DATABASE is terminating abnormally.`
Below are the scripts I use for this purpose.
What is the problem and how to fix it?
---------------------------------------------------------------------------
CreateProject
---------------------------------------------------------------------------
ALTER PROCEDURE CreateProject
@name nvarchar(50)
AS
SET XACT_ABORT, NOCOUNT ON
DECLARE @starttrancount int
BEGIN TRY
SELECT @starttrancount = @@TRANCOUNT
IF @starttrancount = 0
BEGIN TRANSACTION
DECLARE @sql nvarchar(MAX)
DECLARE @projId int
DECLARE @dbName nvarchar(128)
-- ======================================================================
-- Create new project
-- ======================================================================
INSERT INTO Project(Name)
VALUES(@name)
-- ======================================================================
-- Generate dbname that will be used to restore db with that name
-- ======================================================================
SET @projId = SCOPE_IDENTITY()
SET @dbName = 'Site' + CONVERT(nvarchar(20), @projId)
-- ======================================================================
-- Create db with @dbname
-- ======================================================================
EXEC CreateDB @dbName
--Insert some records to tables in created DB
...
...
IF @starttrancount = 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorState INT
DECLARE @ErrorSeverity INT
IF XACT_STATE() <> 0 AND @starttrancount = 0
ROLLBACK TRANSACTION
SELECT
@ErrorMessage = 'Source:CopyPage\r\t' + ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState);
END CATCH
---------------------------------------------------------------------------
CreateDB
---------------------------------------------------------------------------
ALTER PROCEDURE CreateDB
@newDbname nvarchar(128),
@restoreFrom nvarchar(MAX) = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Site.bak'
AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
DECLARE @newMdfPath nvarchar(MAX) = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' + @newDbname + '.mdf'
DECLARE @newLdfPath nvarchar(MAX) = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' + @newDbname + '_log.ldf'
RESTORE DATABASE @newDbname FROM DISK=@restoreFrom
WITH RECOVERY,
MOVE 'Site' TO @newMdfPath,
MOVE 'Site_log' TO @newLdfPath
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorState INT
DECLARE @ErrorSeverity INT
SELECT
@ErrorMessage = 'Source:PrepareNewSite\r\t' + ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState);
END CATCH