Sql-server – Loop for restoring databases

backuprestoresql servert-sql

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

Best Answer

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