Disaster Recovery plan for SQL Server 2005


Recover SQL Server 2005 Server:

This posting describes the steps to recover SQL Server 2005/2008 at disaster recovery site in case of major disaster in the data center.  The disaster recovery plan assumes a regular backup of SQL databases done, also the production server has E Partition contain data/log files of SQL databases. If there is a major disaster of the SQL database server locate the hardware replacement of the server and the latest backups of the databases which have at least one full backup, Install windows, Install SQL 2005, restore the master database and user databases using SQL script as below.

The same disaster recovery plan can be used with SQL Server 2008 since there are no major changes between SQL 2005 and SQL 2008.

1. Build Windows 2003 Server 64bit

1.1        The server should have 16GB of RAM and 4 Xeon CPU at 3.0GHZ

1.2        Operating system Partitions

Partition Description Size
C:\ OS and Oracle will be installed in this partition 30 GB
E:\ Oracle data files will be stored in this partition 300GB

1.3        Install Windows 2003 64bit with sp2

1.4        Change the machine Name

2. Install SQL 2005 Enterprise Edition 64bit

2.1        Create Active directory account will be used to run SQL server Services(Company\db2recovery_user)

You don’t have to create this account if you will use local system account to run SQL services

2.2        Install SQL 2005

      • Download SQL 2005 installation from Microsoft website 
      • Start the setup
      • Install Prerequisite will start and once it is completed Click Next
      • Welcome to Microsoft SQL server Installation Wizard will show up
      • Click Next
      • System Configuration check will start
      • Verify all the checks completed successfully and Click Next
      • Click Next
      • Check the following components:
          1. SQL server Database services
          2. Analysis Services
          3. Integration Services
          4. Work component, books online and development
      • See Fig. 1 for Details 

Fig. 1

      • Click Advanced >> Database Services>> Data Files
      • Click Browse and Set the data files path to E:\Microsoft SQL Server\ as in Fig. 2

Please note that you have to set the data files path to E:\Microsoft SQL Server\ otherwise the recovery plan will not work

Fig. 2

    • Click Ok >> Click Next
    • Leave the default instance checked and Click Next
    • Service Account form will show up as in Fig. 3, please note you can use built-in system account or AD account created earlier

Fig. 3

      • Click Next
      • Select Mixed Mode, set SA password and Click Next
      • Click Next
      • Click Next
      • Ready to Install Screen will show up
      • Click Install
      • The installation process might take 45 minutes
      • Verify the install completed successfully without errors

2.3        Install Service pack 4

      • Download it from Company file server or from Microsoft
      • Start the installer
      • Leave the default settings and Click next
      • Double click to install

3. SQL databases Recovery Steps

3.1        Restore databases backup from the backup system to the new server, you need to restore the latest full and differential backups to E:\dbsbackup

3.2        Restore OLAP backups from the backup system to the new server, you should restore the latest backups to E:\OLAPbackups

3.3        Recover the database engine

      • Restore the master database

Click Start>>All Programs>>Microsoft SQL Server>> Configuration Tools>> SQL Server configuration Manager
Right Click SQL Server(MSSQLSERVER) >>Properties>> Advanced
Edit Startup Parameters and add “;-c; -m” as in Fig. 4

Fig. 4

Click OK, warning message will show up, Click OK
Right Click SQL Server(MSSQLSERVER) >>Restart
Click Start>>ALL Programs>>Microsoft SQL Server >>SQL Server Management Studio 
Set the Server type to database engine
Set the Server name to the database Server name
Click Connect
Click Databases>> System databases
Right Click Master>> Tasks >> Restore >> Database
Select From Device and add backup file
Click Option and Select “Overwrite the existing database”
Set the file path for the data and log files as in Fig. 5

Fig. 5

Click OK  and wait for the restore to be completed
You might get error message after the restore, don’t worry about it. When you restore the master database, the database engine restart and that is why you get this error message
Click OK>>Click Cancel
Click Start>>All Programs>>Microsoft SQL Server>> Configuration Tools>> SQL Server configuration Manager
Right Click SQL Server(MSSQLSERVER) >>Properties>> Advanced
Edit Startup Parameters and remove “;-c; -m”
Click OK, warning message will show up, Click OK
Right Click SQL Server(MSSQLSERVER) >>Restart
Close SQL Server Management Studio if it is open
Click Start>>All Programs>>Microsoft SQL Server >>SQL Server Management Studio 
Set the Server type to database engine
Set the Server name to the database Server name
Change the authentication mode to “SQL Server authentication”
Login as sa
Sa password will be the password of the old server
Click Connect
Verify the users are showing up
Verify the linked servers are showing up 

      • Restore MSDB database

Stop SQL agent>> Click Databases>> System databases
Right Click msdb>> Tasks >> Restore >> Database
Select From Device and add backup file
Click Option and Select “Overwrite the existing database”
Set the file path for the data and log files as in Fig. 6

Fig. 6

Click OK  and wait for the restore to be completed
Click OK
Verify the jobs show up>> Click SQL Server Agent>>Jobs  
Verify the SSIS packages, Click Connect>> Integration services >> Click Connect>> Stored Packages >> MSDB

      • Restore user databases  
          1. In backup folder (the folder you restored earlier in 3.1) move or delete the backups for master, msdb and model database. The script will restore any user database in the backup folder and don’t want restore any system database so the restore folder shouldn’t have master, msdb and model folders. the backup folder have a folder for every user database and the folder name the same as the database name 
          2. SQL Server Management Studio >>Click New Query
          3. Copy the script below in step f.
          4. Set the Two parameters @SourceDirBackupFiles and @DestDirDbFiles, please read the script comments for details below
          5. Click Execute
          6. The script will run for maybe five hours(it depends on the number of database you will restore and the size of each database:

--*********************************************************************************
-- Written by : Tarek Mohamed Date:May 10, 2008
-- Updated by: Date:
-- Updated by: Date:
-- Updated by: Date:
-- Version:1.0
--*********************************************************************************
--
-- This script will restore databases in folder which each database has folder and the folder name will be
-- the database name so you can use this script to restore one database or more
-- The functnality of the script:
-- 1-Locate the database folder in the source folder
-- 2-Sort the files in in ecach database folder
-- 3-Locate the latest full backup and restore it to the new location
-- 4-Locate the Transaction log files and restore them
-- Please note this script desgined to work with backups form SQL maintenance plans
--
--*********************************************************************************
-- User inputs
-- 1-@SourceDirBackupFiles, this parameter with the path of the backup files
-- 2-@DetDirDbfiles, this paramter will have the path will be used to restore the data and log file
-- for each database
--*********************************************************************************
SET NOCOUNT ON
Declare @SourceDirBackupFiles varchar(500)
Declare @DestDirDbFiles varchar(500)
-- set this paramater with the path of the backups folder
Set @SourceDirBackupFiles='E:\DBsbackup'
-- set this paramater with the destnation of the data and log files
Set @DestDirDbFiles='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
Declare @SourceDirBackupfolder varchar(500)
Declare @dbname Varchar(200)
declare @NoOfRestorefiles int
Declare @dirfile varchar(900)
DECLARE @fname varchar(200)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
Declare @BaseRestoredone int
DECLARE @sql nvarchar(2000)
DECLARE @type char(1)
--*********************************************************************************
-- Initliazation
--*********************************************************************************
-- create temp table to hold all the the folders has the database files to restore
CREATE TABLE ##folders(fname varchar(200),depth int, file_ int)
-- create temp table to hold all the backup files for each database
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
-- create temp table will save backup file info
CREATE TABLE #dbfiles(
LogicalName nvarchar(500)
,PhysicalName nvarchar(500)
,Type char(1)
,FileGroupName nvarchar(500)
,Size numeric(20,0)
,MaxSize numeric(20,0),
filed sql_variant,
createlsn sql_variant,
droplsn sql_variant,
uniqueld sql_variant,
readonlylsn sql_variant,
readwritelsn sql_variant,
backupsizeinbytes sql_variant,
sourceblocksize sql_variant,
filedgroup sql_variant,
loggroupguid sql_variant,
diff sql_variant,
diff1 sql_variant,
isreadonly sql_variant,
ispresent sql_variant)
--*********************************************************************************
-- script body
--*********************************************************************************
--get all the folders from the source backup folder and each folder will be the database name
INSERT ##folders
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Cursor to process each folder(database backup files)
DECLARE folders CURSOR FOR SELECT fname FROM ##folders
open folders
fetch next from folders into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--get all the files from the Directory contains the SQL backup
set @SourceDirBackupfolder =@SourceDirBackupFiles+'\'+@dbname
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupfolder, 1, 1
DECLARE files SCROLL CURSOR FOR SELECT fname from #files order by fname desc
open files
fetch next from files into @fname
set @NoOfRestorefiles=0
set @BaseRestoredone=0
WHILE (@@FETCH_STATUS = 0 and @BaseRestoredone=0)
BEGIN
--print substring(@fname,len(@fname)-2,3)
if substring(@fname,len(@fname)-2,3)='bak'
begin
set @BaseRestoredone=1
--Construct the beginning for the RESTORE DATABASE command
set @dirfile=@SourceDirBackupFiles+'\'+@dbname+'\'+@fname
print @dirfile
SET @sql = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @dirfile + ''' WITH MOVE '
TRUNCATE TABLE #dbfiles -- delete the records in this temp table
Insert #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
-- Declare Cursor for the files for each database
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
--For each database file that the database uses

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + '\' + @dbname
--print @sql
IF @type = 'D'
SET @sql = @sql + '.mdf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '_log.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END
CLOSE dbfiles
DEALLOCATE dbfiles
set @sql=@sql+', NORECOVERY, REPLACE '
print @sql
exec (@sql)
-- ****************************************************************************
-- restore the log files
--****************************************************************************
if @NoOfRestorefiles>0
begin
WHILE @NoOfRestorefiles <> 0
BEGIN
fetch PRIOR from files into @fname
set @dirfile=@SourceDirBackupFiles+'\'+@dbname+'\'+@fname
set @NoOfRestorefiles=@NoOfRestorefiles-1
if @NoOfRestorefiles=0
begin
set @sql='RESTORE LOG ['+@dbname+'] FROM DISK ='''+ @dirfile+''' with recovery'
end
else
begin
set @sql='RESTORE LOG ['+@dbname+'] FROM DISK ='''+ @dirfile+''' with norecovery'
end
print @sql
exec (@sql)
End
end
end
set @NoOfRestorefiles=@NoOfRestorefiles+1
fetch next from files into @fname
End
close files
deallocate files
truncate table #files
fetch next from folders into @dbname
End
--**************************************************
close folders
deallocate folders
drop table ##folders
drop table #files
drop table #dbfiles

3.4        Recover SQL Integration Services(SSIS)

Once you restore the MSDB database as part of the database engine recovery all the packages will show up in SSIS and you don’t need to do anything

3.5        Recover analysis Services

        • Click Start>>All Programs>>Microsoft SQL Server >>SQL Server Management Studio 
        • Set the Server type to Analysis Services
        • Set the Server name to the database Server name
        • Click Connect
        • Right Click databases >>Restore
        • Set restore database name to Cube name
        • Set from backup file to the latest backup file for the cube
        •  The backup file path will be something like this E:\OLAPBackup\cubename_backup_2008_5_13.abf
        • Click OK
        • Restore the remaining Cubes as as described earlier
Advertisements

About Tarek

I have been working in IT for years and I thought it might be good to share my experience using real life scenarios.
This entry was posted in Microsft SQL Server and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s