-- Stored Procedure: dbo.CopyBackupConfigToFB -- Bill Wunder use admin GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CopyBackupConfigToFB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[CopyBackupConfigToFB] GO CREATE PROCEDURE [dbo].[CopyBackupConfigToFB] AS /******************************************************************************************************* * admin.dbo.CopyBackupConfigToFB * Creator: bw * Date: 11-03-2003 * * Description: Copy the backupConfig rows that are about a production instance to it's fallback server * * Usage: EXECUTE admin.dbo.CopyBackupConfigToFB * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- --------------------------------------------- -- create temp tables --------------------------------------------- --------------------------------------------- -- set session variables --------------------------------------------- SET NOCOUNT ON --------------------------------------------- -- body of stored procedure --------------------------------------------- declare @SourceServer varchar(30) , @SourceLinkedServer varchar(30) , @hr int , @SQLStr nvarchar(4000) if charindex('FB_',@@serverName) = 1 begin set @SourceServer = substring(@@servername, 4, datalength(@@servername)) set @SourceLinkedServer = 'lnk' + @SourceServer exec @hr = sp_helpserver @SourceLinkedServer if @hr = 1 exec admin.dbo.AddLinkedServer @SourceLinkedServer, @SourceServer exec @hr = sp_helpserver @SourceLinkedServer if @hr = 0 begin set @SQLStr = 'update fb ' + 'set IncludeRestore = prod.IncludeRestore ' + ', RestoreServerName = prod.RestoreServerName ' + ', BeforeBackupPackageName = prod.BeforeBackupPackageName ' + ', BeforeBackupPackageUNCFile = prod.BeforeBackupPackageUNCFile ' + ', BeforeBackupPackageGlobalVariables = prod.BeforeBackupPackageGlobalVariables ' + ', ArchivePackageName = prod.ArchivePackageName ' + ', ArchivePackageUNCFile = prod.ArchivePackageUNCFile ' + ', ArchivePackageGlobalVariables = prod.ArchivePackageGlobalVariables ' + ', BackupPackageName = prod.BackupPackageName ' + ', BackupPackageUNCFile = prod.BackupPackageUNCFile ' + ', BackupPackageGlobalVariables = prod.BackupPackageGlobalVariables ' + ', AfterBackupPackageName = prod.AfterBackupPackageName ' + ', AfterBackupPackageUNCFile = prod.AfterBackupPackageUNCFile ' + ', AfterBackupPackageGlobalVariables = prod.AfterBackupPackageGlobalVariables ' + ', BeforeRestorePackageName = prod.BeforeRestorePackageName ' + ', BeforeRestorePackageUNCFile = prod.BeforeRestorePackageUNCFile ' + ', BeforeRestorePackageGlobalVariables = prod.BeforeRestorePackageGlobalVariables ' + ', RestorePackageName = prod.RestorePackageName ' + ', RestorePackageUNCFile = prod.RestorePackageUNCFile ' + ', RestorePackageGlobalVariables = prod.RestorePackageGlobalVariables ' + ', AfterRestorePackageName = prod.AfterRestorePackageName ' + ', AfterRestorePackageUNCFile = prod.AfterRestorePackageUNCFile ' + ', AfterRestorePackageGlobalVariables = prod.AfterRestorePackageGlobalVariables ' + ', RecCreatedDt = prod.RecCreatedDt ' + ', RecCreatedUser = prod.RecCreatedUser ' + ', RecUpdatedDt = prod.RecUpdatedDt ' + ', RecUpdatedUser = prod.RecUpdatedUser ' + 'from ' + @SourceLinkedServer + '.admin.dbo.BackupConfig prod ' + 'join admin.dbo.BackupConfig fb ' + 'on prod.DatabaseName = fb.DatabaseName ' + 'and prod.BackupServerName = fb.BackupServerName ' + 'and prod.BackupScheduleSet = fb.BackupScheduleSet ' + 'where prod.BackupServerName = ''' + @SourceServer + ''' ' + 'insert admin.dbo.BackupConfig ' + 'select * from ' + @SourceLinkedServer + '.admin.dbo.BackupConfig prod ' + 'where BackupServerName = ''' + @SourceServer + ''' ' + 'and not exists (select 1 from admin.dbo.BackupConfig ' + 'where DatabaseName = prod.DatabaseName ' + 'and BackupServerName = prod.BackupServerName ' + 'and BackupScheduleSet = prod.BackupScheduleSet ' + 'and BackupServerName = ''' + @SourceServer + ''') ' + 'delete admin.dbo.BackupConfig ' + 'where BackupServerName = ''' + @SourceServer + ''' ' + 'and DatabaseName not in (select name from ' + @SourceLinkedServer + '.master.dbo.sysdatabases)' exec sp_executesql @SQLStr end else raiserror ('(CopyBackupConfigToFB) Unable to find or create he necessary linked server to production server %s',16,1,@SourceServer) end else raiserror ('(CopyBackupConfigToFB) Copy aborted, %s is not a WSOD Fallback Server - must begin with "FB_"',16,1,@SourceServer) RETURN GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO