SQLServerRegistrationBackup.bat

,

Backs up the SQL Server EM registration from the registry. Assumes that EM is configured to save common, not by user/independently.

I recommend changing EM to Store locally for all users, (not independently) and create a weekly scheduled job to backup the HKLM..\..\Registered Servers X branch.

Configure EM to store settings for the local machine, all users:
A. Choose the menu items; Tools, Options
B. Go to the General tab
C. Un-Check the Read/Store user independent box.
D. Click OK


Create a weekly job to execute the following batch file, (with sql server agent or windows scheduler), SQLServerRegistrationBackup.bat. The batch file backs up the local machine registry branch, HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X.

[batch to execute] [action] [backvolume] [backdirectory]
SQLServerRegistrationBackup.bat backup c temp

@echo off
rem ********************************************************************
rem *                                                                  *
rem * This batch file backs up the SQL Server registration;  REGISTRY  *
rem *                                                                  *
rem * Created by Clifton Collins                                       *
rem * Date Created:  06/12/2005                                        *
rem *                                                                  *
rem * Command line parameters                                          *
rem *				action                                 *
rem *                           backup_Volume                          *
rem *                           backup_path                            *
rem *------------------------------------------------------------------*
rem * To see parameters run the batch file passing no parameters.      *
rem *------------------------------------------------------------------*
rem *------------------------------------------------------------------*
rem *                                                                  *
rem * Revisions                                                        *
rem *                                                                  *
rem *                                                                  *
rem ********************************************************************

:INITIALIZE
  SET ROUTINEREG=SQLServerRegistrationBackup
  SET ACTION=""
  SET SQL_REGISTRY="HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X"
  SET SQLBACKFILENAME=_SQLServerRegistration_Backup
  SET WKDIR=%CD%
  SET ERRORCODE=0
  SET ERRORCOUNT=0
  SET MKDIRERRORCODE=0
  SET DELDIRERRORCODE=0
  SET REGISTRYERRORCODE=0
  SET BACKUPDIR=%2:\%3
  SET OUTFILE="%WKDIR%\%ROUTINEREG%.log"
  SET ERRFILEREG="%WKDIR%\%ROUTINEREG%.Err"
  SET RPTFILEREG="%WKDIR%\%ROUTINEREG%.txt"
  SET OLDFILEREG="%WKDIR%\%ROUTINEREG%Old.txt"
  if exist %ERRFILEREG% DEL %ERRFILEREG%
  if exist %OLDFILEREG% DEL %OLDFILEREG%
  if exist %RPTFILEREG% TYPE %RPTFILEREG% >> %OLDFILEREG%
  if exist %RPTFILEREG% DEL %RPTFILEREG%
  echo Starting %ROUTINEREG% on %DATE% at %TIME%...  >>%RPTFILEREG% 2>&1

  echo %WKDIR% >c:\temp\wkdir.txt
  ECHO %ROUTINEREG% >>C:\TEMP\WKDIR.TXT
  echo %OUTFILE% >>C:\TEMP\WKDIR.TXT
  echo %RPTFILEREG% >>C:\TEMP\WKDIR.TXT

:CHECKPARAMS
  IF "%1" == "" GOTO SYNTAX
  IF "%2" == "" GOTO SYNTAX
  IF "%3" == "" GOTO SYNTAX
  SET ACTION=%1
  IF "%4" == "debug" GOTO DEBUG
  SET DEBUG=false
  GOTO DIRINITIALIZE

:DEBUG
  SET DEBUG=true

:DIRINITIALIZE
  echo           DIRectory initialize...  >>%RPTFILEREG% 2>&1
  if NOT exist "%BACKUPDIR%" MKDIR "%BACKUPDIR%" >>%RPTFILEREG% 2>&1
  if NOT %ERRORLEVEL% == 0 SET ERRORCODE=1 & SET /a ERRORCOUNT=%ERRORCOUNT%+1 & SET MKDIRERRORCODE=1
  if %ERRORCODE% == 0 echo                %BACKUPDIR% directory success  >>%RPTFILEREG% 2>&1
  if not %ERRORCODE% == 0 echo                ERROR, %BACKUPDIR% directory does not exist or was not created!  >>%RPTFILEREG% 2>&1
  if NOT exist "%BACKUPDIR%" GOTO MKBACKUPDIRERROR
  SET ERRORCODE=0

  %2:
  CD  CD %3

:REGISTRYBACKUP
  if %ACTION% == backup (
  echo           REGISTRY %ACTION%  >>%RPTFILEREG% 2>&1
  echo               Regedit /e "%BACKUPDIR%\%COMPUTERNAME%%SQLBACKFILENAME%.reg" %SQL_REGISTRY%    >>%RPTFILEREG% 2>&1
  Call Regedit /e "%BACKUPDIR%\%COMPUTERNAME%%SQLBACKFILENAME%.reg" %SQL_REGISTRY%    >>%RPTFILEREG% 2>&1
  if NOT %ERRORLEVEL% == 0 SET ERRORCODE=1 & SET /a ERRORCOUNT=%ERRORCOUNT%+1
  if %ERRORCODE% == 0 echo                Registry %ACTION% success  >>%RPTFILEREG% 2>&1
  if not %ERRORCODE% == 0 echo                ERROR, Registry %ACTION% failure!  >>%RPTFILEREG% 2>&1
 )



GOTO END

:MKBACKUPDIRERROR
  echo !!! Backup directory, %BACKUPDIR%,  does not exist, no %ACTION% performed !!!  >>%RPTFILEREG% 2>&1
  SET /a ERRORCOUNT=%ERRORCOUNT%+1
  SET ERRORCODE=2
  GOTO END

:REGISTRYBACKUPERROR
  echo !!! ERROR during %ACTION% !!!  >>%RPTFILEREG% 2>&1
  SET /a ERRORCOUNT=%ERRORCOUNT%+1
  SET ERRORCODE=4
  GOTO END


:SYNTAX
  echo.
  echo syntax = %ROUTINEREG% [action] [backup volume] [backup path]   >>%RPTFILEREG% 2>&1
  echo.
  SET ERRORCODE=1
  SET /a ERRORCOUNT=%ERRORCOUNT%+1
  GOTO END

:END
  if %ERRORCOUNT% == 0 echo Completed %ROUTINEREG% %ACTION% >>%RPTFILEREG% 2>&1
  if not %ERRORCOUNT% == 0 echo ERROR during %ROUTINEREG% %ACTION%!  ErrorCount is %ERRORCOUNT%  >>%RPTFILEREG% 2>&1
  echo Ending %ROUTINEREG% on %DATE% at %TIME%.  >>%RPTFILEREG% 2>&1
:EXIT
  echo %ERRORCODE% >%ERRFILEREG%
  SET REGISTRYERRORCODE=%ERRORCODE%
  REM *  Remove the remark below to receive and process the exit code from the batch.
  REM Exit %ERRORCODE%

Rate

5 (1)

Share

Share

Rate

5 (1)