Re: Recovering Transaction Logs

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/06/04 

Date: Tue, 6 Jul 2004 09:48:41 -0400

***,

Backing up MSDB is always a good thing to do but it does not affect the way
differentials work at all. The problem with relying on MSDB to provide the
restore chain is that you usually only backup MSDB once a night. Unless you
backed it up after every log backup you run the risk that it won't contain
all the information needed to restore all the log files. If your backing up
the log every minute I assume you have vital information you don't want to
loose. If that is the case isn't it worth spending a little time to develop
a tool to ensure your customers can restore properly with little effort? Or
invest in a tool such as SQL LiteSpeed that will not only compress your
backups but give you an interface that will restore your logs in the proper
order regardless of the history in MSDB. 

-- 
Andrew J. Kelly  SQL MVP
"*** Willis" <***.willis@softsolutionsit.com> wrote in message
news:4b09688b.0407060441.51ad4ba8@posting.google.com...
> If MSDB isn't being backed up, will differential backups do anything
> other than shorten the number of t-logs that need to be recovered.  No
> magic, right?  I think the solution ought to be that I should back up
> MSDB so that I can take advantage of the backup histories when I
> recover the files.
>
> Our customer is willing to use the Recovery "wizard" but I'm not sure
> if I can ask them to execute SQL code, even if I document it well.  My
> guess it that their perception of the "ease of use" would be very
> different.
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:<#HVZlE9XEHA.3972@TK2MSFTNGP12.phx.gbl>...
> > You might want to think about doing a differential backup several times
a
> > day so you don't have that many log files to recover.  It can be a lot
> > faster.  But there is nothing built in to SQL Server that I know of.
There
> > are several third part products that do backups that will take care of
that
> > for you though.  SQL LiteSpeed, SQL Backtrac etc.  You could probably
write
> > some code that loops thru the files and determines the order with
"Restore
> > LabelOnly".
> >
> > -- 
> > Andrew J. Kelly  SQL MVP
> >
> >
> > "*** Willis" <***.willis@softsolutionsit.com> wrote in message
> > news:4b09688b.0407011151.4ee9f28@posting.google.com...
> > > We are experimenting with different backup/restore techniques.  One
> > > technique is to perform full backup once a day, then perform
> > > transaction log backups once a minute.  This generates 1440 t-log
> > > files per day.  We are storing them on a networked drive.
> > >
> > > When I recover to the same SQL Server that made the backups - no
> > > problem - the MSDB database provides a list of database files and
> > > t-log files to pick from, and I can easily click on the t-log that I
> > > want to recover to.  It will automatically perform the database
> > > recovery and then the t-log recovery, in order, for me.  Great.
> > >
> > > Problem is that when I want to recover to a server that didn't make
> > > the backups(and it doesn't have the same MSDB), I have to manually
> > > create a device for each t-log file, which is so tedious I can't
> > > recommend it.
> > >
> > > What is the technique for recovering a huge number of t-logs from a
> > > common networked disk?  Do I have to back up the MSDB database every
> > > time I make a t-log backup, and then recover the MSDB on the other
> > > servers first?  I can arrange things so that the disk filename is the
> > > same for any server trying to recover from the files.
Quantcast