July 18, 2016 at 2:04 pm
Jeff -
I am placing it in a package for others to use, not for my own use. Some prefer to run a package than running the script. That is all.
July 18, 2016 at 4:59 pm
lsalih (7/18/2016)
Jeff -I am placing it in a package for others to use, not for my own use. Some prefer to run a package than running the script. That is all.
Thanks and understood. Are you packaging a script or a call to a stored procedure? If you're going to use SSIS, I'd recommend the stored procedure method so that you don't need to redeploy a package when simple tweaks are needed.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2016 at 5:55 am
Jeff Moden (7/18/2016)
lsalih (7/18/2016)
Jeff -I am placing it in a package for others to use, not for my own use. Some prefer to run a package than running the script. That is all.
Thanks and understood. Are you packaging a script or a call to a stored procedure? If you're going to use SSIS, I'd recommend the stored procedure method so that you don't need to redeploy a package when simple tweaks are needed.
+1. Also because you'll find in the long run that keeping as much T-SQL as you can within your DB environment is easier to maintain.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 19, 2016 at 9:04 am
Thank you Jeff and Phil...I will use a stored procedure..
Now the challenge I have is that the restore in norecovery in first transaction fails! If I run the restore without being inside begin/end transaction it works just fine. However I am unable to run two separate transactions (1 restore with norecovery and then restore with recovery). I am getting restore terminated abnormally:
SET XACT_ABORT ON
DECLARE @Full_BKUP_PATH varchar(400),
@DIFF_BKUP_PATH nvarchar(100),
@DB_DEST nvarchar(75)
SET @Full_BKUP_PATH ='D:\TEST.bak'
SET @DIFF_BKUP_PATH ='D:\TEST_DIFF.bak'
SET @DB_DEST = 'dw_target'
BEGIN TRANSACTION
BEGIN TRY
RESTORE DATABASE @DB_DEST FROM DISK = @Full_BKUP_PATH WITH REPLACE, NORECOVERY;
END TRY
BEGIN CATCH
PRINT error_message();
ROLLBACK TRANSACTION;
END CATCH;
GO
BEGIN TRY
-- Rollback transaction if error occurred
IF (XACT_STATE()) = -1
BEGIN
print 'Restore terminated...'
END;
IF (XACT_STATE()) = 0
BEGIN
print 'The transaction was rolled back.'
END;
RESTORE DATABASE @DB_DEST FROM DISK = @DIFF_BKUP_PATH WITH REPLACE, RECOVERY;
END TRY
BEGIN CATCH
PRINT error_message();
IF (XACT_STATE()) <> 0
BEGIN
PRINT 'Rolling Back Transaction...';
ROLLBACK TRANSACTION;
END;
END CATCH;
GO
IF XACT_STATE() = 1
BEGIN
COMMIT TRANSACTION;
PRINT 'Transaction committed.';
END;
July 19, 2016 at 9:17 am
The one thing I learned is that I can't place restore inside begin/end transaction.
How can I set one batch program to start with doing restore in no recovery first then do restore with recovery?
July 19, 2016 at 9:18 am
You can't carry out restores or backups within transactions.
July 19, 2016 at 9:21 am
Yes, I just learned that, http://stackoverflow.com/questions/21087055/how-to-use-sql-server-transaction-inside-t-sql-try-catch-block
The question I have how to do restore in no recovery and then in recovery all in one single script?
July 19, 2016 at 9:22 am
lsalih (7/19/2016)
The one thing I learned is that I can't place restore inside begin/end transaction.How can I set one batch program to start with doing restore in no recovery first then do restore with recovery?
Just call the commands one after the other.
DECLARE @Full_BKUP_PATH varchar(400),
@DIFF_BKUP_PATH nvarchar(100),
@DB_DEST nvarchar(75)
SET @Full_BKUP_PATH ='D:\TEST.bak'
SET @DIFF_BKUP_PATH ='D:\TEST_DIFF.bak'
SET @DB_DEST = 'dw_target'
RESTORE DATABASE @DB_DEST FROM DISK = @Full_BKUP_PATH WITH REPLACE, NORECOVERY;
RESTORE DATABASE @DB_DEST FROM DISK = @DIFF_BKUP_PATH WITH REPLACE, RECOVERY;
July 19, 2016 at 9:24 am
Have a look at this page. In particular:
RESTORE is not allowed in an explicit or implicit transaction.
By the way, I don't think you need to switch back to multi-user. Once it's in single user, everybody is out and you have exclusive access to do the restore. I've never had to switch back myself - maybe I've just been lucky!
John
July 19, 2016 at 9:41 am
The restore does not work using your script, I got:
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 12
RESTORE DATABASE is terminating abnormally.
I changed it back to the following, but still did not work .. It is complaining about the scalar variable must be declared in the second restore with recovery line!
--SET XACT_ABORT ON
DECLARE @Full_BKUP_PATH varchar(400),
@DIFF_BKUP_PATH nvarchar(100),
@DB_DEST nvarchar(75)
SET @Full_BKUP_PATH ='D:\TEST.bak'
SET @DIFF_BKUP_PATH ='D:\TEST_DIFF.bak'
SET @DB_DEST = 'TEST_DB'
BEGIN TRY
RESTORE DATABASE @DB_DEST FROM DISK = @Full_BKUP_PATH WITH REPLACE,NORECOVERY;
END TRY
BEGIN CATCH
PRINT error_message();
END CATCH;
GO
BEGIN TRY
RESTORE DATABASE @DB_DEST FROM DISK = @DIFF_BKUP_PATH WITH REPLACE,RECOVERY;
END TRY
BEGIN CATCH
PRINT error_message();
END CATCH;
GO
--IF XACT_STATE() = 1
--BEGIN
--COMMIT TRANSACTION
--end;
July 19, 2016 at 9:47 am
Yes, because [font="Courier New"]GO[/font] starts a new batch, so your variable declarations are forgotten.
It looks as if either your differential was taken before your full, or there was another full after the one you're restoring from and before the differential.
John
July 19, 2016 at 9:51 am
If you get that error then I am assuming that the diff backup you are trying to restore isn't matched to the full backup you have restored. You can only restore a diff backup to the last full backup prior to when the diff backup was taken.
The reason you are having issues with the variables is you have a go between your statements. This is turning your script into two batches.
July 19, 2016 at 10:39 am
John -
You are correct, I fixed the issue and now everything is working. Thank you so much.
July 19, 2016 at 10:40 am
Correct. I made the change and it is working now...
Thank you all for all your input, I now have a working script!
July 19, 2016 at 12:41 pm
John Mitchell-245523 (7/19/2016)
Have a look at this page. In particular:RESTORE is not allowed in an explicit or implicit transaction.
By the way, I don't think you need to switch back to multi-user. Once it's in single user, everybody is out and you have exclusive access to do the restore. I've never had to switch back myself - maybe I've just been lucky!
John
The reason to switch it back is in case you lose the connection and there's been a failure. There's hell to pay when that happens. To wit, you have, indeed, been lucky. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply