May 17, 2010 at 8:06 pm
Dear All:
We have two production databases running on SQL 2005 server. I made the following backup plan and looking for feedback. :
for each instance I created the following maintenance plans:
1. Full Weekly backup starts every Monday morning at 1:00 AM. ( Append to media)
2. Differential daily backup runs every day from monday - Friday at 5:00 PM ( Overwrite if exist)
3. Transaction Log backups twice daily at 12:00 PM and 5:00 PM
these backups are made on SQL server on a separate volume.
4. I Scheduled tasks on SQL server to copy backup files to network share, 10 minutes after each backup is finished.
5. FTP encrypted backup files to our ISP ( our ISP provides remote backup service).
on the other hand I am planning to configure database mirroring with high protection mode.
May 17, 2010 at 8:42 pm
It sounds good overall. I don't ever append to files. If the file corrupts, especially with fulls, you're dead. Overwrite all the time, IMHO.
The thing to think about is recovery. The way you have it now, if the volume with data/logs fails at 4:30pm, you are restoring a full, a diff, and a 12pm log, but if you can't get the tail of the prod log, you're out 4.5 hours of work. If that's OK, run with what you have. Think about a failure at 11:30am as well. How far can you recover? Is 5pm the previous day ok?
Fulls provide safety, diffs speed recovery, logs get you less data lost. I have worked in a similar scenario, but I have usually run logs every hour just in case. The other thing to think about is that if you lose a diff, do you have the logs since the full? I might keep those around, or schedule a full halfway through the week to reduce your exposure.
The other thing I'd do is make sure the backups if copied off the machine, and the new one is complete BEFORE I clean up old files.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 17, 2010 at 11:50 pm
What sizes are the databases ?
How critical are the databases ? For my prod dbs, I backup logs every 15 min or so.
How much space do you have to store backups ?
May 18, 2010 at 1:11 am
one is about 300 mb and the other one is about 500 mb . the second one is growing at a faster rate. in terms of local space it is not a problem. the remote backup only allows 10 gig
regards
May 18, 2010 at 7:12 am
Are you a 8 hour a day company? Does business get done all day/night long? Those are good sized dbs, but not huge.
Also, is that data size, or database size? Meaning how large are your full backups?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 18, 2010 at 9:36 am
Sounds like the constraint is your backup location. 10 G is pretty tiny these days.
You could copy your backups to a tape rotation so current backups exist in 2 locations. You could also use a 3rd party tool to get compressed backups and fit more in your 10 G space.
May 19, 2010 at 2:50 am
I suggest u run the full backup on saturday\sunday instead of monday 01:00am , if some thing goes wrong u can rectify before monday business hours start
May 20, 2010 at 7:33 pm
Steve Jones - Editor (5/18/2010)
Are you a 8 hour a day company? Does business get done all day/night long? Those are good sized dbs, but not huge.Also, is that data size, or database size? Meaning how large are your full backups?
We are a school ( 8:30 - 4:30) the full backups the full backup currently is about 247 kb for the first and 450 kb for the other database.
regards
May 20, 2010 at 8:56 pm
I am confused don't know what is the problem. I am doing some restore tests in a testing environment. so I had the following backup files:
1. Full on the 17/5 at 10:30 AM
2. Differential on 17/5 at 5:00 PM, 18/5 at 5:00 PM, 20 at 7:00 AM and 21/5 at 7:00 AM
reason for different time is renew of backup strategy.
3. also there was some log files backups from 17/5 to 21/5.
so I did the following
1. I restored the full backup to the Test SQL server with nonrecovery option
2. Then attempted to restore the last differential backup which was taken on 21/5 at 7:00 AM
at this stage i receive the following error message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'MYSERVER'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Not sure where am I going wrong, my understanding is in Full mode the restore order is :
the latest full backup
then the last differential taken after that
followed by all transaction logs taken after that I hope I am right 🙂
May 21, 2010 at 4:11 am
It sounds that your logic is correct. Could there be a more recent Full backup ? Maybe a scheduled Maint plan you forgot about.
May 21, 2010 at 5:36 am
There was a maintenance plan doing full backups, which I disabled since the 14/5, that when I changed my recovery mode from Simple to full. and then I created new maintenance plans as a explained earlier.
but I cant be 100% sure that is the case I will double check. but let us say there is no full backup taken before that, then what is the way around this problem.
regards
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply