Loading ...
Sorry, an error occurred while loading the content.

Re: MUGH-SQLCON Re: Log Backup

Expand Messages
  • Ved Prakash Agrawal
    Good Explaination...Madhu... Thanks Ved ... From: Madhu K Nair To: sqlcon@yahoogroups.com Sent: Friday, January 12, 2007 10:52:57 PM
    Message 1 of 6 , Jan 15, 2007
    • 0 Attachment
      Good Explaination...Madhu...

      Thanks
      Ved

      ----- Original Message ----
      From: Madhu K Nair <madhuotp@...>
      To: sqlcon@yahoogroups.com
      Sent: Friday, January 12, 2007 10:52:57 PM
      Subject: MUGH-SQLCON Re: Log Backup

      Hi Manish,

      This is not a valid case and that is what I mentioned. I think u
      have missed the observation part. Nevertheless, I will repeat what
      I really want to mention here.
      In this particular test case, what i have addressed is three
      different scenario

      (a) What will happen if we try to restore the TL with STOPAT option
      which is earlier than Full Backup Taken time. This should give u
      error and it has, this is invalid operation. That I have mentioned
      in the Observation also
      Eg. if your FUll backup is taken at 08:30 AM and TL is taken at
      08:40 AM and you have restored Full Backup with NO RECOVERY option
      and then u r trying to restore TL with STOPAT 08:20 , which is an
      invalid scenario, i really want to show what will happen in that
      case.

      (b) Second scenario is You have Restored the Full Backup with NO
      RECOVERY Option and You have restored TL with STOPAT option by
      specifying time between 08:30 and 08:40. It is a perfect case for TL
      Restoration and it works fine. After TL restoration the Database is
      online.

      (c) In the third scenario what i want to show is What will happen if
      u r specifying the STOPAT option after the TL backup taken time. ie.
      you TL bakcup is taken at 08:40 and you are giving STOPAT 08:45,
      then what will happen. In this case, the database state will be
      Restoring and It will not be Online after the TL restoration also.

      There are straight forward scenario given in BOL, thats why i did
      little this unconventional method. These are the common doubt we all
      have and until unless u try in various permutation and combination
      it will not be clear.

      Madhu

      --- In sqlcon@yahoogroups. com, manish singhal <manish_hce@ ...> wrote:
      >
      > Madhu can you please xplin this :
      >
      > "I will restore the full backup with NoRecovery Option and then I
      > will restore the log earlier than the full backup taken time"
      >
      > Thanks
      > Manish Singhal
      >
      >
      > ----- Original Message ----
      > From: mohd rizwan <mo2riz@...>
      > To: sqlcon@yahoogroups. com
      > Sent: Thursday, January 11, 2007 10:14:25 PM
      > Subject: Re: MUGH-SQLCON Re: Log Backup
      >
      > Great explanation Madhu....I really appreciate�
      >
      > Rizwan...
      >
      > Madhu K Nair <madhuotp@yahoo. com> wrote:
      > Hi Santosh,
      >
      > First let me tell you, if you really want to learn the backup
      > architecture of SQL Server, you should try yourself with some case
      > study in your scenario. Otherwise, it would be like learning
      > swimming through a curresponding course ��. What I would
      ��request��
      > you is, try yourself first and if you face any problem post in
      > fourm. In this particular question, there is no one word answer.
      The
      > actual answer will be more or less copy and paste from BOL, which
      > you already have. So I am not doing that, I have done few test
      > cases for you, which is of course very simple,and will lead you in
      > right direction. It is not a substitution for BOL. You will
      > understand this only when you do this after reading BOL regarding
      > SQL Server Backup Architecture.
      >
      > Basically u have two questions
      >
      > (a) Is Passible to take only log back up and as same to restore...
      > No, it is not possible to restore the database from *ONLY*
      > Transaction Log Backup, it has to have a full backup or base line
      > backup.
      >
      > (b) After taking log backup, can i delete the contains of log
      files
      > to release the memory space..
      > It again depends , it is depend upon your backup policy, you can
      > truncate the TL and you can shrink it. But You should take Full
      > backup soon after truncating and shrinking log file.
      >
      > Here is my test case �K..
      >
      > I have a database called Test and I have Full Backup and
      Transaction
      > Log backup as mentioned below :-
      >
      > (a) full backup of [i][u]10 Jan 2007 0830 AM [/i]
      > (b) Log backup of [i][u]10 Jan 2007 0840 AM[/i]
      >
      > I am restoring the database
      >
      > Scenario 1
      >
      > I will restore the full backup with NoRecovery Option and then I
      > will restore the log earlier than the full backup taken time
      >
      > Step 1 : Restoring Full Backup
      >
      > RESTORE DATABASE Test
      > FROM disk='D:\test. bak'
      > WITH NORECOVERY;
      >
      > Result
      > Processed 152 pages for database 'Test', file 'testchanged' on
      file
      > 1.
      > Processed 2 pages for database 'Test', file 'test_log' on file 1.
      > RESTORE DATABASE successfully processed 154 pages in 0.202 seconds
      > (6.209 MB/sec).
      >
      > Step 2 : Restoring Transaction Log Backup
      >
      > RESTORE LOG Test
      > FROM disk='D:\testlog. bak'
      > WITH RECOVERY, STOPAT = 'jan 10, 2007 08:20 AM';
      >
      > Processed 0 pages for database 'Test', file 'testchanged' on file
      1.
      > Processed 10 pages for database 'Test', file 'test_log' on file 1.
      > Msg 4335, Level 16, State 1, Line 1
      > The specified STOPAT time is too early. All or part of the
      database
      > is already rolled forward beyond that point.
      > Msg 3013, Level 16, State 1, Line 1
      > RESTORE LOG is terminating abnormally.
      >
      > Observation: This is not valid, because you are trying to restore
      > the transaction log backup with StopAt option to Time earlier than
      > full backup. The database remains in restoring state and non
      > operational
      >
      > SENARIO 2
      >
      > I will restore to the exact time when the log backup was taken or
      to
      > a time between Fullbackup and Trascation log backup
      >
      > Step 1 : Restoring Full Backup
      >
      > RESTORE DATABASE Test
      > FROM disk='D:\test. bak'
      > WITH NORECOVERY;
      >
      > Result
      > Processed 152 pages for database 'Test', file 'testchanged' on
      file
      > 1.
      > Processed 2 pages for database 'Test', file 'test_log' on file 1.
      > RESTORE DATABASE successfully processed 154 pages in 0.202 seconds
      > (6.209 MB/sec).
      >
      > Step 2 : Restoring Transaction Log Backup
      >
      > RESTORE LOG Test
      > FROM disk='D:\testlog. bak'
      > WITH RECOVERY, STOPAT = 'jan 10, 2007 08:40 AM';
      >
      > Processed 0 pages for database 'Test', file 'testchanged' on file
      1.
      > Processed 10 pages for database 'Test', file 'test_log' on file 1.
      > RESTORE LOG successfully processed 10 pages in 0.012 seconds
      (6.272
      > MB/sec).
      >
      > Observation :This is the actual case of Transaction log StopAt, I
      > have restored just till the time transaction log backed up. I
      could
      > restore the database to 08:35 AM,08:39AM etc. After the restore
      the
      > database is in recovered state and operational
      >
      > Secnario 3
      >
      > Here what I am going to do is ,
      >
      > My Transaction Log backup is till 08:40 AM. I am trying to restore
      2
      > min later the backup which I have taken. Ie. 08:42AM
      > [u]Step 1 : Restoring Full Backup
      >
      > RESTORE DATABASE Test
      > FROM disk='D:\test. bak'
      > WITH NORECOVERY;
      >
      > Result
      > Processed 152 pages for database 'Test', file 'testchanged' on
      file
      > 1.
      > Processed 2 pages for database 'Test', file 'test_log' on file 1.
      > RESTORE DATABASE successfully processed 154 pages in 0.202 seconds
      > (6.209 MB/sec).
      >
      > Step 2 : Restoring Transaction Log Backup
      >
      > RESTORE LOG Test
      > FROM disk='D:\testlog. bak'
      > WITH RECOVERY, STOPAT = 'jan 10, 2007 08:42 AM';
      >
      > Processed 0 pages for database 'Test', file 'testchanged' on file
      1.
      > Processed 10 pages for database 'Test', file 'test_log' on file 1.
      > This backup set contains records that were logged before the
      > designated point in time.
      > The database is being left in the restoring state so that more
      roll
      > forward can be performed.
      > RESTORE LOG successfully processed 10 pages in 0.015 seconds
      (5.017
      > MB/sec).
      >
      > Observation: The Backup is still in Recovering State. It is still
      > expecting another Transaction Log Backup to restore and the
      database
      > is non-opertaional.
      >
      > Madhu
      >
      > --- In sqlcon@yahoogroups. com, "nic_santosh"
      > <santosh.samantaray @...> wrote:
      > >
      > > Hi All
      > > Is Passible to take only log back up and as same to restore...
      > >
      > > Is It Possible then How?
      > >
      > > after taking log backup,can i delete the contains of log files
      to
      > > release the memory space..
      > >
      > > With Regards
      > > S.Santosh
      > >
      >
      > Thanks & Regards,
      > Mohd Rizwan
      >
      > (�`�.���) Always
      > `�.�(�`�.���) Keep
      > (�`�.���)�.�� Smiling!
      > `�.�.��
      >
      > Send instant messages to your online friends
      http://uk.messenger .yahoo.com
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      >
      >
      >
      >
      ____________ _________ _________ _________ _________ _________ _
      ____________ ___
      > The fish are biting.
      > Get more visitors on your site using Yahoo! Search Marketing.
      > http://searchmarket ing.yahoo. com/arp/sponsore dsearch_v2. php
      >
      > [Non-text portions of this message have been removed]
      >






      ____________________________________________________________________________________
      Do you Yahoo!?
      Everyone is raving about the all-new Yahoo! Mail beta.
      http://new.mail.yahoo.com

      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.