Sample SQL Server Disaster Recover Plans

(I) XXXX MSSQL Server Details

(a)   Hardware Configuration

(b)   Server Software Configuration

1.1 Server Type
1.2 Computer Model
1.3 RAM Memory 3840 MB
1.4 No. of CPU’s 4
1.5 Processor Type & Speed
1.6 Hard Disk Space
2.1 Operating System Windows NT 4.0 service pack 6.0
2.2 RDBMS (a)  MS SQL Server 7.0 Standard Edition (server version)

(b) Product Version – 7.00.961

2.3 General (SQL Server) (a) Autostart SQL Server, SQL ServerAgent & MSDTC

(b)  OS Memory – 3840 MB

(c)  Root Directory – “d:MSSQL7”

2.4 Memory (SQL Server) Dynamically configure SQL Server Memory (0 to 3840 Mb)
2.5 Processor (SQL Server) 4 Nos.
2.6 Security (SQL Server) (a)  Authentication (SQL Server & windows NT)

(b)  Audit level (Failure)

2.7 Connections (SQL Server) (a)  Allow other SQL Servers to connect remotely to this SQL Server using RPC.

(b)  Maximum concurrent user connections = 0 (unlimited)

2.8 Server Settings (SQL Server) (a)  Default language – English

(b)  Allow triggers to be fired which fire other triggers (nested triggers)

2.9 Database Settings (SQL Server) (a)  Default Index fill factor to use when rebuilding indexes – Default (optimal)

(b)  Backup/restore time-out period – Wait indefinitely

(c)    Database Details

3.1.1 DBPays General File Name dbpays-test2_Data
File Path D:mssql7dataDBPAYS.mdf
File Group PRIMARY
File Properties (a) Automatically grow file (checked)

(b) File growth – By percent 10 %

(c)  Maximum file size – Unrestricted file growth

Transaction Log File Name dbpays-test2_Log
File Path D:mssql7dataDBPAYS_log.ldf
File Properties (a)  Automatically grow file (checked)

(b)  File growth – By percent 10 %

(c)  Maximum file size – Unrestricted file growth

Options (a) Torn page detection (checked)

(b) Auto Create Statistics (checked)

(c)  Auto Update Statistics (checked)

Permissions None
User Rights bb001384 Public, Db_datareader, db_datawriter
bb001445 Public, Db_datareader, db_datawriter
bb009026 Public
bb009511 Public
BEXADM Public
Dbpays Public, Db_datareader, db_datawriter
Dbpays-qry Public, Db_datareader
3.1.2 Chqatpar General File Name chqatpar_data
File Path D:mssql7datachqatpar.mdf
File Group PRIMARY
File Properties (a) Automatically grow file – checked

(b) File growth – by percent 10 %

(c) Maximum File Size – Unrestricted file growth

Transaction Log File Name chqatpar_Log
File Location D:mssql7datachqatpar_log.ldf
File Properties (a) Automatically grow file – checked

(b) File growth – by percent 10%

(c) Maximum File Size – Unrestricted file growth

Options (a) Auto Shrink (checked)

(b) Auto Create Statistics (checked)

(c)  Auto Update Statistics (checked)

Permissions None
User Rights bb009026 public
bb009511 public
BEXADM public
Chqatpar public, db_datareader, db_datawriter
db_cap_query public, db_datareader,

(d)   Server Name & IP Address

4.1 Server Name xxxxxx
4.2 IP Address xxx.xxx.xxx.xxx
4.3 Server Physical Location KD House

(e)   DR / Standby Server

5.1 Server Name xxxxxx
5.2 IP Address xxx.xxx.xxx.xxx
5.3 Server Physical Location DB House
5.4 Physical MSSQL Files Location D:mssql

(f)     Impacted Audiences & Application 

6.1 Impacted Audiences All the Users using xxxx & xxxx application
6.2 Impacted Applications xxxx & xxxx
6.3 Application Co-ordination user name / user name

(g)   Priority order of Database recovery

7.1 Priority Order (1)   DBPays

(2)   Chqatpar

(g) Backup Details (Location of backup, types of backup, backup schedules)

8.1.1 Database Name DBPays
8.1.2 Types of backup Full & Transactional backups
8.1.3 Location Backup Full backups (Tapes & d:mssql7backup on xxxx)

Transactional backups (on xxxx)

8.1.4 Backup Schedules Full Backups – Once at 1.15 am

Transactional Backups – Twice at 2.00 PM & 7.00 PM

8.2.1 Database Name Chqatpar
8.2.2 Types of backup Full & Transactional backups
8.2.3 Location Backup Full backups (Tapes & d:mssql7backup on xxxx)

Transactional backups (on xxxx)

8.2.4 Backup Schedules Full Backups – Once at 1.15 am

Transactional Backups – Twice at 2.00 PM & 7.00 PM

(h)   Permissible period of acceptable loss

9.1 xxxx Approximate of 8 hrs
9.2 xxxx Approximate of 8 hrs

(i) Permissible acceptable period of downtime for database

10.1 xxxx Approximate of 5 hrs
10.2 xxxx Approximate of 5 hrs

(j) Disaster Recovery Testing schedule

11.1 xxxx Once in every 2 months
11.2 xxxx Once in every 3 months

(k) Disaster Recovery System

12.1 Scenarios of System Failures (a)  Hardware Failure / Operating System failure / Database failure / Natural Disasters / Major Human errors / Computer Viruses –——> Results in failure of the complete database

 (Failure of all the tables & objects)

(b)  Soft corruption / Minor human errors / program or application malfunction ——> Results in failure of partial database

     (Failure of one or few tables or objects)

12.2 Kinds of Recovery (a)  Recovery for the complete failed databaseà

(I)  Recovery procedure – Due to some reason if the database (xxxx & xxxxx) is lost or corrupted,  the complete database is required to restore which will include all the objects. Xxxx server will act as a Disaster Recovery server for both the databases. Latest one number full & two number transactional backup files will have to be transferred from xxxx hard disk (or the tapes where daily full backup is been taken) to the DR Server (xxxx) which is currently having a balance space of around 27 GB. Once one *.bak & two *.trn files are shifted to the DR server, the same can be restored in the following steps :-

–Right click on databases select “All tasks” & then “restore databases”. Fill in the name as xxxx or xxxx at “Restore as database”.

–Select the restore option as “From device” & Restore backup set as “Database – complete”.

–Click on “select devices”, then click “add” & give the exact path of the backup (*.bak) file & then click OK.

–Next go to the “Options” tab & in order to use “NO RECOVERY” option select “Leave database non-operational, but able to restore additional transactional logs” in the Recovery completion state section. 

–Finally click OK to start the full backup restoration.

–After completion of full backup restoration, transactional backup has to be restored.

–Again right click on databases & select  “All tasks” & then “restore databases”. Select in the newly created database such as DBPays or Chqatpar at “Restore as database”.

–Select the restore option as “From device” & Restore backup set as “Transaction log”.

–Click on “select devices”, then click “add” & give the exact path of the backup (*.trn) file & then click OK.

–Next go to the “Options” tab & in order to use “NO RECOVERY” option select “Leave database nonoperational, but able to restore additional transactional logs” in the Recovery completion state section.

–Finally click OK to start the first transactional backup restoration.

–Similarly after restoring the first transactional backup & the second & the final backup too needs to be restored in same steps mentioned above except for restoring the last backup “RECOVERY” option needs to be enabled.

–Go to the “Options” tab & in order to use “RECOVERY” option select “Leave database operational, No additional transactional logs can be restored” in the Recovery completion state section.

–This final step completes the complete process of disaster recovery of the required database on the DR Server.

(II) State of Database after Recovery – The database recovered on the DR Server using the above mentioned method will be recovered till the time last transactional backup was taken on the main production server i.e. xxxx. All the records or data entered after the last transactional backup will be lost.

(b)   Recovery for the partial failed databaseà

(I)  Recovery procedure – Due to some reason if one or few tables of a database gets corrupted then the whole database need not be replaced. The following options needs to be tried before going for complete:

–If possible, try taking a backup of the damaged table database & if it is successful then restore the same backed up database.

–Second option is to try exporting all the possible records from the damaged tables to a dummy database on the DR server if successful then drop the damaged table & reimport the exported table from the DR server.

–If none of the above mentioned options are working successfully then the final option is to restore the last taken full backup & thereafter taken all the transaction backup’s on the DR server as per the steps mentioned above. Identifying the tables which are damaged & the tables which are dependent on these damaged tables are re-exported & overwritten the old records.

–If the record consistency between the tables in the database is not been able to be maintained then the complete backup needs to be restored as per the steps mentioned above for “Recovery for the complete failed database”.

 
Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |