Disaster Recovery Plan??

[sneha]

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

[PD]

Maybe this answer the thing -
To prepare for a disaster, do the following:

Periodically dump all databases, preferably to a disk on another computer in another building (but beware of network load), and also to a tape device. Transaction logs can be handled similarly.


Maintain system logs in a secure fashion. Record the directory where all SQL Server files are located, especially the Master.dat file. Keep records of all service packs installed for both Windows NT Server and SQL Server. Keep records of Net-libraries used, the security mode, and the SA password. Keep records of the specified database options.


Record in scripts ALL size changes for ALL devices and databases. This is crucial to simplify recovery in this situation!


Maintain a base functionality script for quickly assessing minimal capability (see the note at the bottom of this article).


Assess the following disaster recovery steps ahead of time on another server, and amend the steps as necessary.




------------
sneha at 11/15/00 4:24:31 PM

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

This I already found in BOL. I am looking for answers from Experienced DBA who know that even after being fully prepared, at the time of disaster you think oh I should have done that too.

Thanks


------------
PD at 11/15/00 4:31:05 PM


Maybe this answer the thing -
To prepare for a disaster, do the following:

Periodically dump all databases, preferably to a disk on another computer in another building (but beware of network load), and also to a tape device. Transaction logs can be handled similarly.


Maintain system logs in a secure fashion. Record the directory where all SQL Server files are located, especially the Master.dat file. Keep records of all service packs installed for both Windows NT Server and SQL Server. Keep records of Net-libraries used, the security mode, and the SA password. Keep records of the specified database options.


Record in scripts ALL size changes for ALL devices and databases. This is crucial to simplify recovery in this situation!


Maintain a base functionality script for quickly assessing minimal capability (see the note at the bottom of this article).


Assess the following disaster recovery steps ahead of time on another server, and amend the steps as necessary.




------------
sneha at 11/15/00 4:24:31 PM

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

[Jesse Roberge]

Make sure those backups work. Test restore them and never let the jobs fail. You don't have to worry about logins, etcc.. A full database backup is the entire datbases - permissions, tables, triggers, diagrams, and all. If you what to make a backup of the schema, then you can script the objects and stash them somewheres.

------------
at 11/15/00 4:38:31 PM

This I already found in BOL. I am looking for answers from Experienced DBA who know that even after being fully prepared, at the time of disaster you think oh I should have done that too.

Thanks


------------
PD at 11/15/00 4:31:05 PM


Maybe this answer the thing -
To prepare for a disaster, do the following:

Periodically dump all databases, preferably to a disk on another computer in another building (but beware of network load), and also to a tape device. Transaction logs can be handled similarly.


Maintain system logs in a secure fashion. Record the directory where all SQL Server files are located, especially the Master.dat file. Keep records of all service packs installed for both Windows NT Server and SQL Server. Keep records of Net-libraries used, the security mode, and the SA password. Keep records of the specified database options.


Record in scripts ALL size changes for ALL devices and databases. This is crucial to simplify recovery in this situation!


Maintain a base functionality script for quickly assessing minimal capability (see the note at the bottom of this article).


Assess the following disaster recovery steps ahead of time on another server, and amend the steps as necessary.




------------
sneha at 11/15/00 4:24:31 PM

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

[Tim]

http://www.mssqlserver.com/backup/ - Mike Hotek posted a DR document on his web site going over this stuff.

When we lost a server, some of things that took a while to find were disks with the actual tape drivers
to read the tape back and serial numbers (cd-key) for the tape software. Thank god it wasn't a fire.

Sysusers are in the database you are restoring but probably not syslogins (unless you restore master as well)
Unless you have integrated security, you may wish to know what passwords to assign to individuals (found in syslogins in
master (only encrypted in 6.5 ).
If you have batch programs running, you may have to recompile them if you change their password (got source code?)

You need to decide who is going to do what. Plan for your worst nightmare
Your one week off after the big conversion. Your in disneyworld with your wife whom you haven't seen in
3 months due to work. Marrige on the rocks. The big day happens.
Your pager rings as your standing in line for space mountain (1/2 hour wait in heat with kids in tow)
Wife starts looking for divorce lawyer's phone number in handbag.

Do you want to start to walk a junior person through scenarios at that point. I didn't think so.

Where I was before, we had a simple drill. Go to an offsite location and have your boss read
your disaster plan (you do have it written down?) and have him rebuild server from scratch literally NT cd, service pack, tape driver,
tape restore (if she/he needs to recall tapes, make him call, and sign the sheet from the driver from offsite tape company) If he lost
his id, its his problem). Surge protector and battery backup on disaster box? Enough outlets in the computer room?
Nearest Deli and doughnut shop? This could go on for awhile.

Let them know that its 3 weeks to order an equivalently sized server and thats rushing it.
Who would have thought we needed a 30 amp plug for that big server only to have it blow its motherboard due to
someone screwing something else up.

Only you can't talk - only watch. Finds problems in the
documentation and what needs to be written down very quickly. You can't detail every possible
scenario but just thinking about it from the smallest problem (forgot a user password) to phone numbers for
tape facility (driving directions too!) and nearby hospitals. (Boss had stroke due to stupid tape drive).
You get paged again telling you of your new promotion and how soon can you return to continue loading
transaction logs. Maybe you should have paid the retainer to that consulting firm to have a tape jockey load
tapes why you fly home.

Also, specify what is not covered. NT? Firewall problems? Router down? Hardware copy protection devices lost
in fire? Get others in charge of things that are not your responsibility to sign off that they agree to what you
think they should be responsible for. The business has to agree (in writing) to accept a certain amount of
downtime during disaster. If they can't agree to accept downtime, there's your budget for alternate locations and more hardware.

Ideally, this brings you back to the point that you don't want to have a disaster. So do everything in you power to prevent distaster (mirroring, redundantcy, intelligent co-workers, limited access, up to date
consistency checks)

PS what if the networking guy is with you at Space Mountain? (at the 2001 SQL PASS conference)

Life just got more interesting for you.
------------
Jesse Roberge at 11/15/00 5:50:25 PM

Make sure those backups work. Test restore them and never let the jobs fail. You don't have to worry about logins, etcc.. A full database backup is the entire datbases - permissions, tables, triggers, diagrams, and all. If you what to make a backup of the schema, then you can script the objects and stash them somewheres.

------------
at 11/15/00 4:38:31 PM

This I already found in BOL. I am looking for answers from Experienced DBA who know that even after being fully prepared, at the time of disaster you think oh I should have done that too.

Thanks


------------
PD at 11/15/00 4:31:05 PM


Maybe this answer the thing -
To prepare for a disaster, do the following:

Periodically dump all databases, preferably to a disk on another computer in another building (but beware of network load), and also to a tape device. Transaction logs can be handled similarly.


Maintain system logs in a secure fashion. Record the directory where all SQL Server files are located, especially the Master.dat file. Keep records of all service packs installed for both Windows NT Server and SQL Server. Keep records of Net-libraries used, the security mode, and the SA password. Keep records of the specified database options.


Record in scripts ALL size changes for ALL devices and databases. This is crucial to simplify recovery in this situation!


Maintain a base functionality script for quickly assessing minimal capability (see the note at the bottom of this article).


Assess the following disaster recovery steps ahead of time on another server, and amend the steps as necessary.




------------
sneha at 11/15/00 4:24:31 PM

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

[Jesse Roberge]

Whoa lots to think about when the company gets larger. To add to the complexity of everything, the servers are most likely not clustered, located wherever and people can't find the boxes. They are probably shipping data between each other with replication, log shipping, dts/bcp, and bad legacy VB code.

I had to reinstall NT on one of the lesser SQL boxes yesterday. It quit talking to the network through NT functions (TCP/IP worked fine though). Reisntalling fixed the problem but I had to reinstall the SQL server as well. Running SP_attach_DB on the database files was easy. But when I tried to recreate the logins in the database, which then I found that I couldn't use the server naems because there are orphaned logins (suid was set to NULL) in the database under the same name and EM can't see these orphaned logins.

I just realized that restoring a backup of master would probably be quicker.

------------
Tim at 11/16/00 2:18:20 PM

http://www.mssqlserver.com/backup/ - Mike Hotek posted a DR document on his web site going over this stuff.

When we lost a server, some of things that took a while to find were disks with the actual tape drivers
to read the tape back and serial numbers (cd-key) for the tape software. Thank god it wasn't a fire.

Sysusers are in the database you are restoring but probably not syslogins (unless you restore master as well)
Unless you have integrated security, you may wish to know what passwords to assign to individuals (found in syslogins in
master (only encrypted in 6.5 ).
If you have batch programs running, you may have to recompile them if you change their password (got source code?)

You need to decide who is going to do what. Plan for your worst nightmare
Your one week off after the big conversion. Your in disneyworld with your wife whom you haven't seen in
3 months due to work. Marrige on the rocks. The big day happens.
Your pager rings as your standing in line for space mountain (1/2 hour wait in heat with kids in tow)
Wife starts looking for divorce lawyer's phone number in handbag.

Do you want to start to walk a junior person through scenarios at that point. I didn't think so.

Where I was before, we had a simple drill. Go to an offsite location and have your boss read
your disaster plan (you do have it written down?) and have him rebuild server from scratch literally NT cd, service pack, tape driver,
tape restore (if she/he needs to recall tapes, make him call, and sign the sheet from the driver from offsite tape company) If he lost
his id, its his problem). Surge protector and battery backup on disaster box? Enough outlets in the computer room?
Nearest Deli and doughnut shop? This could go on for awhile.

Let them know that its 3 weeks to order an equivalently sized server and thats rushing it.
Who would have thought we needed a 30 amp plug for that big server only to have it blow its motherboard due to
someone screwing something else up.

Only you can't talk - only watch. Finds problems in the
documentation and what needs to be written down very quickly. You can't detail every possible
scenario but just thinking about it from the smallest problem (forgot a user password) to phone numbers for
tape facility (driving directions too!) and nearby hospitals. (Boss had stroke due to stupid tape drive).
You get paged again telling you of your new promotion and how soon can you return to continue loading
transaction logs. Maybe you should have paid the retainer to that consulting firm to have a tape jockey load
tapes why you fly home.

Also, specify what is not covered. NT? Firewall problems? Router down? Hardware copy protection devices lost
in fire? Get others in charge of things that are not your responsibility to sign off that they agree to what you
think they should be responsible for. The business has to agree (in writing) to accept a certain amount of
downtime during disaster. If they can't agree to accept downtime, there's your budget for alternate locations and more hardware.

Ideally, this brings you back to the point that you don't want to have a disaster. So do everything in you power to prevent distaster (mirroring, redundantcy, intelligent co-workers, limited access, up to date
consistency checks)

PS what if the networking guy is with you at Space Mountain? (at the 2001 SQL PASS conference)

Life just got more interesting for you.
------------
Jesse Roberge at 11/15/00 5:50:25 PM

Make sure those backups work. Test restore them and never let the jobs fail. You don't have to worry about logins, etcc.. A full database backup is the entire datbases - permissions, tables, triggers, diagrams, and all. If you what to make a backup of the schema, then you can script the objects and stash them somewheres.

------------
at 11/15/00 4:38:31 PM

This I already found in BOL. I am looking for answers from Experienced DBA who know that even after being fully prepared, at the time of disaster you think oh I should have done that too.

Thanks


------------
PD at 11/15/00 4:31:05 PM


Maybe this answer the thing -
To prepare for a disaster, do the following:

Periodically dump all databases, preferably to a disk on another computer in another building (but beware of network load), and also to a tape device. Transaction logs can be handled similarly.


Maintain system logs in a secure fashion. Record the directory where all SQL Server files are located, especially the Master.dat file. Keep records of all service packs installed for both Windows NT Server and SQL Server. Keep records of Net-libraries used, the security mode, and the SA password. Keep records of the specified database options.


Record in scripts ALL size changes for ALL devices and databases. This is crucial to simplify recovery in this situation!


Maintain a base functionality script for quickly assessing minimal capability (see the note at the bottom of this article).


Assess the following disaster recovery steps ahead of time on another server, and amend the steps as necessary.




------------
sneha at 11/15/00 4:24:31 PM

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

[Jesse Roberge]

I don't like Backup Exec's SQL agent. It interferes with the disk / developer error backups, causeing one or both sides to fail. I told them to stop using the SQL agent and just pick up the backup files.

------------
ChuckG at 11/16/00 12:52:05 PM

The best backup and recovery tool to use is Veritas BackupExec with the SQL agent to make hot backups and retores. The current version is suppose to support clustering although I have not worked with the most rtecent version. I recommend using the passive/2nd server, in an active/passive cluster pair as the backup server. However if you have an active/active pair or do not want to bog down the database in the event of a fail-over, you need a separate dedicated backup server with permission to attach and make the backups using BackupExec. In the event of a fail-over, your standby server should host the database or if any data is corrupted you can reload it from the most current backup job. Should a complete disaster take down the cluster, it may be easier to rebuild it completely with a fresh database install and then reload the database from the backup. The actual reload of the files on a 3GB database can taqke as little as 20 mins for a hot-restore or 60 mins for device dropping, recreation, and reload. In any event we use Veritas BackupExec as a corporate solution enterprise wide currently. Hope this helps, best regards.


------------
Jesse Roberge at 11/15/00 5:50:25 PM

Make sure those backups work. Test restore them and never let the jobs fail. You don't have to worry about logins, etcc.. A full database backup is the entire datbases - permissions, tables, triggers, diagrams, and all. If you what to make a backup of the schema, then you can script the objects and stash them somewheres.

------------
at 11/15/00 4:38:31 PM

This I already found in BOL. I am looking for answers from Experienced DBA who know that even after being fully prepared, at the time of disaster you think oh I should have done that too.

Thanks


------------
PD at 11/15/00 4:31:05 PM


Maybe this answer the thing -
To prepare for a disaster, do the following:

Periodically dump all databases, preferably to a disk on another computer in another building (but beware of network load), and also to a tape device. Transaction logs can be handled similarly.


Maintain system logs in a secure fashion. Record the directory where all SQL Server files are located, especially the Master.dat file. Keep records of all service packs installed for both Windows NT Server and SQL Server. Keep records of Net-libraries used, the security mode, and the SA password. Keep records of the specified database options.


Record in scripts ALL size changes for ALL devices and databases. This is crucial to simplify recovery in this situation!


Maintain a base functionality script for quickly assessing minimal capability (see the note at the bottom of this article).


Assess the following disaster recovery steps ahead of time on another server, and amend the steps as necessary.




------------
sneha at 11/15/00 4:24:31 PM

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

[Ravi]

we have implemented vinca co-standby server. It is a cheaper and easy-to-implement solution
when compared to other similar solutions. Clustering will create a big hole in your pocket.

You can also use replication as a method for backup though microsoft says no to such an idea.

rgds,
ravi

------------
Jesse Roberge at 11/16/00 6:04:19 PM


Whoa lots to think about when the company gets larger. To add to the complexity of everything, the servers are most likely not clustered, located wherever and people can't find the boxes. They are probably shipping data between each other with replication, log shipping, dts/bcp, and bad legacy VB code.

I had to reinstall NT on one of the lesser SQL boxes yesterday. It quit talking to the network through NT functions (TCP/IP worked fine though). Reisntalling fixed the problem but I had to reinstall the SQL server as well. Running SP_attach_DB on the database files was easy. But when I tried to recreate the logins in the database, which then I found that I couldn't use the server naems because there are orphaned logins (suid was set to NULL) in the database under the same name and EM can't see these orphaned logins.

I just realized that restoring a backup of master would probably be quicker.

------------
Tim at 11/16/00 2:18:20 PM

http://www.mssqlserver.com/backup/ - Mike Hotek posted a DR document on his web site going over this stuff.

When we lost a server, some of things that took a while to find were disks with the actual tape drivers
to read the tape back and serial numbers (cd-key) for the tape software. Thank god it wasn't a fire.

Sysusers are in the database you are restoring but probably not syslogins (unless you restore master as well)
Unless you have integrated security, you may wish to know what passwords to assign to individuals (found in syslogins in
master (only encrypted in 6.5 ).
If you have batch programs running, you may have to recompile them if you change their password (got source code?)

You need to decide who is going to do what. Plan for your worst nightmare
Your one week off after the big conversion. Your in disneyworld with your wife whom you haven't seen in
3 months due to work. Marrige on the rocks. The big day happens.
Your pager rings as your standing in line for space mountain (1/2 hour wait in heat with kids in tow)
Wife starts looking for divorce lawyer's phone number in handbag.

Do you want to start to walk a junior person through scenarios at that point. I didn't think so.

Where I was before, we had a simple drill. Go to an offsite location and have your boss read
your disaster plan (you do have it written down?) and have him rebuild server from scratch literally NT cd, service pack, tape driver,
tape restore (if she/he needs to recall tapes, make him call, and sign the sheet from the driver from offsite tape company) If he lost
his id, its his problem). Surge protector and battery backup on disaster box? Enough outlets in the computer room?
Nearest Deli and doughnut shop? This could go on for awhile.

Let them know that its 3 weeks to order an equivalently sized server and thats rushing it.
Who would have thought we needed a 30 amp plug for that big server only to have it blow its motherboard due to
someone screwing something else up.

Only you can't talk - only watch. Finds problems in the
documentation and what needs to be written down very quickly. You can't detail every possible
scenario but just thinking about it from the smallest problem (forgot a user password) to phone numbers for
tape facility (driving directions too!) and nearby hospitals. (Boss had stroke due to stupid tape drive).
You get paged again telling you of your new promotion and how soon can you return to continue loading
transaction logs. Maybe you should have paid the retainer to that consulting firm to have a tape jockey load
tapes why you fly home.

Also, specify what is not covered. NT? Firewall problems? Router down? Hardware copy protection devices lost
in fire? Get others in charge of things that are not your responsibility to sign off that they agree to what you
think they should be responsible for. The business has to agree (in writing) to accept a certain amount of
downtime during disaster. If they can't agree to accept downtime, there's your budget for alternate locations and more hardware.

Ideally, this brings you back to the point that you don't want to have a disaster. So do everything in you power to prevent distaster (mirroring, redundantcy, intelligent co-workers, limited access, up to date
consistency checks)

PS what if the networking guy is with you at Space Mountain? (at the 2001 SQL PASS conference)

Life just got more interesting for you.
------------
Jesse Roberge at 11/15/00 5:50:25 PM

Make sure those backups work. Test restore them and never let the jobs fail. You don't have to worry about logins, etcc.. A full database backup is the entire datbases - permissions, tables, triggers, diagrams, and all. If you what to make a backup of the schema, then you can script the objects and stash them somewheres.

------------
at 11/15/00 4:38:31 PM

This I already found in BOL. I am looking for answers from Experienced DBA who know that even after being fully prepared, at the time of disaster you think oh I should have done that too.

Thanks


------------
PD at 11/15/00 4:31:05 PM


Maybe this answer the thing -
To prepare for a disaster, do the following:

Periodically dump all databases, preferably to a disk on another computer in another building (but beware of network load), and also to a tape device. Transaction logs can be handled similarly.


Maintain system logs in a secure fashion. Record the directory where all SQL Server files are located, especially the Master.dat file. Keep records of all service packs installed for both Windows NT Server and SQL Server. Keep records of Net-libraries used, the security mode, and the SA password. Keep records of the specified database options.


Record in scripts ALL size changes for ALL devices and databases. This is crucial to simplify recovery in this situation!


Maintain a base functionality script for quickly assessing minimal capability (see the note at the bottom of this article).


Assess the following disaster recovery steps ahead of time on another server, and amend the steps as necessary.




------------
sneha at 11/15/00 4:24:31 PM

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

[Carl B.]

I work in Business Recovery Services and I do disaster recovery tests on different platforms on a daily basis.
The first thing to do is to ensure that you have valid backups.
All the procedures in the world don't help you if don't have the data you want to restore.
It would be ideal if your company has resoureces to have a test server.

For SQL server , I've found that Arcserve works pretty well.
The easiest/fastest way I've found to restore a SQL server is start by restoring the master database ( remember that you need to start the MSSQL Service with the startup parameter -m in order for that to succeed , this is also in BOL)
Testore the master database by backup media ( you need to now the session number for it so keep a track them when you do the backups...this will save you from scanning the tape)

When finished restart the MSSQL service.

After that restore the rest of the system databases by session and restore your 'own' databaases as last.

Restart the MSSQL Service.

This works fine with full backups.

Word of a warning.... DO NOT disable the alerts in backup software....
I had a client who had some problems restoring thei SQL Server..
I discovered that the SQL agent hadn't run properly in 4 months.
Somebody had changed their admin password...

They were extremely happy to find this out in a test rather that in a disaster.

Hope this helps.

[JasonW]

I agree. I found the same errors and sometimes it would mark our databases suspect.


------------
Jesse Roberge at 11/16/00 6:06:46 PM

I don't like Backup Exec's SQL agent. It interferes with the disk / developer error backups, causeing one or both sides to fail. I told them to stop using the SQL agent and just pick up the backup files.

------------
ChuckG at 11/16/00 12:52:05 PM

The best backup and recovery tool to use is Veritas BackupExec with the SQL agent to make hot backups and retores. The current version is suppose to support clustering although I have not worked with the most rtecent version. I recommend using the passive/2nd server, in an active/passive cluster pair as the backup server. However if you have an active/active pair or do not want to bog down the database in the event of a fail-over, you need a separate dedicated backup server with permission to attach and make the backups using BackupExec. In the event of a fail-over, your standby server should host the database or if any data is corrupted you can reload it from the most current backup job. Should a complete disaster take down the cluster, it may be easier to rebuild it completely with a fresh database install and then reload the database from the backup. The actual reload of the files on a 3GB database can taqke as little as 20 mins for a hot-restore or 60 mins for device dropping, recreation, and reload. In any event we use Veritas BackupExec as a corporate solution enterprise wide currently. Hope this helps, best regards.


------------
Jesse Roberge at 11/15/00 5:50:25 PM

Make sure those backups work. Test restore them and never let the jobs fail. You don't have to worry about logins, etcc.. A full database backup is the entire datbases - permissions, tables, triggers, diagrams, and all. If you what to make a backup of the schema, then you can script the objects and stash them somewheres.

------------
at 11/15/00 4:38:31 PM

This I already found in BOL. I am looking for answers from Experienced DBA who know that even after being fully prepared, at the time of disaster you think oh I should have done that too.

Thanks


------------
PD at 11/15/00 4:31:05 PM


Maybe this answer the thing -
To prepare for a disaster, do the following:

Periodically dump all databases, preferably to a disk on another computer in another building (but beware of network load), and also to a tape device. Transaction logs can be handled similarly.


Maintain system logs in a secure fashion. Record the directory where all SQL Server files are located, especially the Master.dat file. Keep records of all service packs installed for both Windows NT Server and SQL Server. Keep records of Net-libraries used, the security mode, and the SA password. Keep records of the specified database options.


Record in scripts ALL size changes for ALL devices and databases. This is crucial to simplify recovery in this situation!


Maintain a base functionality script for quickly assessing minimal capability (see the note at the bottom of this article).


Assess the following disaster recovery steps ahead of time on another server, and amend the steps as necessary.




------------
sneha at 11/15/00 4:24:31 PM

What is the best recovery plan consist of??

Backup : Full and Transaction log.
Hardware : Clustering, RAID

I am not sure about what all need to be Scripted for having a good disaster recovery plan??
Scripting:: tables, views, defaults, rules, users, roles, permissions
Backup : Triggers, Stored Procedures

Your inputs are appreciated.

Sneha

[Dmitri]

Hi Carl,

Could you please post the sequence of restore process (I mean which database restore at first msdb or user db) on the server with replication (publisher and distributor databases) after the disk crashed.

Regards,
Dmitri (DBA)

------------
Carl B. at 11/17/00 6:44:05 AM

I work in Business Recovery Services and I do disaster recovery tests on different platforms on a daily basis.
The first thing to do is to ensure that you have valid backups.
All the procedures in the world don't help you if don't have the data you want to restore.
It would be ideal if your company has resoureces to have a test server.

For SQL server , I've found that Arcserve works pretty well.
The easiest/fastest way I've found to restore a SQL server is start by restoring the master database ( remember that you need to start the MSSQL Service with the startup parameter -m in order for that to succeed , this is also in BOL)
Testore the master database by backup media ( you need to now the session number for it so keep a track them when you do the backups...this will save you from scanning the tape)

When finished restart the MSSQL service.

After that restore the rest of the system databases by session and restore your 'own' databaases as last.

Restart the MSSQL Service.

This works fine with full backups.

Word of a warning.... DO NOT disable the alerts in backup software....
I had a client who had some problems restoring thei SQL Server..
I discovered that the SQL agent hadn't run properly in 4 months.
Somebody had changed their admin password...

They were extremely happy to find this out in a test rather that in a disaster.

Hope this helps.

[Chiu Smith]

/Magnus

I would like to include some points on Microsoft Cluster Server concerning disaster recovery.

The MSCS (Microsoft Cluster Server) has 2 physical nodes and 2 virtual nodes in an ACTIVE-ACTIVE setup. The VIRTUAL nodes can move from one physical node to the other with all of their resources (shared drives,IP address,MSSQL,SQL Agent). The VIRTUAL servers are the ones that your applications will point to.

This setup is very stable and reliable. If any resources doesn&#39;t respond to ~3 inquiries within 1 second the cluster administrator will failover. This is very fast even under load (< 60 seconds). In this system you have only one single point of failure, the shared drives. If you log ship (resource kit CD) to a second cluster and have a fail over DSN in your web page include file and a SQL script to bring the read only servers to restored condition. You have a very robust system.

Chiu

------------
at 11/19/00 8:04:36 PM

The sentance:
If it&#39;s stand alone you just can make one big backup of your entire system setup

should be:
If it&#39;s stand alone you just can&#39;t make one big backup of your entire system setup

/Magnus

------------
Magnus at 11/19/00 8:01:01 PM

Yea the things mentioned below must be answered first. And what kind of backup can you afford. Can you afford to have an identical cluster setup (or single machine), ready to take over the databases or can you take the downtime to reinstall the entire cluster if needed and then restore databases?

Also what kind of cluster we are talking about here. Is it an active/active or active/passive. And what&#39;s the reason you are clustering. Is it to split the load between different servers and at the same time acheiving high availability for both servers or do you use one of the servers in the cluster as a backup server already.

Running an active/active cluster means you have additional things to consider. In an active/active each server has it&#39;s own name as you know. Say you have 50 websites connecting to databases on this cluster. All sites have some reference to either server1 or server2. This means you can&#39;t just switch to one single backup server and be home free. You either need to go trough the websites code or the registry changing the database references pointing to the backup servers. If you have a WINS in your domain you can of course change the old names to both point to the same new IP (backup server). Or you update the lmhost files on all servers pointing the two old cluster names to a new IP. Or you have two servers ready as backup, clustered or not.

If you are running active/passive using one server as backup already, then you only need a single server as backup. Have it configures with the same name as the old one, ready to bring up and restore databases incase the enire cluster burns down.

If you don&#39;t have the money to have a backup server standing ready, you need to minimize the time it takes to get the cluster back up. Do you have full system backup of the cluster servers so you can restoer the entire system? This can be done with software like Seagate BackupExec.

What kind of disk subsystem are you using and how do you communicate with it? Is it a by fibre or scsi channel? Is it a stand alone subsystem like Compaq&#39;s MA8000 or is it &#34;integrated&#34;. Be sure to have configs saved of you disksystem configuration, ready to be run on a new disksystem if needed. Save all drivers etc needed to communicate with the disksytem. If it&#39;s stand alone you just can make one big backup of your entire system setup. You have operating system files in the servers, you have MSSQL on differnt drives on the disksystem, you have the qourum drive etc. You need to be able to bring any part in the cluster up if it fails, be it servers or disksystem.

When it comes to &#34;disaster plans&#34; you have different levels of disaster. Worst being of course being the enitire cluster burning down. But what of the diskystem crashes but both the servers are ok? Do you have a fast way for connecting them to a new disksytem? What if the communication between the servers and disksystem fail, say you are using dual fibre hubs. Do you have extra hubs? etc.

The list can be made long. Disaster plans are very specific to your own setup of the cluster when it comes to hardware,software,your use of the cluster, acceptable downtime etc. There is no single ultimate disaster plan that covers all situations.

Common for all situations though, as someone mentioned before, is to have your data on backup. You can have 10 extras servers standing by, but if you loose your data you can get nowhere.

Make sure you have working backups of all your databases. Try saving a couple of weeks (or something) localy, and rest on tape backup or another server. Have multiple copies of the backups. Have one in your current offices and send one or two elsewhere. Incase your entire office blows up...

To verify your backups, restore them on another sql server to see that they works. Now and then do test restores from your tape backups too. Not that uncommon really that your tape can be damaged.

You can automate the process of taking backup of databases, moving them to another server, restoring them there, if restore was ok backup on tape else notify admin.

Woha, that was a long post. Well it&#39;s just proves the point that you there is no single ultimate disaster plan, that you can copy and paste on a message board

/Magnus

------------
Babu Ambati at 11/16/00 1:04:49 PM

There quite a few facts to be considered before you decide a disaster recovery plan. Though some things sound stupid but they are nessesary .
Like how much time can the company spare for recovery.
How big is the database and how important is the information.
Can you afford to lose some data.
How much of space you can afford.
How much is the network traffic at the time of backup....to name a few.
As many have mentioned the basic details,but depending on the answers to the above questions you can devise a good disaster recovery plan. And you need to test it before implementing it. Though this may not completely serve your purpose , but it will give you a brief idea on your question.
Best of luck.