April 16, 2010 at 6:57 pm
Hi all,
May I request feedback from the experts if my understanding of backups is correct.
If let's say I currently have the following backup in place:
Saturday - Sunday - FULL at 1200AM (midnight)
Monday - Friday - DIFFERENTIAL at 0700 and transaction log backups at 1200PM and 2200
Question for Scenario 01:
If for example on Thurs - 1100AM, I need to do a restore due to corruption, what backups do I require? Am I right to assume, that I need to restore the FULL backup of the Sunday and apply all the DIFFERENTIAL backups from Monday to Thursday? Does that mean I lost all work done from 0700 - 1100 since I do not have any transaction log backups before 1100?
Question for Scenario 02:
Same timeline, I need to do a restore on Thurs - 1100AM BUT for some reason the THURS 0700 DIFF backup had failed so there is no THURS 0700 DIFF backup. Does that mean the most recent that I can restore to is using the SUNDAY FULL backup and then the 0700 DIFF backup of the WED and apply the transaction log backups of WED until the 2200 transaction log backup? Any work done after the WED 2200 transaction log backup are lost and will have to be re-done by the users?
Any response on this question will be very much appreciated. Thanks in advance.
April 16, 2010 at 7:15 pm
#1 - You restore Sun full, Thur Diff (00:00 Thur) and logs from 00:00 Thur to failure.
#2 - You restore Sun full, Wed diff, all logs you have after that.
April 16, 2010 at 8:19 pm
Your assumptions are correct.
Scenario 1 You would restore full backup and then the Thursday differential (7am). Since you only do tran logs twice a day - you would lose any work after 7am.
Scenario two is just as Steve said.
I would recommend more frequent tran log backups.
Here is a reference for diff restores.
http://msdn.microsoft.com/en-us/library/ms175510.aspx
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
April 16, 2010 at 10:32 pm
CirquedeSQLeil (4/16/2010)
I would recommend more frequent tran log backups.
Here is a reference for diff restores.
I agree. For production, I backup every 15 minutes
April 17, 2010 at 1:29 am
Hi Steve, Jason,
Are you guys do the Full/Diff/log backups to local server or backup to a Network Share?
If you backup local to the server, then if the server goes down & unable to bring online then we have NO backups to restore right?
If you backup local to the server and then backup to TAPE, then
How you backup the Differential backup files occurring every 4 hrs & Transaction log backups occurring every 15 mins to Tape from local server? Is this required.
At our organization we are doing the below:
1. Full backup every day at 1 AM and it will finish at 2:00 AM and the Tape backup runs at 3:00 AM (Basically copying the full back files (.bak) to TAPE)
2. Every 4 hrs differential and every 15 mins log backup. But these backups did not go to tape until 3:00AM. If anything happen in the middle of the day, and the server will NOT be able to come online then we do NOT have these diff/log backups in TAPE. So we can only restore to last night full backup taken at 1:00 AM from the TAPE.
My question is do you guys backup the diff & log backups to TAPE during the day all the time as the backups occur every 4 hr & every 15 mins to TAPE?
How you deal in above scenario?
Ie backup diff & log to Network, then even if the server goes offline, then we can restore the database to last log backup right?
Considering above, would it be Best practice to Backup to Local drive or Network?
Thanks
April 17, 2010 at 8:57 am
pshaship (4/17/2010)
Hi Steve, Jason,Are you guys do the Full/Diff/log backups to local server or backup to a Network Share?
If you backup local to the server, then if the server goes down & unable to bring online then we have NO backups to restore right?
If you backup local to the server and then backup to TAPE, then
How you backup the Differential backup files occurring every 4 hrs & Transaction log backups occurring every 15 mins to Tape from local server? Is this required.
At our organization we are doing the below:
1. Full backup every day at 1 AM and it will finish at 2:00 AM and the Tape backup runs at 3:00 AM (Basically copying the full back files (.bak) to TAPE)
2. Every 4 hrs differential and every 15 mins log backup. But these backups did not go to tape until 3:00AM. If anything happen in the middle of the day, and the server will NOT be able to come online then we do NOT have these diff/log backups in TAPE. So we can only restore to last night full backup taken at 1:00 AM from the TAPE.
My question is do you guys backup the diff & log backups to TAPE during the day all the time as the backups occur every 4 hr & every 15 mins to TAPE?
How you deal in above scenario?
Ie backup diff & log to Network, then even if the server goes offline, then we can restore the database to last log backup right?
Considering above, would it be Best practice to Backup to Local drive or Network?
Thanks
4hrs differential seems too often to me. True if you backup to local disk and you lose multiple disks on that server then you lose your backups. However, if you backup to SAN you are more protected. Yes, I have the backups on disk also written to tape. Some places the backup was straight to tape. Some places we backed up to disk, then copied that backup to a network share, and then backed up 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
April 17, 2010 at 9:36 am
Couple things I've done.
1. Back up locally. SQL Server does not tolerate network delays and it's not worth having a backup fail.
2. Do not use the same set of disks (physical) for backups and data/logs. Have separate disks.
3. After the backup completes, copy to a network location that the tape drive can see.
April 17, 2010 at 2:22 pm
Steve has the correct solution here!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 19, 2010 at 6:31 am
I'm 100% with Steve. Backup locally, to a seperate drive from any logs or data, and then copy to tape or network shares as appropriate.
In our case, we're on a SAN, so the "local" drive is anything but, however, the same basic approach applies. Trying to transfer a backup across the network in real time can cause serious issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 19, 2010 at 9:44 am
Grant Fritchey (4/19/2010)
Trying to transfer a backup across the network in real time can cause serious issues.
Agreed - network delays can increase backup times as well as failures.
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
April 21, 2010 at 6:40 am
I would suggest you also add to your “restore” process a note about attempting a tail-log backup before you start your restore process. If your recovery model is full and if you disaster is not entire server failure and is for a specific database, then assuming you can back up the active log after a disaster occurs, you can restore the database up to the point of failure without data loss. There are many good articles on this, Books On-Line is one of them, see http://msdn.microsoft.com/en-us/library/ms190217.aspx. There are also some good articles explain how a transaction log could be moved to another server and database to issue the final tail-log backup. See http://sqlblog.com/blogs article by Tibor Karaszi on “Restore database to the point of disaster” … section "d) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there. The installation is toast - we can't start SQL Server."
April 21, 2010 at 9:56 am
Heh, I just came across this question somewhere else. The only difference was that the database disks were malfunctioning and you needed to recover the database. The accepted answer stated that a tail log backup had to be done in order to recover the database. Only problem is that the database is offline and the tail-log backup can't be performed.
A tail log backup is appropriate in some scenarios but not all.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply