July 19, 2016 at 12:53 pm
Jeff and everyone else -
I did modified my script based on everyone input, thanks. I am starting by placing the database offline => restore => then placing it back online. I created a SP to run it..
Here is my working query:
SET XACT_ABORT ON
DECLARE @Full_BKUP_PATH varchar(400),
@DIFF_BKUP_PATH nvarchar(100),
@DB_DEST nvarchar(75),
@sql nvarchar(75)
SET @Full_BKUP_PATH ='c:\Test.bak'
SET @DIFF_BKUP_PATH ='c:\test_diff.bak'
SET @DB_DEST = 'TEST_DB'
SET @sql = 'ALTER DATABASE '+ @DB_DEST+ ' SET OFFLINE WITH ROLLBACK IMMEDIATE';
EXEC (@SQl);
BEGIN TRY
RESTORE DATABASE @DB_DEST FROM DISK = @Full_BKUP_PATH WITH REPLACE,NORECOVERY;
END TRY
BEGIN CATCH
PRINT error_message();
END CATCH;
BEGIN TRY
RESTORE DATABASE @DB_DEST FROM DISK = @DIFF_BKUP_PATH WITH REPLACE,RECOVERY;
END TRY
BEGIN CATCH
PRINT error_message();
END CATCH;
IF XACT_STATE() = 1
BEGIN
COMMIT TRANSACTION
end;
begin try
SET @sql = 'ALTER DATABASE '+ @DB_DEST+ ' SET ONLINE';
EXEC (@SQl);
END TRY
BEGIN CATCH
PRINT error_message();
END CATCH;
July 19, 2016 at 1:10 pm
Just so you know as you are using separate try catch blocks, and also because you are only printing the error, then if your restore of the full backup fails then your sp will still try to restore the diff backup.
Run the 2 queries below in separate batches. As you can see the first one doesn't raise an error and continues with the select hello. The second raises an error and doesn't continue with the select hello.
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
SELECT 'hello'
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
THROW;
END CATCH
SELECT 'hello'
July 19, 2016 at 1:15 pm
That is why I initially was thinking of using try / catch so I catch an error if first step fails. Thanks for correcting me, I will correct my mistake.
July 19, 2016 at 7:19 pm
Never mind. Post removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2016 at 7:50 pm
lsalih (7/19/2016)
That is why I initially was thinking of using try / catch so I catch an error if first step fails. Thanks for correcting me, I will correct my mistake.
There is no problem at all with using several TRY/CATCH blocks.
The problem is with what your CATCH block contains. It needs a THROW or RAISERROR, otherwise you are just outputting informational messages and proceeding, regardless of errors.
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 8:06 pm
Phil -
You are correct, initially I was trying to print the error message if there was an error. I appreciate correcting me, and I have added throw vs using print error message. It is all corrected now, and I have my code working. Thank you for everyone's input.
July 19, 2016 at 8:23 pm
lsalih (7/19/2016)
Phil -You are correct, initially I was trying to print the error message if there was an error. I appreciate correcting me, and I have added throw vs using print error message. It is all corrected now, and I have my code working. Thank you for everyone's input.
Since SQL Server will throw its own error if the restore fails, why do you need a TRY/CATCH to begin with?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2016 at 8:30 pm
Jeff -
Good question.. At first I had in mind to use transaction and use try/catch in each step so I know where it failed. Now that the process is very straightforward, I can remove these try/catch lines..
July 20, 2016 at 2:49 am
Jeff Moden (7/19/2016)
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. 😉
I'm sorry, but I don't get what you mean. What kind of failure? How would you lose the connection? What hell is there to pay? As far as I understand, you don't need an actual connection to the database to start the restore - you just need nobody to be in the database. Indeed, if you attempt a restore while connected to the database, it fails:ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE MyDB
RESTORE DATABASE MyDB FROM DISK = 'C:\Users\Me\Documents\MyDB_Full.BAK', REPLACE
Msg 3102, Level 16, State 1, Line 333
RESTORE cannot process database 'MyDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 333
RESTORE DATABASE is terminating abnormally.
The reason for changing to single-user isn't to get a connection to the database; it's to remove all other connections to the database. I've done a quick search and I can't find anyone else who recommends changing back to multi-user before doing the restore.
John
Edit - just discussed this with a colleague... do you mean that there'll be hell to pay if the restore fails and the database is left in single-user mode? I don't think that would be a problem, or rather I don't think that would be the biggest problem, since the old database is no longer needed anyway (that's why we're REPLACEing it), so who cares what state it's left in if the the restore fails? Indeed, it may be better to have it in a barely useable state than have users connecting to it thinking that everything is OK.
July 20, 2016 at 10:03 pm
John Mitchell-245523 (7/20/2016)
The reason for changing to single-user isn't to get a connection to the database; it's to remove all other connections to the database. I've done a quick search and I can't find anyone else who recommends changing back to multi-user before doing the restore.
It's probably never happened to you or the people that you couldn't find a recommendation from but I've had it happen where in that split second right after the database is set to single user to bounce everyone out and when the restore starts that a network anomaly occurred and the connection for the code running was lost. When that happens, all those nice, eager, and very aggressive Web Services try to grab the single user connection and, when one of them succeeds, you have to figure out which one succeeded and kill it... only to have another succeed, wash, rinse, repeat.
At the very least, set it so that only restricted users can get back in and then pray that no one has an app login that has one.
It costs nothing to do so and saves your hinny if something goes wrong, no matter how low the chances of something going wrong are. It'll only take once to convince you. It happened to me just once and I'm convinced. 😉 I just hope it never happened between the single user and multi-user commands.
If you don't want to flip it back to multi-user, then don't. It's just my recommendation based on a single experience.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2016 at 2:28 am
Jeff Moden (7/20/2016)
John Mitchell-245523 (7/20/2016)
The reason for changing to single-user isn't to get a connection to the database; it's to remove all other connections to the database. I've done a quick search and I can't find anyone else who recommends changing back to multi-user before doing the restore.It's probably never happened to you or the people that you couldn't find a recommendation from but I've had it happen where in that split second right after the database is set to single user to bounce everyone out and when the restore starts that a network anomaly occurred and the connection for the code running was lost. When that happens, all those nice, eager, and very aggressive Web Services try to grab the single user connection and, when one of them succeeds, you have to figure out which one succeeded and kill it... only to have another succeed, wash, rinse, repeat.
At the very least, set it so that only restricted users can get back in and then pray that no one has an app login that has one.
It costs nothing to do so and saves your hinny if something goes wrong, no matter how low the chances of something going wrong are. It'll only take once to convince you. It happened to me just once and I'm convinced. 😉 I just hope it never happened between the single user and multi-user commands.
If you don't want to flip it back to multi-user, then don't. It's just my recommendation based on a single experience.
OK, I understand the scenario now. But I don't get how changing back to multi-user is going to stop it happening? Restricted user is a good suggestion provided, as you say, that nobody who might connect is db_owner or higher. Offline would work, as well. Of course, the proper way to do it is to make sure those Web Services are stopped before you do stuff like this - they might not appreciate having the database whipped out from under their feet, even if the restore goes on to finish successfully.
John
July 22, 2016 at 6:19 pm
John Mitchell-245523 (7/21/2016)
Jeff Moden (7/20/2016)
John Mitchell-245523 (7/20/2016)
The reason for changing to single-user isn't to get a connection to the database; it's to remove all other connections to the database. I've done a quick search and I can't find anyone else who recommends changing back to multi-user before doing the restore.It's probably never happened to you or the people that you couldn't find a recommendation from but I've had it happen where in that split second right after the database is set to single user to bounce everyone out and when the restore starts that a network anomaly occurred and the connection for the code running was lost. When that happens, all those nice, eager, and very aggressive Web Services try to grab the single user connection and, when one of them succeeds, you have to figure out which one succeeded and kill it... only to have another succeed, wash, rinse, repeat.
At the very least, set it so that only restricted users can get back in and then pray that no one has an app login that has one.
It costs nothing to do so and saves your hinny if something goes wrong, no matter how low the chances of something going wrong are. It'll only take once to convince you. It happened to me just once and I'm convinced. 😉 I just hope it never happened between the single user and multi-user commands.
If you don't want to flip it back to multi-user, then don't. It's just my recommendation based on a single experience.
OK, I understand the scenario now. But I don't get how changing back to multi-user is going to stop it happening? Restricted user is a good suggestion provided, as you say, that nobody who might connect is db_owner or higher. Offline would work, as well. Of course, the proper way to do it is to make sure those Web Services are stopped before you do stuff like this - they might not appreciate having the database whipped out from under their feet, even if the restore goes on to finish successfully.
John
Understood and fully agree that all the related Web Services should be stopped. If you can do that, that's the preferred method. But stopping the Web Services isn't all there is. There are SQL Server jobs that run 24/7 and I'm not necessarily the one that knows all of the jobs that may depend on the database and, in a crunch, I'm not going to take a chance that one of the stored procedures being called is hitting the database I'm trying to restore. We also have this "system" known as "Web Methods" that cannot be stopped because it's responsible for job runs against many other databases (it's like SSIS but on steroids). I'm fortunate that I don't also have to worry about SSIS jobs that may run that might take over a single user connection but there's nothing preventing another single user on a mission from taking it over if I lose the connection. It's just easier for me to ensure that right after I bounce everyone out with a single user connection that I immediately shift back to something a bit less dangerous and immediately follow that with the restore command quickly enough so nothing else actually does get back in. If I lose that connection during a panic restore, then there will be a real panic and I'm just not willing to take the chance. Been there once and don't want to ever go through that again.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2016 at 2:34 am
Jeff Moden (7/22/2016)
It's just easier for me to ensure that right after I bounce everyone out with a single user connection that I immediately shift back to something a bit less dangerous and immediately follow that with the restore command quickly enough so nothing else actually does get back in. If I lose that connection during a panic restore, then there will be a real panic and I'm just not willing to take the chance. Been there once and don't want to ever go through that again.
This is the bit I don't understand. Why is MULTI_USER less dangerous when you're about to overwrite the database anyway? If someone else jumps in after you lose the connection, don't you just start again? When this happened to you, what were the consequences - lost data, hours of extra work, or something else?
Thanks
John
July 25, 2016 at 6:02 am
I'm not the one with the keys to the Web Services. When it happened to me and one of them grabbed the single connection, I had to get other several other people involved because as soon as you'd shut one Web Service down, another would pick up on the connection. It also meant shutting down some services that used other databases for different processes and they were as poorly setup as many of the jobs on SQL Server where you couldn't easily shut down only those parts that were affected by the restore of the one database. That meant that some functionality that shouldn't have needed to "go down" for a long term, did.
I'm also not absolutely positive that immediately switching back to multi- or restricted user would save you if the timing were as unfortunate as what I experienced but it is a command that relies on nothing else and is very fast to execute. Think of it more as hedging a bet. If nothing else, the commented line in the code is a reminder to anyone that may be stepping through the code to troubleshoot (or whatever) to be keenly aware not to lose their SSMS session after setting the database to the single user mode.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply