July 18, 2016 at 11:13 am
Hi -
I have a need to stop some application services based on the database environment I am refreshing, restore full bkup in norecovery, and then do the differential in recovery mode. I am putting below script to do all steps in one single code. The two issues I am having is that 1) I am not sure how to run to two XP_CMDSHELL in a single transaction, and 2) for some reason the code is not doing restore as it should. Can you please help me here...
SCRIPt:
DECLARE @Full_BKUP_PATH varchar(400),
@DIFF_BKUP_PATH nvarchar(100),
@DB_DEST nvarchar(75),
@sql varchar(200)
SET @Full_BKUP_PATH ='D:\TEST.bak'
SET @DIFF_BKUP_PATH ='D:\TEST_DIFF.bak'
SET @DB_DEST = 'TEST'
SET @sql = ''
-- STEP 1 STOP SERVICES BASED ON THE DB ENVIRONMENT
BEGIN TRANSACTION stopservice;
BEGIN TRY
--stop services
IF @DB_DEST = 'TEST'
BEGIN
EXEC master..XP_CMDSHELL 'SC \\SRVRNAME stop Spooler' -- I am using these two service to show how I am stopping the services
EXEC master..XP_CMDSHELL 'SC \\SRVRNAME stop PlugPlay'
end
IF @DB_DEST = 'DEV'
BEGIN
EXEC master..XP_CMDSHELL 'SC \\SRVRNAME stop Spooler'
EXEC master..XP_CMDSHELL 'SC \\SRVRNAME stop PlugPlay'
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT < 0 ROLLBACK TRANSACTION stopservice;
END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION stopservice;
-- STEP 2 RESTORE DB in NORECOVERY MODE
BEGIN TRANSACTION RSTRWithNoRecovery;
BEGIN TRY
SELECT @sql = @sql + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DB_DEST) AND SPId <> @@SPId
EXEC(@SQL);
RESTORE DATABASE @DB_DEST FROM DISK = @Full_BKUP_PATH WITH REPLACE, NORECOVERY;
END TRY
BEGIN CATCH
IF @@TRANCOUNT < 0 ROLLBACK TRANSACTION RSTRWithNoRecovery;
END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION RSTRWithNoRecovery; print 'restore still in progress..'
-- STEP 3 RESTORE DB IN RECOVERY MODE
BEGIN TRANSACTION RSTRWIthRecovery;
BEGIN TRY
RESTORE DATABASE @DB_DEST FROM DISK = @DIFF_BKUP_PATH WITH REPLACE, RECOVERY;
END TRY
BEGIN CATCH
IF @@TRANCOUNT < 0 ROLLBACK TRANSACTION RSTRWIthRecovery;
END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION RSTRWIthRecovery;
July 18, 2016 at 11:41 am
... for some reason the code is not doing restore as it should.
You need to provide better detail than this.
Is it doing the restore in a way that it shouldn't? If so, please describe.
Or maybe not at all?
Any error messages?
Any other supporting information?
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 18, 2016 at 11:42 am
What services are you trying to stop? is it your application? You probably do not need to do that.
Just prior to the restore command, use this command:
USE master;
ALTER DATABASE DatabaseName SET OFFLINE
WITH ROLLBACK IMMEDIATE.
That will eliminate the need to loop through and kill connections.
That being said, the initial restore will work. Where you may get burned is if the application is constantly trying to connect. As soon as the restore completes, the app may connect again.
In that case, you can re-run the command to set the DB offline before each restore.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 18, 2016 at 11:48 am
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 the database back to multi user when my session was disconnected.
As for to the services, yes we have two web services which constantly trying to connect to the database. We are unable to start restore if these two services are not restored prior o the restoration itself.
July 18, 2016 at 11:50 am
Do you realize that xp_cmdshell is not affected by explicit transactions?
July 18, 2016 at 11:55 am
No, I do not. In such case how you stop two services within TSQL script.
July 18, 2016 at 11:57 am
Phil -
I just get stop_pending and the code does not perform the restore...
July 18, 2016 at 12:04 pm
lsalih (7/18/2016)
No, I do not. In such case how you stop two services within TSQL script.
Transactions are made to handle changes in the database within a certain connection. Stopping/Starting services is something that has nothing to do with the database. Basically, it would do the same with or without the transaction.
The stop_pending is an issue that arises when you stop the services. Check the following: http://semisignal.com/?p=3627
As the service does not stop, that might be causing the problem with your script.
July 18, 2016 at 12:07 pm
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 have the format correct? I did not see a good example out there showing how you run multiple cmdshell commands. Thank you for your help.
July 18, 2016 at 12:11 pm
lsalih (7/18/2016)
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 have the format correct? I did not see a good example out there showing how you run multiple cmdshell commands. Thank you for your help.
cmdshell commands are not affected by transactions. They're not part of the database.
July 18, 2016 at 12:13 pm
lsalih (7/18/2016)
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 the database back to multi user when my session was disconnected.
As for to the services, yes we have two web services which constantly trying to connect to the database. We are unable to start restore if these two services are not restored prior o the restoration itself.
You had a hard time with single user because the single user was the application that made the connection.
If you set it offline, you will have none of these issues. The service can stay running, the restores will work, and the database will be online after the final restore.
If you still insist that you need to stop these services, then T-SQL is probably not the best tool for the job.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 18, 2016 at 12:17 pm
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
July 18, 2016 at 12:35 pm
lsalih (7/18/2016)
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
You should also understand that you do not need to stop ANY services while trying to do a restore. Switch the database to single user (using ROLLBACK IMMEDIATE WITH NO_WAIT), then quickly to multiuser and immediately start the restore.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2016 at 12:47 pm
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 for this process, I am putting an SSIS package. I will go with placing it offline since the environment is not production.
July 18, 2016 at 1:55 pm
lsalih (7/18/2016)
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 for this process, I am putting an SSIS package. I will go with placing it offline since the environment is not production.
Having a quick trip to single user and back to multi user usually isn't a problem. When I say "quick", I mean back to back commands.
The next question is, why are you using SSIS for this? Nothing I see in your task needs that complication.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply