June 1, 2005 at 8:46 am
filesystem backups are also being performed separately. At this time I'm primarily concerned with three instances for applications called Sharepoint, Altiris and CorpTax.
I believe some of the instances were installed during the installation of the application. Sharepoint is one application where the vendor insists that its own tool be used for all backups rather than the HP Data Protector Microsoft SQL Server 7.0/2000 Agent. I need to look into that more.
Databases within these instances have mixed Recovery Models. User databases are usually set to Full recovery model and are owned by a domain Administrator. Others, such as msdb and master, are set to Simple recovery model and the owner of these is usually sa.
The backup is a Full backup using the HP Data Protector SQL Server Agent with no transaction logs being backed up. I think nothing is being done with transaction logs.
In the one instance I've just looked at, I see transaction logs from the time of the original install. I would think that the transaction logs could overlap the full backups. For example, a full backup might occur at 8 PM and writing to the previous transaction log completed at 7:30 PM with the following transaction log still being written to.
The users appear to feel that point-in-time recovery would not be necessary but I also need to verify that. Would there be a problem restoring from the full backup into these instances where the databases have different Recovery Models? In a restore can SQL Server transaction logs be applied after a full backup in the manner of applying Oracle archive logs or do they need to have been backed up first?
Some documentation says that if there is a database failure, the transaction log should be backed up at that time to aid in recovery. If there is a disk failure, that would not be possible.
I'm not sure how to proceeed. The backups are handled by a different group here - the LAN group. I would be responsible for any restore/recovery. Please advise. I'm new to this.
Thank you! Merrily Blagen Donaldson Company
June 3, 2005 at 10:55 am
Merrily,
I'll get the ball rolling and just bite off a portion of your post by answering your questions. I've no experience with HP Data Protector. We use native SQL Server backups to disk and copy the backup files to tape.
The Recovery model dictates what kind of backup and recovery you can do. Point-in-time recovery is only possible for a database with "Full" recovery model and only if transaction log backups have been made. You can only recover from backups, not from the tlogs themselves.
If point-in-time recovery isn't needed, the recovery model should be "Simple" and only full backups taken. I think you're right to verify the need for point-in-time recovery. Sometimes, it depends on whom you talk to. One person may feel that losing a day's worth of work is okay, while another may feel differently.
There are a lot of articles on this site that may help you. Search for "backup and recovery".
Hope this helps.
Greg
Greg
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply