1

Pretty big and complex problem. There are several databases (DB1, DB2, DB3) which are needed to be copied from one zone to another (from production zone to office zone). Flow need to be: when sql job is occurred, data from one of databases (ex. DB1) is copied to database TRANSFER, after that backup of TRANSFER database is done and sent to network drive. After that restore is started and it's restoring over office zone database.
Problem is, how to secure the way, that several transfers not be started at same time and to wait in loop.

USE [msdb]
GO

/****** Object:  Job [TransferDB_Backup]    Script Date: 11/28/2016 1:07:49 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 11/28/2016 1:07:49 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TransferDB_Backup', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Create Backup]    Script Date: 11/28/2016 1:07:49 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'USE [TRANSFER]
GO
USE MASTER
GO

BACKUP DATABASE [TRANSFER] TO  DISK = N''\\location\Transfer.bak'' WITH NOFORMAT, NOINIT,  NAME = N''TRANSFER-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Delete objectz]    Script Date: 11/28/2016 1:07:49 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete objectz', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'USE TRANSFER
GO

declare @n char(1)
set @n = char(10)

declare @stmt nvarchar(max)

-- procedures
select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop procedure .['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.procedures


-- check constraints
select @stmt = isnull( @stmt + @n, '''' ) +
''alter table [TRANSFER].['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + '']    drop constraint ['' + name + '']''
from sys.check_constraints

-- functions
select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop function [TRANSFER].['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.objects
where type in ( ''FN'', ''IF'', ''TF'' )

-- views
select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop view [TRANSFER].['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.views

-- foreign keys
select @stmt = isnull( @stmt + @n, '''' ) +
    ''alter table [TRANSFER].['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + ''] drop constraint ['' + name + '']''
from sys.foreign_keys

-- tables
select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop table [TRANSFER].['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.tables

-- user defined types
select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop type [TRANSFER].['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.types
where is_user_defined = 1



--print @stmt
exec sp_executesql @stmt
', 
        @database_name=N'TRANSFER', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Cleate tliggelfile]    Script Date: 11/28/2016 1:07:49 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cleate tliggelfile', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'echo 1 > \\location\Transfer.trig', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
  • Is there a particular reason that you don't have ONE Sql Agent job with THREE job steps (DB1, DB2, DB3) and automatically run the next step after the current steps completes? – Scott Hodgin Nov 28 '16 at 18:08
  • Yes. On one side, these are jobs for every database, on second side is application where particular users can force transfer database from production zone to office zone. I know that process is ridiculous but that is how I inherited it so, need to fix it up. – Darko Milic Nov 29 '16 at 8:10
  • So, to simplify, if that is possible. I need some procedure which will monitor Transfer DB and check what is doing in Transfer DB, so, if there is some action (backup/restore/writing) then need to put next action in loop to wait other to be finished. – Darko Milic Nov 29 '16 at 8:54
1

I would highly recommend you to ditch tsql and adapt PowerShell. It specifically adds more value to what you are doing .. Make sure you use Backup compression and instant file initialization on source and destination server instances.

Copy-SqlDatabase from dbatools.io

e.g. Usage :

Copy-SqlDatabase -Source sqlserver2014a -Destination sqlcluster -Exclude Northwind, pubs, AdventureWorks -BackupRestore -NetworkShare \fileshare\sql\migration

0

After all, pretty simple solution. Just check is there some activity on Transfer database.

DECLARE @TransferDB_Active BIT = 1
WHILE @TransferDB_Active = 1
BEGIN
if object_id ('tempdb..#TransferCheck') is not null drop table #TransferCheck
SELECT
   [Database]      = ISNULL(db_name(r.database_id), N'')
  ,[Task State]    = ISNULL(t.task_state, N'')
  ,[Command]       = ISNULL(r.command, N'')
  ,[Application]   = ISNULL(s.program_name, N'')
  -- ,[Login Time]    = s.login_time,
   --,[Host Name]     = ISNULL(s.host_name, N'')
   --,[Net Address]   = ISNULL(c.client_net_address, N'')
into #TransferCheck
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(   SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)

select * from #TransferCheck
where [Database] = 'Transfer' and [Task State] = 'RUNNING'

IF @@ROWCOUNT = 0 --If @@ROWCOUNT = 0, then no activities are running
    BEGIN
        SET @TransferDB_Active = 0 --Drop out of WHILE loop

        BREAK
    END

    WAITFOR DELAY '00:00:10' --Take a break and check again
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.