June 17, 2009 at 1:05 pm
Hi,
Below is the Backup schedule I have. Could you please clarify me the below questions.
Full_Backup occurs daily once at 1:00 AM.Differential Backup occurs every 5 hrs starting at 6:00 AM(6am, 11am, 4pm, 9pm) and Log Backup runs every 1 hr starting from 1:30 AM(1:30am, 2:30am, 3:30am, 4:30am, 5:30am, 6:30am, 7:30am, 8:30am, 9:30am, 10:30am, 11:30am, 12:30pm, 1:30pm, 2:30pm, 3:30pm, 4:30pm; 5:30pm, 6:30pm, 7:30pm, 8:30pm, 9:30pm, 10:30pm, 11:30pm, 12:30am).
Questions:
1.Currently, the full backup of all the databases is taking only 7 mins (i.e. jobs duration is 7 mins ) and its fine starting log backup at 1:30 AM. But in future, if the database’s size grows and the full backup takes an hour (i.e. job ends at 2:00 AM) and then is it OK to run log backup at 1:30 AM? Is this allowed in sql server 2005? What the log backup will backup while full backup is running?
2.In case of restore the database, for example,
If the full backup of one of the databases is completed at 1:45 AM but the log backup of same database, which is started at 1:30 AM, is completed at 1:40 then directly we can restore the full backup of 1:45 AM. But when the log backup completes at 1:50 AM, then do we need to restore full backup of 1:45 AM and then log backup of 1:50 AM??
3.If we want to restore a database for which the Differential backup started at 6:00 AM and completed at 6:45 AM and the Log backup started at 6:30 AM and completed at 6:50 AM
Then Restore full Backup at 1:00 AM and then restore Diff of 6:45 AM and Log backup at 6:50 AM to bring the database online right? But in the backup location, the date modified is the Backup started time or backup completed time? If it is backup started time, how can we know when the backup is completed?
Please clarify me the above.
Thanks
Ram
June 19, 2009 at 1:34 pm
any advice please....
June 19, 2009 at 2:05 pm
Run tran log backups after completion of the full.
If you are running a nightly full, there is no need to do a diff that day with your databases being so small.
Timestamps on the backups are the start times of the backup.
Restore scenario
Backup runs at 1:30am and completes at 2:30am (don't run tlog backups during full backup)
Restore full backup from 1:30 and then tlogs from after completion time of full backup.
If using a diff, then restore last full backup, most recent diff, and then any tlogs from time of diff to point in time for the restore.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 22, 2009 at 12:34 pm
Is the above backup strategy makes sense??
June 22, 2009 at 12:41 pm
If you do not experience a high volume of transactions / or are backing up to tape - then it looks like your Backup Plan makes sense.
Do a full nightly backup as you are doing, and then the hourly tran log backups.
An alternate scenario would be to do the full backup once a week with nightly diffs (will be faster in some environments rather than doing a nightly full) and then do your hourly tran log backups.
If you can back it up to a SAN / DISK, I would consider changing the tran log backups to every 15 minutes. If you backup to disk, make sure you also do a full system backup to tape.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 22, 2009 at 1:36 pm
we do weekly fulls for most databases with nightly diffs 4 times a week
small db's 1GB or less get a nightly full 4 times a week and no diffs. less management hassle
system databases get a weekly full
archive databases with R/O static data get backed up once a month or so
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply