March 10, 2012 at 9:12 pm
Comments posted to this topic are about the item Disaster Recovery: Rebuild system databases on SQL Server 2008 cluster
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comMarch 12, 2012 at 2:02 am
Hi Basit,
Great article: quick and effective. Just a quick question, since in SQL Server 2008 the resource database is stored in Binn directory, did the disaster affect this directory? Also, since this database is never modified except by service pack updates, it could not be faster to copy and paste from another running instance with the same version, language and edition?
Kind regards,
March 12, 2012 at 3:59 am
JManuelN (3/12/2012)
Hi Basit,Great article: quick and effective. Just a quick question, since in SQL Server 2008 the resource database is stored in Binn directory, did the disaster affect this directory? Also, since this database is never modified except by service pack updates, it could not be faster to copy and paste from another running instance with the same version, language and edition?
Kind regards,
As far as I'm aware it only updates resource database files. I never tried copying and pasting of the other instance resource database onto this dricetory. But it's worth trying to see if this hack approach works. đ
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comMarch 12, 2012 at 5:43 am
Great article - thanks.
March 12, 2012 at 5:48 am
Great article! Did you meet the SLA? Congrats on the quick thinking.
March 12, 2012 at 6:00 am
baxterr (3/12/2012)
Great article! Did you meet the SLA? Congrats on the quick thinking.
Thanks for liking the article. Yes we did meet client SLA.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comMarch 12, 2012 at 6:41 am
March 12, 2012 at 7:57 am
Is it possible to have the system/resource databases stored on both node so 1 nodes backups the other one ?
dani
March 12, 2012 at 8:01 am
I really dislike to rebuild system databases, thus I will have a file backup of the system databases (database and log). In the case of such a scenario that you describe, I would copy inn the file backup, start in single user mode, restore the lastest database backups, and voila, the system is up and running. Just keep in mind to update the file copy whenever the system is upgraded.
And the best of all, with this method you don't have to reapply service packs and patches.
March 12, 2012 at 8:05 am
kaplan.dani (3/12/2012)
Is it possible to have the system/resource databases stored on both node so 1 nodes backups the other one ?dani
Hi Dani,
I think this is not possible because drive on which the system databases resides are part of Cluster resource.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comMarch 12, 2012 at 8:10 am
okbangas (3/12/2012)
I really dislike to rebuild system databases, thus I will have a file backup of the system databases (database and log). In the case of such a scenario that you describe, I would copy inn the file backup, start in single user mode, restore the latest database backups, and voila, the system is up and running. Just keep in mind to update the file copy whenever the system is upgraded.And the best of all, with this method you don't have to reapply service packs and patches.
As mentioned in my article our backup drive that contains the file backup of the system databases got corrupted as well. So this is not option in the situation which I have discussed.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comMarch 12, 2012 at 8:20 am
Basit Farooq (3/12/2012)
okbangas (3/12/2012)
As mentioned in my article our backup drive that contains the file backup of the system databases got corrupted as well. So this is not option in the situation which I have discussed.
Sorry, I missed that. In that case I must say that you had extremely bad luck, or poor choice of location of the backup files.
March 12, 2012 at 8:25 am
kaplan.dani (3/12/2012)
Is it possible to have the system/resource databases stored on both node so 1 nodes backups the other one ?dani
No, this is not possible. SQL Server 2012 have some imporovements to this though, as TempDB can be stored on a local drive in clusteres as well. This was not supported in SQL Server 2008 R2 and prior, though you could use volume mount points as a workaround.
In addition, Always On availability groups is a new availability feature in SQL Server 2012 which can be seen more or less like a combination of clustering and database mirroring, where each node will have its own database files, even system databases.
March 12, 2012 at 8:27 am
okbangas (3/12/2012)
Basit Farooq (3/12/2012)
okbangas (3/12/2012)
As mentioned in my article our backup drive that contains the file backup of the system databases got corrupted as well. So this is not option in the situation which I have discussed.Sorry, I missed that. In that case I must say that you had extremely bad luck, or poor choice of location of the backup files.
Retention for keeping the backup on SAN storage on server is three days. Then they got copied to TAPE and stored offsite for security reasons. We host sensitive client data so can't keep every thing on out Network location. Its really a bad luck because backup drive is dead and because of this we cannot access backup files. Network team restored the backup drive and in the mean time I rebuilded system databases.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comMarch 12, 2012 at 8:47 am
Good article, however; before anyone decides to use this method as their form of DR, I would highly recommend they rethink this. Unfortunately in your situation you were limited, however; you would possibly still require the use for the backups. When you restore all of the system database, if the server was previously used to run jobs or store any SSIS packages, this would have been lost and would would require a restore of the MSDB backup. Also when the master is restored, all of the databases would have to be re-attached and the SQL logins would have been lost.
As having been down this road multiple times and having also tested this a multitude of times, I would recommend that everyone thoroughly think out what solution is best given the amount of time they have to bring their server back online. Practice..practice..practice.. is crucial to ensure a smooth recovery process. I would recommend leaving a copy of backups local as well as at a remote location. I also employ a script daily that creates a script to recreate all of my logins.
Hope this is helpful.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply