Correct. I made the change and it is working now...

Thank you all for all your input, I now have a working script!

John -

You are correct, I fixed the issue and now everything is working. Thank you so much.

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

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?

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

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

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.

I use windows scheduled task to perform backups using SQLCMD in one of my environment. If you are not seeing a scheduled task under Windows scheduler, search in msdb for...

Jeff -

I once had a hard time placing the database back to multiuser mode, that is why I am avoiding placing database into single mode or offline during DB restoration.

However...

I guess I was not thinking straight when putting this script together, I now understand how to approach the restore better. Thank you all for your great input. Lava

I am going to look further into it and see what is happening.

Meantime may I ask you how you run two CMDSHELL commands in the same transaction. Do I...

Phil -

I just get stop_pending and the code does not perform the restore...

No, I do not. In such case how you stop two services within TSQL script.

Michael -

I could place it in single mode too, but I prefer to kill sessions than placing the database in single mode because I once had a hard tome brining...

Steve and Jeff -

Thank you both for your input. The way I did the work is by creating an Amazon S3 storage in AWS and created an Amazon EC2 (same...