February 8, 2010 at 8:57 pm
We are currently using MS SQL Sever 2000, and our database model is in Simple Recovery. We have a stable backup and restore maintenance. Full backup every Sunday night, and nightly Differential from from Monday to Saturday. Then the backup chain is restored to our standby server every after completion of the backup chain. This schedule is stable and doing good since it started until now. But my problem now is that our database has grown and our Full backup has already reached 120Gig.
I am planning to change our recovery model from simple to full, and change our backup and restore maintenance. Please help me on what backup and restore maintenance I can shift to from Full and Differential backups.
Can I consider transaction log backups? Can i also restore it to our standby database? How?
Thank you!
February 8, 2010 at 9:48 pm
If you change to full recovery, you have to be running log backups.
Please read through this - Managing Transaction Logs[/url]
What's your reason for switching from Simple to Full? It's not going to decrease the size of your backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2010 at 12:08 am
I want to switch from simple to full recovery because of the length of time of restore it takes when restoring full and differential backup everyday. am i on the right track?
February 9, 2010 at 12:24 am
Maybe, but you should test. The time for restoring the full won't change. Whether the restoring of all the tran logs is faster or slower than a single diff, that you'll have to test.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2010 at 4:50 pm
I'm a big believer in keeping production databases in the full recovery mode, and then performing nightly fully backups, and hourly (or more often) transaction log backups. If you are having a problem with backups being too big, or backups taking too long, consider a third-party backup compression tool, such as SQL Backup. Using such a tool can reduce backup sizes and times as much as 80% or more (depending on the type of date stored in the database).
Brad M. McGehee
DBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply