SQL Server BACKUP folder in windows registry

SQL Server BACKUP folder in windows registry
*This script reads and copies  the backup folder as defined in the windows registry to an Alternative directory

/******************************************************************
*
* SQL Server BACKUP 
*This script read the backup folder as defined in the windows registry

******************************************************************/

CREATE  PROCEDURE BACKUP_DB
AS 
DECLARE @BACKUP_DIR VARCHAR(200) 
DECLARE @ALTERNATIVE_DIR VARCHAR(200)
DECLARE @CMD VARCHAR(1000)
SET @ALTERNATIVE_DIR = '\\SERVER2\SERVER1\BACKUP\'
EXECUTE master..xp_regread   
     'HKEY_LOCAL_MACHINE',
     'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 
     ' BackupDirectory ', 
      @BACKUP_DIR OUTPUT 
      SET @CMD = 'COPY "' + @BACKUP_DIR + '\*MASTER*" ' +  @ALTERNATIVE_DIR 
      EXEC master..xp_cmdshell @CMD

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 Related Posts

Copy only backup in SQL server 2005 and not break the backup chain

Slow SQL Server Backup and Restore with DBCC TRACEON (3004, 3605, -1)