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

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(by CreateDB stored procedure below) 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 @ErrorMess开发者_如何转开发age 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

You can't "roll back" a database restore, its not an ATOMic operation.

You'll have to trap the error and undo-it manually.

would you want to rollback only current transation?

begin tran

  -- perform.......

  if @@error>0

     rollback tran

commit tran