Best recovery plan fror 900 sql dbs? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Best recovery plan fror 900 sql dbs?

Somebody suggested make a copy of mdf and ldf files for all our sql databases
I was wondering is that a good idea? In case of failure we have to quicly restore 900 dbs.
So the idea is that attaching them will be much quicker then restoring
Attaching databases is much faster than restoring them. The only drawback to this would be that you would have to bring down all of these databases each day in order to perform a full backup of them. If this is not a problem, then OK. But if being down for the time to backup 900 databases is an issue, then you real only option is to perform backups and restores. You can also consider using log shipping as an DR option. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Thank you
I will consider log shipping
The only my concern is how it will affect performance?
Considering the number of databases you plan to restore in case of any failure you need to take into consideration lot many factors like the downtime required for copying the latest mdf & ldf files, priority wise restoring of database, size & schedules of these copying files, etc. There will be lot much of manual work involved if you go in for attaching mdf & ldf files. Log Shipping is offcourse a better way of handling your problems, but then u need to check out that backups are performed when there is no load or very minimal load on the server & test it for several days before completly relying on it. Another better way is to go for replication between the production server & the standby server. Considering the number of databases you have u will be required to have a very neat plan regarding the scheduling of various replication jobs so that none of them clash with each other. Regards Bhushan
IF you adopt LOG SHIPPING then no need to go for replication which is again a cumbersome process to keep a track for defined databases. No issues on performance in dealing with LS. Also as an alternative keep all these databases backups on remote location to other server to use in case of disaster recovery. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

> There will be lot much of manual work involved if you go in for attaching mdf & ldf files. I dont necessarily agree with this. I cant think of any part of the process that couldnt be scripted and automated

I agree and second Chappy’s comments. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I keep thinking also about clustered servers
Another drawback of copying mdf/ldf files is they may occupy more space than backup files. Because SQL backup does not consider the free space in your database, it creates a file almost equal to the used space in your database. We have 15 databases (almost 10GB each) in our production and I am using log shipping for our warm standby server. So far, I never had any problems with it and I didn’t observe any performance issues as I take log backups once every 1 hour.
While a cluster helps in regard to a software or server failure, it still doesn’t provide a backup if the disk array goes bad. Ideally, you need to do clustering and also log shipping to another backup server. In fact, I will be implementing such a solution on our mission critical servers in the months ahead. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Another drawback of copying mdf/ldf files is they may occupy more space than backup files. Because SQL backup does not consider the free space in your database, it creates a file almost equal to the used space in your database. For any production setup you should ensure to make room to store necessary backups and files, no compromise. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>