Going back in time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Going back in time

Author

Topic

qqi
Starting Member

5 Posts

Posted - 2011-03-10 : 22:09:33

I have

1. Full-Backup-A
2. Transaction-Log-Backup-A
3. Transaction-Log-Backup-B
(*) - I need to restore this point
4. Full-Backup-B

How to do it? It seems that the only way is

1. Full-Backup-A
2. Transaction-Log-Backup-A
3. Transaction-Log-Backup-B
4. Shut-off client access
5. Transaction-Log-C
6. Full-Backup-B
7. Allow client access

If I actually have to do it this way it seems too idiotic even for Microsoft - so I am sure there is a less dramatic way to achieve this goal, all I need is to find somebody who knows it.

Thanks, Alex

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-11 : 03:59:38

What you need:

1. Full-Backup-A
2. Transaction-Log-Backup-A
3. Transaction-Log-Backup-B
(*) - I need to restore this point
4. Transaction-Log-Backup-C

Restore Full-Backup A, TranslogA and B with NORECOVERY. Then restore translog C with RECOVERY and STOPAT with your point in time.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-11 : 04:01:32

If it's not possible to do translog backup C then you will loose whichever changes were made since translog backup B.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

qqi
Starting Member

5 Posts

Posted - 2011-03-11 : 06:29:25

> If it's not possible to do translog backup C then you will loose whichever changes were made since translog backup B.

Thanks, it seems that I have to rephrase my question:

Are there ways to guarantee (beyond shutting off client access) that the database would not change between transaction-log-backup-C and full-backup-B?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-11 : 08:28:34

The short (and only) answer to your question is no.

But I'm more interested in knowing why you're asking? As long as you do regular full and translog backups there will be no holes in your backups. The amount of data loss you will have to accept is decided by the frequency of your transaction log backups. By doing translog backups every 15 mins you are implicitly saying that you are willing to loose a maximum of 15 minutes worth of data.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-11 : 08:36:53

Not really. You can eventually do a log tail backup and not lose a nanosecond.

quote:
Originally posted by Lumbago

What you need:

What you really need is a DeLorean going 88.8 mph.

N 56°04'39.26"
E 12°55'05.63"

qqi
Starting Member

5 Posts

Posted - 2011-03-11 : 20:19:31

It seems that all I need is no-Microsoft in production environment.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-13 : 09:01:40

Yeah, that's probably it. Just blame all the stuff you're incapable of understanding on MS and you'll do fine.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-13 : 10:12:16

I agree. The flux capacitor is probably easier to understand than point in time recovery...
http://msdn.microsoft.com/en-us/library/ms191468.aspx

N 56°04'39.26"
E 12°55'05.63"

qqi
Starting Member

5 Posts

Posted - 2011-03-13 : 15:38:17

I am new to all things Microsoft (beyond generic computer/c-compiler thing). At first, I could not believe that it even Microsoft would have an unavoidable UNRECOVERABLE period no matter what backup plan is being used.

Now, I see that it is just a very popular mindset - happy kool-aid drinking, guys.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-14 : 04:44:29

A thinking exercise would probably be useful before asking questions like this. There are basically two options, now which of the two are the most likely:

1. One of the most widely implemented database engines in the world has no ways to back up all data
2. I must be missing out on something

Choosing #1 says quite a bit about your reasoning abilities.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

qqi
Starting Member

5 Posts

Posted - 2011-03-14 : 07:22:55

1. It happen that for that last 20 years I worked on several (3 to be exact) "most widely" used software products with non-stop/high-availability requirements (not in the database area though) and spotting issues like this takes some experience in this area, indeed.

2. Oracle does not have this gap.

Thanks, for discussion, keep drinking your kool-aid.

quote:
Originally posted by Lumbago

A thinking exercise would probably be useful before asking questions like this. There are basically two options, now which of the two are the most likely:

1. One of the most widely implemented database engines in the world has no ways to back up all data
2. I must be missing out on something

Choosing #1 says quite a bit about your reasoning abilities.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-14 : 07:37:40

Are you still not getting it? Let me say it as clear as I can:

*There is no gap*

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-15 : 03:45:50

Maybe he means that the database is not reachable/workable during the restore process?

N 56°04'39.26"
E 12°55'05.63"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-15 : 04:24:27

quote:
Originally posted by Peso

Maybe he means that the database is not reachable/workable during the restore process?

I have no idea...and to be honest I really don't care anymore. Some people are just too full of themselves to be able to see that things might actually be different from what they think.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-15 : 04:28:48

quote:
Originally posted by qqi

2. Oracle does not have this gap.

Neither does SQL Server.

If you want to restore to exactly 11:01:52 then you can do so providing you have log backups covering that period. The presence (in your example) of the second full backup is completely immaterial as long as you have log backups covering the entire period from a full backup (or diff) to the point that you want to restore to

quote:
Thanks, for discussion, keep drinking your kool-aid.

No thanks, I prefer coffee.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 14:32:20

quote:
Originally posted by qqi
1. It happen that for that last 20 years I worked on several (3 to be exact) "most widely" used software products with non-stop/high-availability requirements (not in the database area though) and spotting issues like this takes some experience in this area, indeed.

2. Oracle does not have this gap.

Thanks, for discussion, keep drinking your kool-aid.

[giggle]Only? 20 Years?[/giggle]

Let's see

SELECT DATEADD(yyyy,-20,GetDate())

Put's you somewhere in the space-time continuum where M$ and IBM where still friends

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources