1. ntbackup command to backup the SQL file system
2. reg command to backup the windows registry
Important: when we recover file system from file system backup, first DELETE the files that have been damaged or maybe damaged, otherwise, the existing file will NOT be recoverred from the backup, this is very important!
Example: Suppose, the SQL Master database is damaged, and the SQL server cannot be started. Here is the steps: Open the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder, for all of the files that have new time stamps after the existing backup time, move them to a temp folder, or simply delete all of the files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. This will force a clean recovery. And then,use ntbackup command line to recover the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder from the backup.
-----------
Below is the script for backup the SQL file systems:
@ECHO OFF
REM --------------------------------------------------------------------------------
REM Procedure Name: BackupSQLFileSystem.bat
REM Created By: Bob Wang
REM Creation Date: 17-Mar-2010
REM Functionality: 1. backup SQL File System
REM 2. backup hKEY_LOCAL_MACHINE registries
REM
REM Modification History:
REM ---------------------------------------------------------------------------------
@for /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do @set dt=%%i-%%j-%%k-%%l
@for /f "Tokens=1" %%i in ('time /t') do @set tm=-%%i
@set tm=%tm::=-%
@set dtt=%dt%%tm%
@echo SQL file system full backup set: %dtt%.
@echo Please wait...
@C:\WINDOWS\system32\ntbackup.exe backup "@C:\download\GoLive\SQL Server\set_SQL.bks" /a /d "Backup %dtt%" /v:no /r:no /rs:no /hc:off /m copy /j "%dtt%" /l:s /f "C:\SQL Server Backup\SQLFileSystemBackup %dtt%.bkf"
@ECHO eXPORT HKEY_LOCAL_MACHINE registries ..
@C:\WINDOWS\system32\REG.exe EXPORT HKLM "C:\SQL Server Backup\HKEY_LOCAL_MACHINE_Backup %dtt%.reg"
-----------------------------
The improved code:
@ECHO OFF
REM --------------------------------------------------------------------------------
REM Procedure Name: BackupSQLFileSystem.bat
REM Created By: Bob Wang
REM Creation Date: 17-Mar-2010
REM Functionality: 1. backup SQL File System
REM 2. backup hKEY_LOCAL_MACHINE registries
REM
REM Modification History:
REM ---------------------------------------------------------------------------------
@set path1="C:\WINDOWS\system32\ntbackup.exe"
@set path2="@C:\download\GoLive\SQL Server\set_SQL.bks"
@set path3="C:\SQL Server Backup\SQLFileSystemBackup %dtt%.bkf"
@set path4="C:\SQL Server Backup\HKEY_LOCAL_MACHINE_Backup %dtt%.reg"
@for /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do @set dt=%%i-%%j-%%k-%%l
@for /f "Tokens=1" %%i in ('time /t') do @set tm=-%%i
@set tm=%tm::=-%
@set dtt=%dt%%tm%
@echo SQL file system full backup set: %dtt%.
@echo Please wait...
@%path1% backup %path2% /a /d "Backup %dtt%" /v:no /r:no /rs:no /hc:off /m copy /j "%dtt%" /l:s /f %path3%
@ECHO eXPORT HKEY_LOCAL_MACHINE registries ..
@C:\WINDOWS\system32\REG.exe EXPORT HKLM %path4%
