Red Gate forums :: View topic - Transaction Log Restores
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Transaction Log Restores

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
vanderetto



Joined: 26 Jun 2013
Posts: 5

PostPosted: Wed Jun 26, 2013 12:44 pm    Post subject: Transaction Log Restores Reply with quote

Hi All,

I currently have the following backup schedule for a selected database:

Weekly FULL, Daily DIFF & Hourly LOGS

I was under the impression that any FULL backup that was done on the database without the "COPY_ONLY" option selected, would result in the backup chain being broken and would potentially impact any subsequent restore operations.

However, I have discovered that this does not appear to be the case.

For example, I created a "rogue" full backup (using SSMS) in between my hourly RedGate Transaction Logs, thinking that any attempt to restore subsequent Transaction Logs would fail, but the restore procedure does not complain about the fact I have this "rogue" backup and allows me to restore the full set of previous logs, differential and full backups.

So, my question is how does the restore not complain about the fact that a full backup in the middle of my Trans Log schedule has been done without the COPY_ONLY option?

Hope this makes sense.

Thanks,
Bob
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Thu Jun 27, 2013 7:46 am    Post subject: Reply with quote

Quote:
So, my question is how does the restore not complain about the fact that a full backup in the middle of my Trans Log schedule has been done without the COPY_ONLY option?

Any problems pertaining to backup 'rules' will be raised by SQL Server during the backup process, and reported by SQL Backup. SQL Backup by itself does not perform any validation of the 'rules'. E.g. if your database is running the simple recovery model and you attempt to back up that database's transaction log using SQL Backup, it is SQL Server that raises the error, and SQL Backup simply reports it.

In your example, there is nothing wrong with taking a full database backup. Neither SQL Server nor SQL Backup can ever know that only full backups taken by your scheduled jobs are considered 'valid', and other other backups should raise an error during the restore process.

If you need to know when full backups have been made by other users, you can refer to the backup history tables, as described here.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
vanderetto



Joined: 26 Jun 2013
Posts: 5

PostPosted: Thu Jun 27, 2013 1:54 pm    Post subject: Reply with quote

Hi Peter,

Thanks for taking the time to reply.

Reading back my post, I don't think I explained particularly well what my query is - I'll try and clarify a bit better.

Let's say I have the following backup files (all created by RedGate):

Full_1
Log_1
Log_2
Log_3
Diff_1
Log_4
Log_5
Log_6
Full_2
Log_7
Log_8
Log_9
Diff_2
Log_10
Log_11

If I wanted to restore up to (and including) Log_6, I would restore Full_1, Diff_1, Log_4, Log_5 & Log_6. Pretty straightforward.

However, let's say that unknown to me, someone had taken a full backup of the database (via SSMS) after Log_4. Surely in this case, the "log chain" will have been broken, and my attempt at restoring using the same backup set would fail?

Hope that makes sense.


Thanks,
Bob
Back to top
View user's profile Send private message
vanderetto



Joined: 26 Jun 2013
Posts: 5

PostPosted: Thu Jun 27, 2013 2:32 pm    Post subject: Reply with quote

Just done some further reading on this and discovered that my understanding of my restore scenario is wrong.

So, unless I'm mistaken, I could still use the same backup set for the restore without any problems.

Embarassed
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Thu Jun 27, 2013 3:32 pm    Post subject: Reply with quote

Yes, you can still restore up to Log_6 using the same sequence you described earlier, or you could restore using that 'other' backup and subsequently just Log_5 and Log_6.

A full backup affects only the base from which your differential backups need to restore from. E.g. if a full backup was taken between Log_2 and Log_3, then Diff_1 would require that you restore that full backup first. Full_1 would be rendered redundant for Diff_1, unless that other full backup was made using the COPY_ONLY option.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
vanderetto



Joined: 26 Jun 2013
Posts: 5

PostPosted: Thu Jun 27, 2013 5:09 pm    Post subject: Reply with quote

Thanks Peter.

Makes perfect sense now.


Regards,
Bob
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group