如果数据库还原失败，如何回滚所有内容(t-sql)? [英] How to rollback everything(t-sql) if restoring of DB is failed?

问题描述

我将新记录插入到"Project"表中，然后还原数据库并向该数据库中插入一些行.
我需要以下内容:

1.如果将记录插入到"Project"表中，但是还原数据库失败，那么我想回滚所有内容
2.如果插入记录失败，我也想回滚所有内容

为此，我正在使用事务，但出现以下错误"RESTORE DATABASE异常终止".

以下是我用于此目的的脚本.

有什么问题以及如何解决?


-------------------------------------------------- -------------------------
CreateProject
-------------------------------------------------- -------------------------

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
-------------------------------------------------- -------------------------

---------------------------------------------------------------------------
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

推荐答案

在CreateProject中进行更改将解决该错误.

在调用
之前，使用Commit transaction进行交易 EXEC CreateDB @dbName

并在调用SP后再次使用Begin事务

"RESTORE DATABASE异常终止."错误得到解决.

Change in CreateProject will resolve the error.

use Commit transaction the transaction before you call the
EXEC CreateDB @dbName

and again use begin transaction after the SP is called

"RESTORE DATABASE is terminating abnormally." error gets resolved.

这篇关于如果数据库还原失败，如何回滚所有内容(t-sql)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！