Here is something that you can use. It is not fully automated but this is definitely very useful piece of script.
SET NOCOUNT ON;
DECLARE
@DBName NVarchar(100),
@BackupFile NVarchar(1000),
@DeviceFrom NVarchar(1000),
@DeviceTo NVarchar(1000),
@LogicalName NVarchar(1000),
@PhysicalName NVarchar(1000),
@SQL NVarchar(MAX),
@RowsToProcess integer,
@CurrentRow integer,
@Comma NVarchar(25);
SET @DBName = 'DB_Order'; -- Change this for each database
SET @BackupFile = 'E:\New folder\Thursday - DB_Order.bak'; -- Change this for each database
SELECT @DeviceFrom = SUBSTRING(physical_name, 1,
CHARINDEX(@DBName + '.mdf',
physical_name) - 1)
FROM master.sys.master_files
WHERE name = @DBName AND FILE_ID = 1;
SET @DeviceTo = 'E:\New folder\'; -- Change this if you are changing your restore location
SET @SQL = 'RESTORE DATABASE ' + @DBName + ' FROM DISK = ''' + @BackupFile + ''' WITH ';
SET @CurrentRow = 0;
SET @Comma = ',';
DECLARE @FileList TABLE (
RowID int not null primary key identity(1,1)
,LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId BIGINT
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes BIGINT
,SourceBlockSize BIGINT
,FilegroupId BIGINT
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly BIGINT
,IsPresent BIGINT
,TDEThumbprint VARBINARY(32) -- Remove this line for SQL Server 2005
);
INSERT INTO @FileList
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + '''')
SET @RowsToProcess = @@RowCount;
WHILE @CurrentRow < @RowsToProcess
BEGIN
SET @CurrentRow= @CurrentRow + 1;
BEGIN
IF @CurrentRow = @RowsToProcess
SET @Comma = ',STATS=1';
END
SELECT @LogicalName = LogicalName,@PhysicalName = PhysicalName FROM @FileList WHERE RowID=@CurrentRow;
SET @PhysicalName = Replace(@PhysicalName,@DeviceFrom,@DeviceTo);
SET @SQL = @SQL + 'MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' + @Comma + '';
END
SELECT @SQL;
--EXEC(@SQL); -- Execute when you are ready.
You can use SQLCMD / Batch file to pass in the parameters of your DB name and physical file name and try to automate it.
The error in the EXEC
part of the INSERT-EXEC
statement is leaving your transaction in a doomed state.
If you PRINT
out XACT_STATE()
in the CATCH
block it is set to -1
.
Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT
succeeds.
ALTER PROCEDURE test -- or create
AS
BEGIN try
DECLARE @retval INT;
DECLARE @t TABLE(x INT CHECK (x = 0))
INSERT INTO @t
VALUES (1)
SET @retval = 0;
SELECT @retval;
RETURN( @retval );
END try
BEGIN catch
PRINT XACT_STATE()
PRINT ERROR_MESSAGE();
SET @retval = -1;
SELECT @retval;
RETURN( @retval );
END catch;
Adding this to the CATCH
block
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;
Doesn't help. It gives the error
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC
anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.
DECLARE @RC INT;
EXEC sp_executesql
N'EXEC @RC = test',
N'@RC INT OUTPUT',
@RC = @RC OUTPUT;
INSERT INTO @t
VALUES (@RC)
Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.
DECLARE @RetVal INT = -1
IF OBJECT_ID('PrintMax', 'P') IS NULL
BEGIN
EXEC('create procedure PrintMax as begin print ''hello world'' end;')
SET @RetVal = 0
END
SELECT @RetVal;
RETURN( @RetVal );
Best Answer
You are only matching on Author, not B_Name. Change code to: