Point in time recovery

Hi
My Backup policy is to take transaction log backups every 30 minutes...
what if my server crashes at 10 minutes before a Transaction log backup
takes place...
e.g. Full Backup 1:00 pm
Transaction Log Backup - 1:30 pm
Transaction Log Backup - 2:00 pm
Transaction Log Backup - 2:30 pm
Server Crashes at 2:50 pm ... can we recover the work done form 2:30
pm to 2:50 pm ...
Please help
ThanksNo, you would need an additional tlog backup done after 2:50 to do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> Hi
> My Backup policy is to take transaction log backups every 30 minutes...
> what if my server crashes at 10 minutes before a Transaction log backup
> takes place...
> e.g. Full Backup 1:00 pm
> Transaction Log Backup - 1:30 pm
> Transaction Log Backup - 2:00 pm
> Transaction Log Backup - 2:30 pm
> Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> pm to 2:50 pm ...
> Please help
> Thanks
>|||Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what the
purpose of this option is. Post back if you have further questions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> Hi
> My Backup policy is to take transaction log backups every 30 minutes...
> what if my server crashes at 10 minutes before a Transaction log backup
> takes place...
> e.g. Full Backup 1:00 pm
> Transaction Log Backup - 1:30 pm
> Transaction Log Backup - 2:00 pm
> Transaction Log Backup - 2:30 pm
> Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> pm to 2:50 pm ...
> Please help
> Thanks
>|||It backups the log file without truncating it ...
But when the server crashes... how can i use that log file... i mean I
wont be able to take a backup of that log file cause i would not have
access to the server...
Please can someone explain this
Thanks
Tibor Karaszi wrote:
> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what the
> purpose of this option is. Post back if you have further questions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> > Hi
> >
> > My Backup policy is to take transaction log backups every 30 minutes...
> > what if my server crashes at 10 minutes before a Transaction log backup
> > takes place...
> >
> > e.g. Full Backup 1:00 pm
> > Transaction Log Backup - 1:30 pm
> > Transaction Log Backup - 2:00 pm
> > Transaction Log Backup - 2:30 pm
> >
> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> > pm to 2:50 pm ...
> >
> > Please help
> >
> > Thanks
> >|||> But when the server crashes... how can i use that log file... i mean I
> wont be able to take a backup of that log file cause i would not have
> access to the server...
You need to consider a number of scenarios in developing your recovery plan.
Also consider your tolerance for data loss (SLAs) and the cost of preventing
such.
The worst case is that you lose your data center and can recover using only
off-site storage. You will likely lose some data in that scenario unless
you engineer a (very expensive) system to mirror all data real time to an
offsite location. Similarly, you will loose data if you lose your log file
for any reason; you can recover only to your last transaction log backup.
If you lose data file(s) but your log is intact, you'll need to first
recover to the point where SQL Server is running but the database is suspect
due to the data file problem. This may involve correcting hardware
problems, rebuilding the server OS and restoring SQL Server system
databases, depending on the particulars of the recovery scenario. You can
then use the BACKUP LOG...WITH NO_TRUNCATE to extract the last log you'll
need for the restore process.
In any case, you should schedule transaction log backups (stored on a
different server) that are frequent enough to meet your data loss SLAs.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
> It backups the log file without truncating it ...
> But when the server crashes... how can i use that log file... i mean I
> wont be able to take a backup of that log file cause i would not have
> access to the server...
> Please can someone explain this
> Thanks
>
>
> Tibor Karaszi wrote:
>> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command.
>> This is exactly what the
>> purpose of this option is. Post back if you have further questions.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Double_B" <bharatbutani@.gmail.com> wrote in message
>> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
>> > Hi
>> >
>> > My Backup policy is to take transaction log backups every 30 minutes...
>> > what if my server crashes at 10 minutes before a Transaction log backup
>> > takes place...
>> >
>> > e.g. Full Backup 1:00 pm
>> > Transaction Log Backup - 1:30 pm
>> > Transaction Log Backup - 2:00 pm
>> > Transaction Log Backup - 2:30 pm
>> >
>> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
>> > pm to 2:50 pm ...
>> >
>> > Please help
>> >
>> > Thanks
>> >
>|||In addition to Dan's comments:
If the server is really toast, but the log file is intact:
Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove the
database files. "Slide" in your log file from the crashed database. Start that SQL Server, and the
database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
backup the log of a crashed database even if the whole machine goes south, assuming you get to the
ldf file(s).
Another comment:
>> It backups the log file without truncating it ...
Don't let the name of the option fool you. The purpose of this option is exactly what we have
described here. The options is badly named, quite simply. This is why I suggested you to study the
literature about the meaning of this option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
>> But when the server crashes... how can i use that log file... i mean I
>> wont be able to take a backup of that log file cause i would not have
>> access to the server...
> You need to consider a number of scenarios in developing your recovery plan. Also consider your
> tolerance for data loss (SLAs) and the cost of preventing such.
> The worst case is that you lose your data center and can recover using only off-site storage. You
> will likely lose some data in that scenario unless you engineer a (very expensive) system to
> mirror all data real time to an offsite location. Similarly, you will loose data if you lose your
> log file for any reason; you can recover only to your last transaction log backup.
> If you lose data file(s) but your log is intact, you'll need to first recover to the point where
> SQL Server is running but the database is suspect due to the data file problem. This may involve
> correcting hardware problems, rebuilding the server OS and restoring SQL Server system databases,
> depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
> NO_TRUNCATE to extract the last log you'll need for the restore process.
> In any case, you should schedule transaction log backups (stored on a different server) that are
> frequent enough to meet your data loss SLAs.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
>> It backups the log file without truncating it ...
>> But when the server crashes... how can i use that log file... i mean I
>> wont be able to take a backup of that log file cause i would not have
>> access to the server...
>> Please can someone explain this
>> Thanks
>>
>>
>> Tibor Karaszi wrote:
>> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
>> the
>> purpose of this option is. Post back if you have further questions.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Double_B" <bharatbutani@.gmail.com> wrote in message
>> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
>> > Hi
>> >
>> > My Backup policy is to take transaction log backups every 30 minutes...
>> > what if my server crashes at 10 minutes before a Transaction log backup
>> > takes place...
>> >
>> > e.g. Full Backup 1:00 pm
>> > Transaction Log Backup - 1:30 pm
>> > Transaction Log Backup - 2:00 pm
>> > Transaction Log Backup - 2:30 pm
>> >
>> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
>> > pm to 2:50 pm ...
>> >
>> > Please help
>> >
>> > Thanks
>> >
>|||Thanks a ton for the help guys...
I just wanted the procedure , of how to move ahead if i had the
transaction log .. I mean as Tibor said ... I would create a new DB ...
then de-tach it & attach the old transcation log file & then take a
backup & then restore that after the full backup of the database ...
Is that that correct way .. or are there some more steps...
Tibor Karaszi wrote:
> In addition to Dan's comments:
> If the server is really toast, but the log file is intact:
> Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove the
> database files. "Slide" in your log file from the crashed database. Start that SQL Server, and the
> database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
> backup the log of a crashed database even if the whole machine goes south, assuming you get to the
> ldf file(s).
>
> Another comment:
> >> It backups the log file without truncating it ...
> Don't let the name of the option fool you. The purpose of this option is exactly what we have
> described here. The options is badly named, quite simply. This is why I suggested you to study the
> literature about the meaning of this option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
> >> But when the server crashes... how can i use that log file... i mean I
> >> wont be able to take a backup of that log file cause i would not have
> >> access to the server...
> >
> > You need to consider a number of scenarios in developing your recovery plan. Also consider your
> > tolerance for data loss (SLAs) and the cost of preventing such.
> >
> > The worst case is that you lose your data center and can recover using only off-site storage. You
> > will likely lose some data in that scenario unless you engineer a (very expensive) system to
> > mirror all data real time to an offsite location. Similarly, you will loose data if you lose your
> > log file for any reason; you can recover only to your last transaction log backup.
> >
> > If you lose data file(s) but your log is intact, you'll need to first recover to the point where
> > SQL Server is running but the database is suspect due to the data file problem. This may involve
> > correcting hardware problems, rebuilding the server OS and restoring SQL Server system databases,
> > depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
> > NO_TRUNCATE to extract the last log you'll need for the restore process.
> >
> > In any case, you should schedule transaction log backups (stored on a different server) that are
> > frequent enough to meet your data loss SLAs.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Double_B" <bharatbutani@.gmail.com> wrote in message
> > news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
> >> It backups the log file without truncating it ...
> >>
> >> But when the server crashes... how can i use that log file... i mean I
> >> wont be able to take a backup of that log file cause i would not have
> >> access to the server...
> >>
> >> Please can someone explain this
> >>
> >> Thanks
> >>
> >>
> >>
> >>
> >> Tibor Karaszi wrote:
> >> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
> >> the
> >> purpose of this option is. Post back if you have further questions.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> >> > Hi
> >> >
> >> > My Backup policy is to take transaction log backups every 30 minutes...
> >> > what if my server crashes at 10 minutes before a Transaction log backup
> >> > takes place...
> >> >
> >> > e.g. Full Backup 1:00 pm
> >> > Transaction Log Backup - 1:30 pm
> >> > Transaction Log Backup - 2:00 pm
> >> > Transaction Log Backup - 2:30 pm
> >> >
> >> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> >> > pm to 2:50 pm ...
> >> >
> >> > Please help
> >> >
> >> > Thanks
> >> >
> >>
> >
> >|||Those are the steps basically, but not detach/attach. Rather stop SQL Server, delete the files, copy
over the tlog file in the place of the old tlog file. I know there has been a KB article about this
particular subject, and I'd be surprised if it has been removed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1157040981.529433.265080@.i3g2000cwc.googlegroups.com...
> Thanks a ton for the help guys...
> I just wanted the procedure , of how to move ahead if i had the
> transaction log .. I mean as Tibor said ... I would create a new DB ...
> then de-tach it & attach the old transcation log file & then take a
> backup & then restore that after the full backup of the database ...
> Is that that correct way .. or are there some more steps...
>
>
> Tibor Karaszi wrote:
>> In addition to Dan's comments:
>> If the server is really toast, but the log file is intact:
>> Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove
>> the
>> database files. "Slide" in your log file from the crashed database. Start that SQL Server, and
>> the
>> database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
>> backup the log of a crashed database even if the whole machine goes south, assuming you get to
>> the
>> ldf file(s).
>>
>> Another comment:
>> >> It backups the log file without truncating it ...
>> Don't let the name of the option fool you. The purpose of this option is exactly what we have
>> described here. The options is badly named, quite simply. This is why I suggested you to study
>> the
>> literature about the meaning of this option.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
>> >> But when the server crashes... how can i use that log file... i mean I
>> >> wont be able to take a backup of that log file cause i would not have
>> >> access to the server...
>> >
>> > You need to consider a number of scenarios in developing your recovery plan. Also consider your
>> > tolerance for data loss (SLAs) and the cost of preventing such.
>> >
>> > The worst case is that you lose your data center and can recover using only off-site storage.
>> > You
>> > will likely lose some data in that scenario unless you engineer a (very expensive) system to
>> > mirror all data real time to an offsite location. Similarly, you will loose data if you lose
>> > your
>> > log file for any reason; you can recover only to your last transaction log backup.
>> >
>> > If you lose data file(s) but your log is intact, you'll need to first recover to the point
>> > where
>> > SQL Server is running but the database is suspect due to the data file problem. This may
>> > involve
>> > correcting hardware problems, rebuilding the server OS and restoring SQL Server system
>> > databases,
>> > depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
>> > NO_TRUNCATE to extract the last log you'll need for the restore process.
>> >
>> > In any case, you should schedule transaction log backups (stored on a different server) that
>> > are
>> > frequent enough to meet your data loss SLAs.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "Double_B" <bharatbutani@.gmail.com> wrote in message
>> > news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
>> >> It backups the log file without truncating it ...
>> >>
>> >> But when the server crashes... how can i use that log file... i mean I
>> >> wont be able to take a backup of that log file cause i would not have
>> >> access to the server...
>> >>
>> >> Please can someone explain this
>> >>
>> >> Thanks
>> >>
>> >>
>> >>
>> >>
>> >> Tibor Karaszi wrote:
>> >> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
>> >> the
>> >> purpose of this option is. Post back if you have further questions.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Double_B" <bharatbutani@.gmail.com> wrote in message
>> >> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
>> >> > Hi
>> >> >
>> >> > My Backup policy is to take transaction log backups every 30 minutes...
>> >> > what if my server crashes at 10 minutes before a Transaction log backup
>> >> > takes place...
>> >> >
>> >> > e.g. Full Backup 1:00 pm
>> >> > Transaction Log Backup - 1:30 pm
>> >> > Transaction Log Backup - 2:00 pm
>> >> > Transaction Log Backup - 2:30 pm
>> >> >
>> >> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
>> >> > pm to 2:50 pm ...
>> >> >
>> >> > Please help
>> >> >
>> >> > Thanks
>> >> >
>> >>
>> >
>> >
>|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:
> Those are the steps basically, but not detach/attach. Rather stop SQL Server, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has been a KB article about this
> particular subject, and I'd be surprised if it has been removed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1157040981.529433.265080@.i3g2000cwc.googlegroups.com...
> > Thanks a ton for the help guys...
> >
> > I just wanted the procedure , of how to move ahead if i had the
> > transaction log .. I mean as Tibor said ... I would create a new DB ...
> > then de-tach it & attach the old transcation log file & then take a
> > backup & then restore that after the full backup of the database ...
> >
> > Is that that correct way .. or are there some more steps...
> >
> >
> >
> >
> > Tibor Karaszi wrote:
> >> In addition to Dan's comments:
> >>
> >> If the server is really toast, but the log file is intact:
> >> Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove
> >> the
> >> database files. "Slide" in your log file from the crashed database. Start that SQL Server, and
> >> the
> >> database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
> >> backup the log of a crashed database even if the whole machine goes south, assuming you get to
> >> the
> >> ldf file(s).
> >>
> >>
> >> Another comment:
> >>
> >> >> It backups the log file without truncating it ...
> >>
> >> Don't let the name of the option fool you. The purpose of this option is exactly what we have
> >> described here. The options is badly named, quite simply. This is why I suggested you to study
> >> the
> >> literature about the meaning of this option.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> >> news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
> >> >> But when the server crashes... how can i use that log file... i mean I
> >> >> wont be able to take a backup of that log file cause i would not have
> >> >> access to the server...
> >> >
> >> > You need to consider a number of scenarios in developing your recovery plan. Also consider your
> >> > tolerance for data loss (SLAs) and the cost of preventing such.
> >> >
> >> > The worst case is that you lose your data center and can recover using only off-site storage.
> >> > You
> >> > will likely lose some data in that scenario unless you engineer a (very expensive) system to
> >> > mirror all data real time to an offsite location. Similarly, you will loose data if you lose
> >> > your
> >> > log file for any reason; you can recover only to your last transaction log backup.
> >> >
> >> > If you lose data file(s) but your log is intact, you'll need to first recover to the point
> >> > where
> >> > SQL Server is running but the database is suspect due to the data file problem. This may
> >> > involve
> >> > correcting hardware problems, rebuilding the server OS and restoring SQL Server system
> >> > databases,
> >> > depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
> >> > NO_TRUNCATE to extract the last log you'll need for the restore process.
> >> >
> >> > In any case, you should schedule transaction log backups (stored on a different server) that
> >> > are
> >> > frequent enough to meet your data loss SLAs.
> >> >
> >> > --
> >> > Hope this helps.
> >> >
> >> > Dan Guzman
> >> > SQL Server MVP
> >> >
> >> > "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> > news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
> >> >> It backups the log file without truncating it ...
> >> >>
> >> >> But when the server crashes... how can i use that log file... i mean I
> >> >> wont be able to take a backup of that log file cause i would not have
> >> >> access to the server...
> >> >>
> >> >> Please can someone explain this
> >> >>
> >> >> Thanks
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> Tibor Karaszi wrote:
> >> >> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
> >> >> the
> >> >> purpose of this option is. Post back if you have further questions.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> >> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> >> >> > Hi
> >> >> >
> >> >> > My Backup policy is to take transaction log backups every 30 minutes...
> >> >> > what if my server crashes at 10 minutes before a Transaction log backup
> >> >> > takes place...
> >> >> >
> >> >> > e.g. Full Backup 1:00 pm
> >> >> > Transaction Log Backup - 1:30 pm
> >> >> > Transaction Log Backup - 2:00 pm
> >> >> > Transaction Log Backup - 2:30 pm
> >> >> >
> >> >> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> >> >> > pm to 2:50 pm ...
> >> >> >
> >> >> > Please help
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >>
> >> >
> >> >
> >|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:
> Those are the steps basically, but not detach/attach. Rather stop SQL Server, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has been a KB article about this
> particular subject, and I'd be surprised if it has been removed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1157040981.529433.265080@.i3g2000cwc.googlegroups.com...
> > Thanks a ton for the help guys...
> >
> > I just wanted the procedure , of how to move ahead if i had the
> > transaction log .. I mean as Tibor said ... I would create a new DB ...
> > then de-tach it & attach the old transcation log file & then take a
> > backup & then restore that after the full backup of the database ...
> >
> > Is that that correct way .. or are there some more steps...
> >
> >
> >
> >
> > Tibor Karaszi wrote:
> >> In addition to Dan's comments:
> >>
> >> If the server is really toast, but the log file is intact:
> >> Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove
> >> the
> >> database files. "Slide" in your log file from the crashed database. Start that SQL Server, and
> >> the
> >> database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
> >> backup the log of a crashed database even if the whole machine goes south, assuming you get to
> >> the
> >> ldf file(s).
> >>
> >>
> >> Another comment:
> >>
> >> >> It backups the log file without truncating it ...
> >>
> >> Don't let the name of the option fool you. The purpose of this option is exactly what we have
> >> described here. The options is badly named, quite simply. This is why I suggested you to study
> >> the
> >> literature about the meaning of this option.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> >> news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
> >> >> But when the server crashes... how can i use that log file... i mean I
> >> >> wont be able to take a backup of that log file cause i would not have
> >> >> access to the server...
> >> >
> >> > You need to consider a number of scenarios in developing your recovery plan. Also consider your
> >> > tolerance for data loss (SLAs) and the cost of preventing such.
> >> >
> >> > The worst case is that you lose your data center and can recover using only off-site storage.
> >> > You
> >> > will likely lose some data in that scenario unless you engineer a (very expensive) system to
> >> > mirror all data real time to an offsite location. Similarly, you will loose data if you lose
> >> > your
> >> > log file for any reason; you can recover only to your last transaction log backup.
> >> >
> >> > If you lose data file(s) but your log is intact, you'll need to first recover to the point
> >> > where
> >> > SQL Server is running but the database is suspect due to the data file problem. This may
> >> > involve
> >> > correcting hardware problems, rebuilding the server OS and restoring SQL Server system
> >> > databases,
> >> > depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
> >> > NO_TRUNCATE to extract the last log you'll need for the restore process.
> >> >
> >> > In any case, you should schedule transaction log backups (stored on a different server) that
> >> > are
> >> > frequent enough to meet your data loss SLAs.
> >> >
> >> > --
> >> > Hope this helps.
> >> >
> >> > Dan Guzman
> >> > SQL Server MVP
> >> >
> >> > "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> > news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
> >> >> It backups the log file without truncating it ...
> >> >>
> >> >> But when the server crashes... how can i use that log file... i mean I
> >> >> wont be able to take a backup of that log file cause i would not have
> >> >> access to the server...
> >> >>
> >> >> Please can someone explain this
> >> >>
> >> >> Thanks
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> Tibor Karaszi wrote:
> >> >> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
> >> >> the
> >> >> purpose of this option is. Post back if you have further questions.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> >> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> >> >> > Hi
> >> >> >
> >> >> > My Backup policy is to take transaction log backups every 30 minutes...
> >> >> > what if my server crashes at 10 minutes before a Transaction log backup
> >> >> > takes place...
> >> >> >
> >> >> > e.g. Full Backup 1:00 pm
> >> >> > Transaction Log Backup - 1:30 pm
> >> >> > Transaction Log Backup - 2:00 pm
> >> >> > Transaction Log Backup - 2:30 pm
> >> >> >
> >> >> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> >> >> > pm to 2:50 pm ...
> >> >> >
> >> >> > Please help
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >>
> >> >
> >> >
> >