How to rollback everything(t-sql) if restoring of DB is failed?

See more:

SQL

SQL-Server

SQL-Server-2008

, +

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