I understand that SQL 2000 does not have security policy enforcement, but is
there any work-around solution for this (disregard Windows NT Authority)?
like checking for users' passwords if they meet the requirement of number of
characters, digits, or even must be changed when they are expired.
Thank you in advance
PaulHi
If you are using a trusted connection this task is deligated to Windows.
That is why in general it is better to use this type of connection. If you
are using SQL Server authentication and have a had written application that
allows the user to change a password then your rules can be implemented
there. If you want to check existing users then you may want to check out
MBSA or the SQL Server Security advisor
http://www.microsoft.com/technet/se...s/mbsahome.mspx
http://www.microsoft.com/downloads/...&displaylang=en
Other products mentioned at
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=30 may also help.
John
"PaulManh" wrote:
> I understand that SQL 2000 does not have security policy enforcement, but
is
> there any work-around solution for this (disregard Windows NT Authority)?
> like checking for users' passwords if they meet the requirement of number
of
> characters, digits, or even must be changed when they are expired.
> Thank you in advance
> Paul
Showing posts with label policy. Show all posts
Showing posts with label policy. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
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
> >> >> >
> >> >>
> >> >
> >> >
> >
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
> >> >> >
> >> >>
> >> >
> >> >
> >
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. Thi
s 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:[vbcol=seagreen]
> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. T
his 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...|||> 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:
>|||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 th
at 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, a
ssuming you get to the
ldf file(s).
Another comment:
Don't let the name of the option fool you. The purpose of this option is exa
ctly what we have
described here. The options is badly named, quite simply. This is why I sugg
ested 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...[vbcol=seagreen]
> You need to consider a number of scenarios in developing your recovery pla
n. 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 onl
y off-site storage. You
> will likely lose some data in that scenario unless you engineer a (very ex
pensive) system to
> mirror all data real time to an offsite location. Similarly, you will loo
se 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 reco
ver to the point where
> SQL Server is running but the database is suspect due to the data file pro
blem. This may involve
> correcting hardware problems, rebuilding the server OS and restoring SQL S
erver system databases,
> depending on the particulars of the recovery scenario. You can then use t
he 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 diff
erent 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...
>|||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:[vbcol=seagreen]
> 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 th
at 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_TRUNCA
TE. 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:
>
> Don't let the name of the option fool you. The purpose of this option is e
xactly what we have
> described here. The options is badly named, quite simply. This is why I su
ggested 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...|||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:
>|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:[vbcol=seagreen]
> Those are the steps basically, but not detach/attach. Rather stop SQL Serv
er, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has bee
n 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...|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:[vbcol=seagreen]
> Those are the steps basically, but not detach/attach. Rather stop SQL Serv
er, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has bee
n 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...
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. Thi
s 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:[vbcol=seagreen]
> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. T
his 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...|||> 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:
>|||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 th
at 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, a
ssuming you get to the
ldf file(s).
Another comment:
Don't let the name of the option fool you. The purpose of this option is exa
ctly what we have
described here. The options is badly named, quite simply. This is why I sugg
ested 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...[vbcol=seagreen]
> You need to consider a number of scenarios in developing your recovery pla
n. 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 onl
y off-site storage. You
> will likely lose some data in that scenario unless you engineer a (very ex
pensive) system to
> mirror all data real time to an offsite location. Similarly, you will loo
se 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 reco
ver to the point where
> SQL Server is running but the database is suspect due to the data file pro
blem. This may involve
> correcting hardware problems, rebuilding the server OS and restoring SQL S
erver system databases,
> depending on the particulars of the recovery scenario. You can then use t
he 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 diff
erent 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...
>|||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:[vbcol=seagreen]
> 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 th
at 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_TRUNCA
TE. 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:
>
> Don't let the name of the option fool you. The purpose of this option is e
xactly what we have
> described here. The options is badly named, quite simply. This is why I su
ggested 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...|||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:
>|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:[vbcol=seagreen]
> Those are the steps basically, but not detach/attach. Rather stop SQL Serv
er, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has bee
n 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...|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:[vbcol=seagreen]
> Those are the steps basically, but not detach/attach. Rather stop SQL Serv
er, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has bee
n 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...
Subscribe to:
Posts (Atom)