SQL Server - Requesting Backup / Restore Theory Explanation

Asked By IT_Architec

24-Oct-08 09:12 PM

MSSQL backups are odd in that the data and transaction logs are two different
backup operations it seems.  Normally, when a backup is performed, the data
and log file can be archived at exactly the same time, and the data file in
the live area is the same as the archived one except there are no
transactions in the transaction log.  If a database must be restored, the
transaction file in the live area or its mirror is exported to an SQL file,
and the previous backup data and transaction file is moved to the live area,
and the SQL file is run against the restored database resulting in zero
potential for data loss.  The SQL file can even be edited prior if necessary
to remove an oops like an accidental truncate table etc.

MSSQL doesn't appear to be able to synchronize the cut-off between the data
and transaction file.  Is there a work-around for MSSQL that can effectively
accomplish the same thing even if it is a little more clumsy?  I'm trying to
put together some TSQL for MSSQL 2008 Express for backup, restore to last
backup, and restore from failure using a roll-forward from last backup.

There is no clear explanation of the backup theory in the docs.  It talks
about simple, full, restoring the data, and tails, but no explanation on how
they work together nor what the non-standard term tails exactly is.

Thanks!

24-Oct-08 09:57 PM

I am sure that the backup and restore rules for whatever other RDBMS
products you are used to would look as odd to me as SQL Server's do to
you.

Database backups give you the database as of around the time that the
backup finishes.  (Actually I think it is as of the start of the
oldest open transaction in the log when the backup finishes.)  If the
database is in full or bulk-logged recovery mode then log backups
taken after the database backup can be applied either completely or up
to a point in time.  There is no provision for inspecting them to find
the exact moment you want to avoid reaching, nor is there any way to
edit them to prevent a change from happening.

If the "tail" is intact that should be backed up before the restore.
While the term is a bit obscure, using the search feature of Books
Online shows a section titled "Tail-Log Backups" that goes into this
pretty thoroughly, and seems reasonably clear (to me at least).

As for synchronizing the cut-off between the database backup and log
backup, there is no particular need to do that.  As long as log
integrity is maintained, and all the log backups are available, you
can start with any database backup and apply all the subsequent log
backups up to the end or up to any particular point in time.  So if
you found that last night's DB backup was damaged, if you have the one
from two days ago plus all the log backups since then you can still
restore and roll forward.  Without log backups you get the database as
of the time of the backup.

I am looking at the SQL Server 2008 Books Online (August 2008).  In it
I found a section titled Backing Up and Restoring Databases in SQL
Server, which has links to the topics listed below.  Perhaps you have
already found all this and found it wanting, or maybe you had only
found the documentation of the commands themselves and will find some
new material here.

Backup Overview (SQL Server)
Backup Under the Simple Recovery Model
Backup Under the Full Recovery Model
Backup Under the Bulk-Logged Recovery Model
Introduction to Backup and Restore Strategies in SQL Server
Creating Full and Differential Backups of a SQL Server Database
Working with Transaction Log Backups
Copy-Only Backups
Working with Backup Media in SQL Server
Security Considerations for Backup and Restore
Restore and Recovery Overview (SQL Server)
Implementing Restore Scenarios for SQL Server Databases
Working with Restore Sequences for SQL Server Databases
Considerations for Backing Up and Restoring System Databases
Using Marked Transactions (Full Recovery Model)
Optimizing Backup and Restore Performance in SQL Server
Understanding Recovery Performance in SQL Server
Backup and Restore in Large Mission-Critical Environments
Backup and Restore APIs for Independent Software Vendors

I hope this helps.

Roy Harvey
Beacon Falls, CT

On Fri, 24 Oct 2008 18:12:01 -0700, IT_Architect

25-Oct-08 12:12 AM

Hi Roy,

That's a good discussion of backup and restore.  The one correction I would
make is that while you can do point in time restores to some point in the
middle of a log backup if you are in full recovery mode, if you are in
bulk-logged recovery mode, you may or may not be able to restore to a point
in time.  If any bulk logged operations were performed during the time
covered by that log backup, then you cannot do point in time restores to a
point of time in the middle of the period covered by that log backup.

Tom

25-Oct-08 05:39 AM

I believe that Row explained it well (of course). Just a couple of additional thoughts which might
or might nor be of help:

It seems like some other product will automatically "connect" to the live log when you do restore,
so you can achieve zero data loss. SQL Server doesn't do that. You need to produce a log backup from
your active log before you perform the restore - else you will loose whatever was in there (since
your restore if done into the same database will now have overwritten what was in the ldf file).
If you lose that SQL Server instance but the log file is still there, you can just take a machine
with a working SQL Server instance and create a (dummy) database, stop that SLQ server, delete the
files and then copy your ldf file from the production database in place and then product a log
backup of what was in there (using the NO_TRUNCATE option of course).


As for how restore work, here one way of looking at it:
Fact: All backups contains log records. A log backup contains only of log records (more later on
bulk-logged recovery). The different types of database backup contain the log records that was
produced while the backup was running - in addition to data pages.

The restore command can perform several things:
Delete the existing database. This happens if you specify the REPLACE option.
Create the existing database. This happens if the database name you specify in the RESTORE command
doesn't exist.
Copy data pages to the same address in each database file as they were when the backup was produced.
And of course also copy log records to ldf file. Source for these copy operations is the backup file
that you restore from.
REDO. Roll forward transactions based on the log records copied in above step.
UNDO. Rollback all open transaction. Not performed if you specify NORECOVERY (more later on STANDBY
option). Database is now accessible, and no further backups can be restored (diff or log backups).
A couple of special cases:

If you are in bulk-logged recovery model, then a log backup performed if you did minimally logged
operations since last log backup will contain also data pages (in addition to log records). This
type of backup cannot be performed is any of the data files are lost. When you restore from this
type of backup, you cannot do point-in-time restore.

The STANDBY option of the RESTORE command does perform UNDO but saves that work to a file you
specify. This so that the UNDO work can be undone when you restore a subsequent backup.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

25-Oct-08 07:11 AM

Good point.  Thanks for filling in on my omission.

Roy Harvey
Beacon Falls, CT

25-Oct-08 09:24 PM

log when you do restore, so you can achieve zero data loss. SQL Server
doesn't do that.<

Many products work that way, the automatic part depends on which product.
For those that use the manual method, what happens is when you do a backup,
the data and log files from the current live area are replicated to the
backup area.  When finished, the log file in the live area is truncated.  At
that point, both data files are exactly the same.  Let's say somebody does a
truncate table or messes up a WHERE clause by accident or a program has an
obscure bug that all of the sudden triggered.  You would halt the db, copy
off the live log or its mirror, copy in your previous db and log, convert the
binary log file to SQL, search backwards in the log file for where things
went awry, delete that statement(s), save the file, and use it to roll
forward without the bad statements.  If you simply somehow just lost the
data, you could restore with the previous backup and current log or log
mirror without the editing.  This allows you to roll backwards to any point,
and roll forward from any point.  Some automate this with real-time visible
scrolling while handling what needs to happen automatically.


After doing some reading, that’s the clean cut-off I’ve been looking for,
and the transaction log backup I was doing immediately afterward was an
“exercise”.


That makes sense, and would be the case with any DBMS, and it appears this
is what is meant by the term “tail”.


This mitigates some of not being able go to a specific transaction if you
know with some degree of certainty when the problem occurred.  Now STOPAT
makes sense.  So at least you could pick a point in time from where they
would need to re-enter transactions.  You could in fact make multiple stabs
at it to find the closest time, you just couldn’t roll forward transactions
after that.

Summary:
Unless you have a lot of records, you can simply do a full backup every
night.  If anything happens during the day, you can save off the current
transaction file, do a restore, and then run the transaction file you saved
off.  If you have more data, you have differential and log backups available
to you.

Other:
Every DB has its pros and cons.  MSSQL has the best query optimizer of any
DB at any price, which is the attraction for me.  I had been watching it for
usability since version 6, and I’ve been using it simpler projects since it
started making sense, which for me, didn’t occur until version 2000.  I’ve
just been doing normal backups and restores through the 2005 version for
simpler projects.  When 2008 came out, MSSQL (and ASE at the same time) added
the types I'd wanted for ages.  I had to redo the backup SQL anyway, so I
decided it was time I gained a better understanding of its recovery
capabilities.  This thread has been very helpful to that end.

Thank you all!

26-Oct-08 04:33 AM

It is interesting to read and learn how other products do these type
of things. Come comments, one which I believe you want to read
carefully (look for IMPORTANT in text below):



Cool. This process is not that simple with SQL Server. It requires
first doing a log backup (what sometimes is referred to a tail log
backup) and then using some 3:rd party tool to decipher the log
backups and from that find the datetime or lsn (log sequence number)
which you then use with STOPAT when you do the restore of that log
backup.



That past point I have an article about. Basically, say you don't want
to buy a log reader product and you want to try to find where the
same log backup forwarding the clock each time with as small
increments as you wish. As soon as you have passed the accident you
have found the restore time you want to use. See
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp.



IMPORTANT:

You lose me here. You need to tie in recovery model with your backup
strategy. If you are in simple recovery model, then you cannot use the
log for any type of restore points. Your database backups (and
possibly diff) are all you have to work with. If you are in full
recovery model and don't do log backup, then your ldf file will never
be emptied (re-used) and grow until it eats your whole disk.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

26-Oct-08 12:24 PM

log backup) and then using some 3:rd party tool to decipher the log backups
and from that find the datetime or lsn (log sequence number) which you then
use with STOPAT when you do the restore of that log backup.<

Got it!


It seems I'm the one that got lost here.  Assuming the full recovery mode, I
thought that the when you do the backup, that since I now have the data, that
the log in the live area was cleared to that point.  Thus, it wouldn't grow
in size because the space would get reused for the next day's transactions.

What I think I hear you saying is, I don't have the cut-off like I think I
had because the log, if not also backed up, now contains what got logged for
the backed up copy plus the changes that occurred since in the live area
until I do a backup of the log file.  It means that if I backup the log file
right after the data file, the tail is missing transactions that occurred
between the time the data file backup finished and the transaction file
backup finished.  The missing transactions would be in the backup log.  To
restore, I would need to do a tail log backup, restore the data, restore the
log, and then restore the tail log, and that there is a mechanism that
insures the transactions in the backup transaction log are not posted twice,
only the ones that are were not posted to the data file that occurred between
the data file backup and transaction file backup.  So the entire scope of the
data in the backup this way would be from the first record in the data backup
to the last record in the log backup, plus the tail if you wanted to apply
it? (To recover from an oops, you would either not want to apply the tail or
only apply part of it?)

What I have currently is inside of a program they can click backup database
button.  It backs up the database and log at the end of their working day.
(I thought I no longer needed to backup the log file but apparently I do.)  I
also have a restore button, which restores the previous database and log.
I'm deleting the tail because what is in it, I don't want posted back since
I'm essentially doing a rollback.  While it appears to work fine in testing,
it may be based on a false premise that doesn't always work right.  Ideally,
during a restore, I would like to also like to retain the most recent tail
log so I COULD use it if necessary.  What would be the best strategy to do
that, and is my thinking correct now on the relationships of db backups and
logs?

Thanks tons for your help!

26-Oct-08 01:28 PM

See inline, below:

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



No. A database backup do *not* "empty" the log. In other words, a
datababse backup do not affect the log backup chain. Say you need to
do a restore, but you find that your most recent database backup is
missing or corrupt. No problems, just restore the prior database
backup and then an unbroken chain of log backups - up to current point
in time if you wish.



Yes, SQL Server can roll-forward a log record several times and it is
only applied once. This is what allow for my scenario where you
re-restore the same log backup several times and just push the time
slightly forward a little between each restore.
In other words, you do have duplication of log records between
database backup and the following log backup, but that isn't anything
SQL Server have to worry about. There's a fancy name for being able to
REDO the same log records without applying the logical modification
several times - you'll find it in Jim Gray and Andreas Reuters



Correct. Also, there is nothing special about the "tail" backup. This
is just a regular log backup. I'm no fan of how MS uses this word in
recent releases of the product since it perhaps tries to make
something more user-friendly but the cost is muddles the technolify
behind it. Think of it this way: "Hey - don't forget to do that last
tail log backup before you restore!". That log backup is as regular as
any log backup. Say you change your mind and don't do a restore - what
would make this log backup a "tail"-log backup?



I think you got it, but hard to say. You might want to make sure you
have read below, just to verify your understanding and post back if
furher thoughts.
http://msdn.microsoft.com/en-us/library/ms345583.aspx
Make sure you read all the subsections. If your requirement is to
being able to restore to finer granularity than database backup
(disregarding diff backup here), then make sure you also do log
backups. I typically schedule my backups say db every day and log
every 15 minutes. If your situation is manual backups by end-user,
then perhaps do db backup immediately followed by log backup?

27-Oct-08 06:35 AM

regular log backup. I'm no fan of how MS uses this word...muddles the
technoligy behind it. <

Got it!  I work with a lot of different DBMSs of which Oracle is one.  I
tell people if Oracle sold cars, they would advertise them as pneumatically
suspended personal transporters.


I think what I have is enough of the concept to where the docs will now make
sense to me.  The concepts I see are:
1.  There is no cut-off relationship maintained between db backups and log
backups.
2.  Normally, when you do a log backup, you truncate the live one which
keeps it from growing too large.
3.  You need every log backup that occurs after a db backup, including the
current log, in order to restore to present.
4.  Log transactions that overlap the db backup will not double-post due to
lack of synchronization with the db because there is sufficient information
in the db and log to determine the starting point for unposted transactions.

Now I'll read the docs and come back to bounce a plan off you.

27-Oct-08 10:42 AM

The docs now make sense to me.  I would need to code a simple backup
something like this:

To Baclkup from program:
! *** Remove old files using language:
! *** Connect to DBMS
! *** Set Current Database
USE MyDatabase
! *** Remove Old Backup devices in the event their location changed in the
ini file.
EXEC sp_dropdevice BackupDBDevice
EXEC sp_dropdevice BackupLOGDevice
! *** Create New Backup devices with current ini information
EXEC sp_addumpdevice 'disk', 'BackupDBDevice',
'\\MyServer\MyShare\MyDatabaseData.dat'
EXEC sp_addumpdevice 'disk', 'BackupLOGDevice',
'\\MyServer\MyShare\MyDatabaseLOG.ldf'
! *** Backup files
BACKUP DATABASE MyDatabase TO BackupDBDevice
BACKUP BACKUP LOG MyDatabase TO BackupLogDevice

To restore from program:
! *** Remove old Tail Log using language:
! *** Backup latest Tail Log
EXEC sp_dropdevice BackupTailLOGDevice
EXEC sp_addumpdevice 'disk', 'BackupTailLogDevice',
'\\MyServer\MyShare\MyDatabaseTail.ldf'
BACKUP LOG MyDatabase TO BackupTailLogDevice

! *** Restore Database
RESTORE DATABASE MyDatabase FROM BackupDBDevice WITH REPLACE
RESTORE LOG MyDatabase FROM BackupLogDevice WITH REPLACE
*Optionally, restore the tail log.

I read about the LSN and how it works.  I mapped out a plan of how MS or
some enterprising 3rd party could solve this problem.  After I did, I found
out some enterprising 3rd party already had.
http://www.red-gate.com/products/SQL_Log_Rescue/walkthrough.htm.  The cost of
the product is chump-change for what it does for MSSQL.  MS should buy the
company.  If I hadn't found this product, you probably wouldn't have heard
from me for awhile because I didn't want to hurt anybody's feelings.  Without
the recovery capability that RedGate brings, I couldn't entertain using MSSQL
in any game that matters.  The DB doesn't need to be perfect, and I wish the
triggers were more flexible, but this type of recovery capability to me is as
basic as you need skates to play hockey.  In a big company, a lot of
information gets changed and added the database every minute.  When a
catastrophe strikes, I need to be able to change the apps password, and cut
their connections so they go splat like bugs on a windshield when they try to
reconnect, and be able to quickly resolve the issue without losing data.

The real reason for my level of interest is I've been CIO at 2 companies and
have written 2 ERP programs for automotive suppliers that are fully
integrated including QS 9000, quality, shop floor, EDI, and coil management.
One company has seen the huge mistake they made by buying the number one rate
product rather than what the saw from me.  Some people of that company
recognized that and when they started their own company, they wanted me to
write one for them, and now I'm getting the request again from another who is
also dissatisfied with their decision.  (I was in a couple of magazines)  I
need to make a DB decision, and decided to look at MSSQL again because of
their competent query optimizer.  2008 has added some sorely needed data
types and improvements.  If the RedGate product is real, it seals the deal
for me.  I can always add sophistication and redundancy to a backup plan as
long it has the basic recovery capabilities, and when combined with the
RedGate product, it appears to have what I need.

If you look at the RedGate product, they don't explain what they mean by the
warnings and things that need to be handled manually.  Here's the deal.  In a
larger company with sophisticated apps, you will get these.  These problems
were created by decisions made by programs because of the disaster.  E.G.
lets say someone does a truncate table on you or wipes the rows of needed
information.  A lot of well-written apps will post exceptions and create rows
where needed if the basic information can be calculated from other sources in
order to heal itself and keep the ball rolling.  However, when you do the
restore, the log will attempt to insert those auto-created rows into the DB
AFTER they've already been restored.  In a manual system they simply don't
post and give you an error log.  In an automated system, you can see what
they are ahead of time and resolve them them manually.  In most cases you
just don't let the subsequent ones restore, so even the simple system isn't
that bad.  You still have a record of what went haywire if you need to fix
it.  The RedGate method is the common major league method of dealing with
this issue.

Thanks HUGE for your help!

27-Oct-08 11:03 AM

I'm glad you're making progress... Some comments:

Did you consider skipping using (pre-defined) backup devices?
Nowadays, many of us backup directly to disk, like:

BACKUP DATABASE mydb TO DISK = 'C:\mydb.bak'

Above allow for constructing a filename (in a variable) that contain
date and time so that each backup produces a new file (see
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp for
example). It then allow for removing old backups but keeping n
generation of backup files. This is, btw, how a Maintenance Plan does
it. Sure, you can in your code dynamically create backup devices
(sp_addumpdevice) and drop them, but then what is the point of using
pre-defined backup devices in the first place?
I can see your point in re-using the same device names (and file
names), but how do you handle generations? Append/overwrite is simple
using NOINIT/INIT, but they are an all-or-nothing approach.
Don't get me wrong here. I'm not saying how you should do it, but more
like mentioning some things/thoughts with which you can challenge your
own strategy.



Also, I recommend using BAK as extension for database backups. And
either BAK or TRN for log backups. Do *not* use ldf since the format
of a log backup file is not the same thing as the format of the active
transaction log. It will work, but somebody will be confused at some
point and do something really stupid and unexpected (and this will be
the most inconvenient point in time and location).

I can certainly see your point regarding SQL Server not shipping with
a log reader tool. I couldn't agree more! I've been wanting such for
over a decade now, but apparently MS have had other things with higher
priority so far. Consider venting your voice (with as much "business
power"/case as possible) at connect.microsoft.com. There should be at
least one connect item for this already...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

27-Oct-08 02:11 PM

On Mon, 27 Oct 2008 07:42:01 -0700, IT_Architect


Take another look.  My reading of the Redgate web site is that SQL Log
Rescue works with SQL Server 2000 only.  Look under Requirements.

Apex has a tool that works with 2005, and their web site says "and
soon, SQL Server 2008".

I am in agreement, of course, with everything Tibor has been saying.
In particular I would not create a backup device these days, and have
not done so since I stopped backing up to locally attached tape
drives.  Likewise, his suggestions about backup file naming.  You
should also look into the third party backup tools, such as Redgate's.

Roy Harvey
Beacon Falls, CT

27-Oct-08 02:53 PM

soon, SQL Server 2008". <

Thank you sir.  Perhaps MS should buy Apex instead.

Thanks tons!

27-Oct-08 03:07 PM

it is not as clear how it works.  I see that I can download it to try it out.
If it works like the Red-Gate tool, it will be perfect.

Thanks again!

27-Oct-08 06:11 PM

Well.. I thought this would work, but it doesn't.  When I try to restore the
first log I get the error:  The log or differential backup cannot be restored
because no files are ready to rollforward.

Backup DATABASE MyDatabase TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabaseBackup\MyDatabaseData.dat'

Backup LOG MyDatabase TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabaseBackup\MyDatabaseLog.dat'

Backup LOG MyDatabase TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabaseBackup\MyDatabaseTail.dat'
WITH NORECOVERY

Restore database MyDatabase FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabaseBackup\MyDatabaseData.dat'
WITH REPLACE

Restore LOG MyDatabase FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabaseBackup\MyDatabaseLog.dat' WITH
NORECOVERY

Restore LOG MyDatabase FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabaseBackup\MyDatabaseTail.dat'
WITH RECOVERY

27-Oct-08 07:19 PM

(snip)

Hi IT_Architect,

I realise that this is mainly a matter of personal preference, but I
must say that I shuddered when I read your description of the recovery
process earlier in the thread. (BUt maybe I misunderstood?)

I want the database to guard integrity and business rules for the data.
I use constraints for that where possible, and triggers where required.
Now, I would be reallly worried if I knew that there was a way to back
out some changes, edit the log and then redo these (manually edited)
changes. What if I accidentally change an update that was caused from
within the trigger without also editing the statement that caused it to
fire? I would end up with a database in an inconsistent state.

Another worrying consequence is that this technique allows to redo
modifications performed after the erroneous change that you have
corrected. That may sound good at first - but think about it. Suppose
that an UPDATE issued five minutes after the erroneous TRUNCATE TABLE
should have failed, because it violated a business rule, was accepted as
a result of the recently truncated table? Should redoing the log allow
it to succeed again? Or should it now fail? How will the user that
entered the transaction know about this? And can we ever be sure that
the next transaction started by the same user would also have been
started if the UPDATE had failed (as it should have)? Really, this
sounds like opening a very, very, very big can of worms.

I agree that undoing an error in SQL Server can take patience,
especially if you don't know exactly when the error was made (for it's a
painstaking proces to keep restoring the same log to yet a few minutes
later every time until you find the problem). But I can at least be sure
that after each restore, the database will always be in a consistent
state, for the restore process will always respect ACID properties: a
transaction that was still running at the time specified in the STOPAT
parameter will not be restored. Providing a log **READER** would go a
very long way towards reducing that pain. I agree with you here that
this is a hole that Microsoft should have filled itself, several
versions ago already, instead of relying on third parties to provide
such a tool.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

27-Oct-08 09:04 PM

I'm using 2008.  When I tried:
Restore database MyDatabase FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabaseBackup\MyDatabaseData.dat'
WITH REPLACE NORECOVERY

I got a syntax error: Incorrect syntax near 'NORECOVERY'.

28-Oct-08 04:01 AM

You need to comman-separate the options:

WITH REPLACE, NORECOVERY


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

28-Oct-08 06:43 AM

This is leading to a topic which I've learned can be a very sensitive
topic.

Fact is that SQL Server will not let us in or provide tools to get to
a database where different sets of data is from different point in
time. This is a point I had to push a lot when filegroup backups was
introduced and suggested for some pretty ... imaginative backup
strategies.

Also, SQL server will not allow us to select some operation performed
somewhere in the past and undo that operation.

I agree regarding how "worminess" one might end up. One thing I've
learned from my years in training is that some people understand and
accept this immediately. Where others find it amazing that SQL server
doesn't have ..."deeper" abilities. I try to explain data integrity
issues (just as in your post, Hugo) where some accept it and other
don't. We had a case in one of the systems I support, a while back,
where a user deleted some information. I did a point in time restore
into new database and and handed this off to a developer *who I trust
to understand the datamodel*. It's like this colliding thingie in Cern
they recently took for a spin. Would I let anyone collide particles so
we can end up with a (whatever how small) black hole? No. Do I trust
those who are doing it? Yes. (OK, I'm exaggerating and I do understand
at least a little bit about the tinyness of the energy levels produced
by such collide.)

My point is that there are very different perceptions about this topic
understanding about integrity issues and cautious, or probably at the
opposite. At least I like to think so. I'm not sure I want the product
to provide functionality which makes it easier for me to get an
inconsistent database... I know I can handle such a tool with care. I
just don't know how miss-used it will end up? Perhaps great tool to
generate consulting hours?


What I do know is that I definitely have two things I very much would
want in the product:

a) A log *reader* tool. This so I can decipher the log and find out
when to stop my RESTORE LOG.

b) A "reverse time" command. It would be great if we had a command to
for instance back the database say 5 minutes. We can achieve this
today using BACKUP LOG, RESTORE DATABASE, RESTORE LOG and finally
RESTORE LOG WITH STOPAT. But it would me much nicer if we could just
tell SQL Server to "REVERSE DATABASE myDb TO '10:23:12'". Perhaps the
structure of transaction logging doesn't allow for this? But OTOH,
perhaps it would be possible to expand on what is logged so it would
be possible?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

28-Oct-08 10:58 AM

WITH REPLACE, NORECOVERY <

That was it.  Awesome!

This is how my thoughts are coming together now.
1.  When you are doing a full database backup, you are not saving off a copy
of the database that could be copied into the live area and used nor is that
the case with log files.  These are binary files which are not exact
snapshots of the database or log files.
2.  The database backup only backs up the database, and very few
transactions from the log file, but at least 2 so it can enforce the sequence
during restore from DATABASE or LOG backups.  The size of the database backup
is very close to the size of the database, not the database plus log file.
3.  A log file backup is a backup of the log file only, and it truncates the
active log file by default, but you can override that with .
4.  There are several scenarios you could play with 2 & 3.  You could do a
full database backup on Friday and a log backup, and:
a.  differentials on the rest of the days.
b.  just do log file backups during the week.
c.  any combination.
5.  To restore, you would need that latest full backup, plus the latest
differential backup if you had one, and any log files after the latest
database backup.
6.  The NORECOVERY explanation in the docs was confusing for me and changes
depending upon where it is used.  This is my take on what is really
happening, and correct me if I'm wrong.  WITH RECOVERY and NORECOVERY both
roll forward.  NORECOVERY very simply shifts the database into restore mode.
This freezes it in time so that no more transactions can be applied.  The
reason it is used while backing up the tail log, is to prevent any changes to
the database and logs until the restore is completed.  It's the only thing
that makes it unique as a log backup.  When you continue with your RESTORE,
beginning with the first full backup and going forward, you must specify
NORECOVERY with each RESTORE, DATABASE or LOG, because the default is
RECOVERY, which unlocks the database, allowing new transactions to be
applied.  Once you begin posting transactions that are newer than the ones
you are trying to apply from the backups, subsequent RESTORE commands will
return the error message: "The log or differential backup cannot be restored
because no files are ready to rollforward." which is true, because the latest
transaction posted to the database is newer than the last transaction in the
backup you are trying to restore.  Thus, the message makes perfect sense
because MSSQL thinks they've already posted to the database even if they
haven't.  Your last operation you must not specify NORECOVERY because you are
saying after rolling forward the transactions in the file, release the
database to go live again.  STOPAT simply short-stops the final restore part
way through the log restore.
7.  There are two chains, the DATABASE backup chain, and the LOG backup
chain.  What coordinates them is the beginning and ending transaction numbers
in the DATABASE and LOG backups.  That is also the reason the DATABASE backup
does not truncate the LOG.  You need both to get back where you want to be
for a couple of reasons:
a) In any case you need the tail log, or part of it, because it is not
included in any backup of the database.
b) You cannot do a STOPAT in the middle of a DATABASE restore, it's all or
nothing.  You would need to go back to a previous DATABASE backup(s), and go
forward with logs to the point you want to STOPAT.

Other:  What was the logic for removing TRUNCATEONLY?

Thanks tons!

28-Oct-08 11:42 AM

Inline...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



Correct. The backups are not a "bit-by-bit copy" of the mdf or the ldf
file.



Correct. Note that empty extents (not used) are not included in a
database backup. since we often have a fair amount of free space in
out databases it is not unlikely that a database backup is some 60, 70
(or something) % of the size of the combined mdf and ndf files.



.... with NO_TRUNCATE (which is a very misleading name - see
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp) or
COPY_ONLY. Instead of the word "truncate" you can say "mark as
available for overwrite or deallocation in conjunction with a shrink"
if you want - same same.



Yes. Just remember that if you don't do log backups, then you want
simple recovery model (or the lfd file with grow and grow).


Yes.



Correct. RECOVERY also does rollback. After rollback has been
performed, you can't restore any further backups since you rolled back
and you would have a "gap" of the transacitons the were rolled back.

As for NORECOVERY when you do a *backup*, well this is a funny name
for the option. I don't think you should look for a technical
explanation of what it does. think of it as "I now do the very last
log backup before I'll start mu restore optionation. I want to close
down the database after that backup so I *know* that no more
modifications are done between this last log backup and my seubsequent
restore command.




Did I miss something here? Can you elaborate (I don't understand the
question)...?

28-Oct-08 12:55 PM

The Truncate_Only switch was removed in 2008.

Thanks tons for all of your help.

PS:  I tried to make a post to support adding a log edit tool so people can
recover from a disaster but I cannot.  I keep getting an error about maximum
pending or something.

PS:  I just realized I missed a bunch of Hugo's post about the log editor.
A sophisticated one will catch DDL violations and have them there for you to
resolve.  The trigger thing cannot be done that way.  It's true you MAY lose
or corrupt some data, but it beats the stars out of losing all of the data
after the error.  With ERP and banks, a lot of transactions are made by
machines and EDI and nobody knows what data went in.  All of the sudden they
wouldn't know how many parts they have, how much material they have, what
they've shipped, what they shipped, who they owed, and who owes them.  In
real life, the ones that fail to post are the result of a row being
initialized to default values which would be the row that would choke.  The
ones that fail end up in your error log anyway.  Most of the time you don't
need to do anything.  If it was an inventory adjustment, and the trigger
makes it, it would automatically correct an inventory transaction error by
starting with the correct beginning total.  Usually, you can ignore the
error.  I've been through this a few times and I couldn't even imagine
putting together something where I was that much at risk.  It's the
difference between a very disrupting data loss and tiny or none.  If there is
an error that might results from the edited restore, it will be tiny compared
to the errors that always happen when people attempt to recreate the last X
minutes or hours.

29-Oct-08 06:30 AM

For all intents and purposes, it accomplishes the same thing as Simple
except Simple does it automatically.

Thanks!

29-Oct-08 07:44 AM

You can combine STOPAT and STANDBY to restore the
same log backup forwarding the clock each time with as small
increments as you wish. As soon as you have passed the accident you
have found the restore time you want to use. See
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp.<

In a small vertical market app, the user does backups when he wants to.
When the backup occurs, I perform a full database backup followed by a log
backup.  These overwrite the two that are there.  This keeps it simple for
now while I'm developing this concept for use in the program.  This gives me
three files to work with, the database backup, the immediately following log
backup, and the current log in the active are that can be converted into a
tail log backup.

I've written a function to restore where the user gets a radio box with two
buttons, the default is "Restore to last full backup" which shows the date
and time of that backup, and a second radio button that says "Restore to
later date and time" where he can select the date from a calendar and time in
an entry box that ranges from the date and time of the log file backup to
present.  If he selects "Restore to last full backup", it restores the
previous full backup and log.  If he selects "Restore to later date and
time", it performs a tail log backup, and restores the full database backup,
log backup, and tail log with STOPAT.

The problem with this scenario is he only gets one shot.  I'm looking at two
options.
1.  Preserve the tail log until the next backup.  I would programmatically
delete it with the backup.  This would allow the user to restore within that
period as many times as necessary but never again forward of the date and
time on the tail log until the next db and log backup when the tail log would
be erased.  Thus, he could start over with the backup of the full restore,
log, and different point in the tail log backup if he is wrong the first time.
2.  The other way is more of a question if there is a way to use STANDBY to
advantage. (Which I don't completely understand)  Remember, this is not done
on the server, it is done from a user program, and the user program needs to
work in order for him to determine if he is at the right place in the
restore.  There is no DBA on-site for small apps that I have using MSSQL.
(Prior to this they were simply restore from the last full backup, but that
depends on them to saying yes to backup when they leave the program.)

Any thoughts appreciated for a better strategy.

Thanks Tons for everyone's help!

29-Oct-08 09:58 AM

PS:  It appears to me that transaction file management becomes tricky once a
restore has been performed that does not restore all of the transaction files
as is the case with STOPAT.  That log, and subsequent logs would no longer be
usable, even though they reside in your backup archive in logically ascending
dates and times.  After you've figured out where the proper point in time to
restore to, and restored to it, you would almost need to archive off your
previous backups, and do a complete db and log backup to start a new point of
recovery.

29-Oct-08 02:01 PM

happen unless the user does them.<

They will still have a db, log, and previous backups of the db and logs from
the hard drive backup.  It's not a great method, but they backup fine and
restore fine.  The active log might get a little big if they don't backup on
a timely manner.

However, I'm certainly open to a better idea.  The criterion are:
1.  The office person can restore to a certain point in history.  They don't
have to go back a week or anything, but the previous working day would not be
out of the question.
2. It installs from a DVD, DB and all.  Sometimes it will run on a desktop
and used by one user.  Sometimes it will be installed on a desktop machine
and used peer-to-peer.  The desktop is not necessarily always on.  Some are
installed on SBS and shared across the network.
3.   I don't want support calls.

The way I have it working now is with 3 files, the DB backup, the Log backup
taken immediately after the DB backup, and the live log file.  The date of
the backup log determines the earliest he can go back.  During a restore, the
live log becomes the tail, and allows the user to traverse through time at
will up to the time the tail log was taken.  Once the user agrees it is
restored to the proper cut-off, it prompts to do another full backup, which
wipes out the tail log in the backup directory, and replaces the current DB
and LOG backups.  There are never any unusable logs from a STOPAT restores.
There is no user maintenance required.

If you have a better idea of how to accomplish this, I'd be glad to hear it.
Don't worry about it being complex, I can program anything.  It's the user
aspect that matters.

Thanks!