SQL Server Performance Forum – Threads Archive

Minimize data loss

In a Win2000 environment and SQL-Server 2000, I get a backup everyday after work @ 8:45pm. I have set transaction logs backup every 1 hour, starting @ 7am and ending @ 8pm. Recovery model is Full. The files are all on the server but all information are go into tape backup hourly as well. Assume that my hardware is ok and all backup and transaction logs are on harddisk but something happens on my database at 2:00pm that I have to restore it. I can restore the last night backup and then restore every hour transaction logs up to 1:00pm (because the 2:00pm TLog is not ready). This way I will lose 60 minutes of data. Questions:
1. If I set the transation log backup to every 30 minutes, how it impacts my server performance? 2. Are there other ways to minimize the data loss? 3. Is it good idea to have a replicated server for this situation? Or Network team should take care of this with a parallel mirror server(I don’t know the exact name)? Thanks, CanadaDBA

1. The impact on performance depends on the frequency of updates to the database. Personally, I don’t think that’s problem, but I don’t think that’s necessary either. 2. In your scenario, you will lose 60 minutes of data in the worst case, i.e. if you also lose your active transaction log file. But, if you have a good transaction log file at the time of failure, you can take a manual backup of this log and will be able to recover up to the minute.
So, to minimize data loss make sure you protect your transaction log file (Too bad, we don’t have mirroring of Log files as Oracle do) 3. A log shipping to a standby server is a better option than replication.

Disaster recovery assessment is the simulation of a catastrophic event and/or the evaluation of the disaster recovery plan’s capability to deliver the specified recovery needs. Disaster recovery planning should be considered in light of your own environment, modified accordingly, specified, and verified. To prepare for disaster, it is recommended that you perform these steps periodically: – Back up all system and user databases.
– Maintain system logs in a secure fashion. Keep records of all service packs installed for both Windows & SQL Server. Keep records of network libraries used, the security mode, and the sa password.
– Maintain a base functionality script for assessing minimal capability quickly.
– To minimize the amount of data lost, perform regular database and transaction log backups. Performing a backup operation has minimal effect on running transactions, so backup operations can be run during normal operations.
1) In any case the backup procedure will not impact any of the performance, provided the hardware is under HCL.
2) Data loss is completely depends upon the interval of transaction log restore task.
3) Log shipping provides you warm-standby to use in case of failure, if you want point in time recovery with no data loss then look into Clustered solution in SQL. http://databases.about.com/library/weekly/aa031101a.htm – Disaster recovery planning
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/bkprst_1.htm – Backup planning. HTH
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>