Point in Time Recover

I had a hardware crash where my Datafile got corrupted but my Logfile was on
another array of disks so it was fine. Every two hours, I do a backup of my
Transaction Log. When my hardware crashed, it had be awhile since the last
Transaction log backup. Can I do a restore to a point it time. I can not
backup my current logfile because it has to be attached to a datafile an
online in order to be backed up. I tried to restore an old COLD backup of my
datafile and attach the logfile to it to try to back it up and that did not
work.. Any help would be appreciated. I need to recover to the point of
hardware failure..Hi
As the DB is corrupt, you can execute:
BACKUP LOG TO <disk device> WITH NO_TRUNCATE
You may need to put the DB into Emergency mode.
The NO_TRUNCATE tells SQL Server just to back the log to disk and not update
the DB with LSN (as it can't as it is corrupt) and not empty the log.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:A404F899-BDB1-44D4-BD8F-74FD74444155@.microsoft.com...
>I had a hardware crash where my Datafile got corrupted but my Logfile was
>on
> another array of disks so it was fine. Every two hours, I do a backup of
> my
> Transaction Log. When my hardware crashed, it had be awhile since the
> last
> Transaction log backup. Can I do a restore to a point it time. I can not
> backup my current logfile because it has to be attached to a datafile an
> online in order to be backed up. I tried to restore an old COLD backup of
> my
> datafile and attach the logfile to it to try to back it up and that did
> not
> work.. Any help would be appreciated. I need to recover to the point of
> hardware failure..|||My database is not attached. I can not attach it because the datafile is
corrupt. How do I issue the BACKUP LOG TO <disk device> WITH NO_TRUNCATE if
the database is not attached. How do I put the database in Emergency mode.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> As the DB is corrupt, you can execute:
> BACKUP LOG TO <disk device> WITH NO_TRUNCATE
> You may need to put the DB into Emergency mode.
> The NO_TRUNCATE tells SQL Server just to back the log to disk and not update
> the DB with LSN (as it can't as it is corrupt) and not empty the log.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:A404F899-BDB1-44D4-BD8F-74FD74444155@.microsoft.com...
> >I had a hardware crash where my Datafile got corrupted but my Logfile was
> >on
> > another array of disks so it was fine. Every two hours, I do a backup of
> > my
> > Transaction Log. When my hardware crashed, it had be awhile since the
> > last
> > Transaction log backup. Can I do a restore to a point it time. I can not
> > backup my current logfile because it has to be attached to a datafile an
> > online in order to be backed up. I tried to restore an old COLD backup of
> > my
> > datafile and attach the logfile to it to try to back it up and that did
> > not
> > work.. Any help would be appreciated. I need to recover to the point of
> > hardware failure..
>
>|||Hi
Never detach a DB if it is corrupt, until you have backed it up. Once it is
detached, you can forget backing it up as you can't attach it again.
From "Suspect" To "Emergency Mode"
Open Enterprise Manager,
Right click onto your server node - a SQL Server Properties dialog will
popup,
Please enable "Allow modifications to be made directly to the system
catalogs" option
Proceed to the sysdatabases table in the master database, locate your
database row, and put 32768 into its status column
Restart SQL Server and refresh Enterprise Manager
The basic rule of recoverability is that you have to be willing to loose at
least the data in the period equal to your transaction log backup interval.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:101F3875-D177-4840-8EEA-061F719B35B1@.microsoft.com...
> My database is not attached. I can not attach it because the datafile is
> corrupt. How do I issue the BACKUP LOG TO <disk device> WITH NO_TRUNCATE
> if
> the database is not attached. How do I put the database in Emergency
> mode.
> Thanks
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> As the DB is corrupt, you can execute:
>> BACKUP LOG TO <disk device> WITH NO_TRUNCATE
>> You may need to put the DB into Emergency mode.
>> The NO_TRUNCATE tells SQL Server just to back the log to disk and not
>> update
>> the DB with LSN (as it can't as it is corrupt) and not empty the log.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
>> news:A404F899-BDB1-44D4-BD8F-74FD74444155@.microsoft.com...
>> >I had a hardware crash where my Datafile got corrupted but my Logfile
>> >was
>> >on
>> > another array of disks so it was fine. Every two hours, I do a backup
>> > of
>> > my
>> > Transaction Log. When my hardware crashed, it had be awhile since the
>> > last
>> > Transaction log backup. Can I do a restore to a point it time. I can
>> > not
>> > backup my current logfile because it has to be attached to a datafile
>> > an
>> > online in order to be backed up. I tried to restore an old COLD backup
>> > of
>> > my
>> > datafile and attach the logfile to it to try to back it up and that did
>> > not
>> > work.. Any help would be appreciated. I need to recover to the point
>> > of
>> > hardware failure..
>>|||The hard drive crashed that held the datafiles for my USER database as well
as the MASTER database. I had no choice but to restore the MASTER database
which lost all knowledge of my USER databases which meant I had to re-Attache
them. But they also were corrupt. The only drive that was not corrupt was
the drive that held the TransLogs. I was hoping there was a way to get a
backup of them from a database that can not be attached..
Thanks
Jason Greene
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Never detach a DB if it is corrupt, until you have backed it up. Once it is
> detached, you can forget backing it up as you can't attach it again.
> From "Suspect" To "Emergency Mode"
> Open Enterprise Manager,
> Right click onto your server node - a SQL Server Properties dialog will
> popup,
> Please enable "Allow modifications to be made directly to the system
> catalogs" option
> Proceed to the sysdatabases table in the master database, locate your
> database row, and put 32768 into its status column
> Restart SQL Server and refresh Enterprise Manager
>
> The basic rule of recoverability is that you have to be willing to loose at
> least the data in the period equal to your transaction log backup interval.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:101F3875-D177-4840-8EEA-061F719B35B1@.microsoft.com...
> > My database is not attached. I can not attach it because the datafile is
> > corrupt. How do I issue the BACKUP LOG TO <disk device> WITH NO_TRUNCATE
> > if
> > the database is not attached. How do I put the database in Emergency
> > mode.
> >
> > Thanks
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> As the DB is corrupt, you can execute:
> >>
> >> BACKUP LOG TO <disk device> WITH NO_TRUNCATE
> >>
> >> You may need to put the DB into Emergency mode.
> >>
> >> The NO_TRUNCATE tells SQL Server just to back the log to disk and not
> >> update
> >> the DB with LSN (as it can't as it is corrupt) and not empty the log.
> >>
> >> Regards
> >> --
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@.epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> >> news:A404F899-BDB1-44D4-BD8F-74FD74444155@.microsoft.com...
> >> >I had a hardware crash where my Datafile got corrupted but my Logfile
> >> >was
> >> >on
> >> > another array of disks so it was fine. Every two hours, I do a backup
> >> > of
> >> > my
> >> > Transaction Log. When my hardware crashed, it had be awhile since the
> >> > last
> >> > Transaction log backup. Can I do a restore to a point it time. I can
> >> > not
> >> > backup my current logfile because it has to be attached to a datafile
> >> > an
> >> > online in order to be backed up. I tried to restore an old COLD backup
> >> > of
> >> > my
> >> > datafile and attach the logfile to it to try to back it up and that did
> >> > not
> >> > work.. Any help would be appreciated. I need to recover to the point
> >> > of
> >> > hardware failure..
> >>
> >>
> >>
>
>|||How do you put your database in Emergency mode.. Or how do you force it from
Offline to something else so you can issue the Backup Log command.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> As the DB is corrupt, you can execute:
> BACKUP LOG TO <disk device> WITH NO_TRUNCATE
> You may need to put the DB into Emergency mode.
> The NO_TRUNCATE tells SQL Server just to back the log to disk and not update
> the DB with LSN (as it can't as it is corrupt) and not empty the log.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:A404F899-BDB1-44D4-BD8F-74FD74444155@.microsoft.com...
> >I had a hardware crash where my Datafile got corrupted but my Logfile was
> >on
> > another array of disks so it was fine. Every two hours, I do a backup of
> > my
> > Transaction Log. When my hardware crashed, it had be awhile since the
> > last
> > Transaction log backup. Can I do a restore to a point it time. I can not
> > backup my current logfile because it has to be attached to a datafile an
> > online in order to be backed up. I tried to restore an old COLD backup of
> > my
> > datafile and attach the logfile to it to try to back it up and that did
> > not
> > work.. Any help would be appreciated. I need to recover to the point of
> > hardware failure..
>
>|||If you have a tlog file for a database, and you have been doing regular tlog backups for that
database, you can to the "last" backup from this ldf file even if the whole installation is lost.
Just create a database using the same filenames on another machine. Stop that SQL Server. Delete the
newly created empty database file. Slide in your old ldf file where the new database had its ldf
file. Start the SQL Server. Do a log backup using the NO_TRUNCATE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:DF541978-4CE6-42D1-85E4-AA797E907C25@.microsoft.com...
> The hard drive crashed that held the datafiles for my USER database as well
> as the MASTER database. I had no choice but to restore the MASTER database
> which lost all knowledge of my USER databases which meant I had to re-Attache
> them. But they also were corrupt. The only drive that was not corrupt was
> the drive that held the TransLogs. I was hoping there was a way to get a
> backup of them from a database that can not be attached..
> Thanks
> Jason Greene
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Never detach a DB if it is corrupt, until you have backed it up. Once it is
>> detached, you can forget backing it up as you can't attach it again.
>> From "Suspect" To "Emergency Mode"
>> Open Enterprise Manager,
>> Right click onto your server node - a SQL Server Properties dialog will
>> popup,
>> Please enable "Allow modifications to be made directly to the system
>> catalogs" option
>> Proceed to the sysdatabases table in the master database, locate your
>> database row, and put 32768 into its status column
>> Restart SQL Server and refresh Enterprise Manager
>>
>> The basic rule of recoverability is that you have to be willing to loose at
>> least the data in the period equal to your transaction log backup interval.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
>> news:101F3875-D177-4840-8EEA-061F719B35B1@.microsoft.com...
>> > My database is not attached. I can not attach it because the datafile is
>> > corrupt. How do I issue the BACKUP LOG TO <disk device> WITH NO_TRUNCATE
>> > if
>> > the database is not attached. How do I put the database in Emergency
>> > mode.
>> >
>> > Thanks
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> As the DB is corrupt, you can execute:
>> >>
>> >> BACKUP LOG TO <disk device> WITH NO_TRUNCATE
>> >>
>> >> You may need to put the DB into Emergency mode.
>> >>
>> >> The NO_TRUNCATE tells SQL Server just to back the log to disk and not
>> >> update
>> >> the DB with LSN (as it can't as it is corrupt) and not empty the log.
>> >>
>> >> Regards
>> >> --
>> >> Mike Epprecht, Microsoft SQL Server MVP
>> >> Zurich, Switzerland
>> >>
>> >> IM: mike@.epprecht.net
>> >>
>> >> MVP Program: http://www.microsoft.com/mvp
>> >>
>> >> Blog: http://www.msmvps.com/epprecht/
>> >>
>> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
>> >> news:A404F899-BDB1-44D4-BD8F-74FD74444155@.microsoft.com...
>> >> >I had a hardware crash where my Datafile got corrupted but my Logfile
>> >> >was
>> >> >on
>> >> > another array of disks so it was fine. Every two hours, I do a backup
>> >> > of
>> >> > my
>> >> > Transaction Log. When my hardware crashed, it had be awhile since the
>> >> > last
>> >> > Transaction log backup. Can I do a restore to a point it time. I can
>> >> > not
>> >> > backup my current logfile because it has to be attached to a datafile
>> >> > an
>> >> > online in order to be backed up. I tried to restore an old COLD backup
>> >> > of
>> >> > my
>> >> > datafile and attach the logfile to it to try to back it up and that did
>> >> > not
>> >> > work.. Any help would be appreciated. I need to recover to the point
>> >> > of
>> >> > hardware failure..
>> >>
>> >>
>> >>
>>|||Hi,
How to set to Emergency Mode
--­­-
Use emergency mode (also called bypass mode) to recover data when SQL Server
marks a database suspect and the automatic recovery will fail.
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data. To get your data, use this
script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Thanks
Hari
SQL Server MVP
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:B40D6738-4AA5-4680-8F94-468E329606B4@.microsoft.com...
> How do you put your database in Emergency mode.. Or how do you force it
> from
> Offline to something else so you can issue the Backup Log command.
>
> Thanks
> Hi,
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> As the DB is corrupt, you can execute:
>> BACKUP LOG TO <disk device> WITH NO_TRUNCATE
>> You may need to put the DB into Emergency mode.
>> The NO_TRUNCATE tells SQL Server just to back the log to disk and not
>> update
>> the DB with LSN (as it can't as it is corrupt) and not empty the log.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
>> news:A404F899-BDB1-44D4-BD8F-74FD74444155@.microsoft.com...
>> >I had a hardware crash where my Datafile got corrupted but my Logfile
>> >was
>> >on
>> > another array of disks so it was fine. Every two hours, I do a backup
>> > of
>> > my
>> > Transaction Log. When my hardware crashed, it had be awhile since the
>> > last
>> > Transaction log backup. Can I do a restore to a point it time. I can
>> > not
>> > backup my current logfile because it has to be attached to a datafile
>> > an
>> > online in order to be backed up. I tried to restore an old COLD backup
>> > of
>> > my
>> > datafile and attach the logfile to it to try to back it up and that did
>> > not
>> > work.. Any help would be appreciated. I need to recover to the point
>> > of
>> > hardware failure..
>>|||I can not use this script because the database will not attach. I get a
'corrupt data' error on the datafile.
"Hari Prasad" wrote:
> Hi,
> How to set to Emergency Mode
> --­­-
> Use emergency mode (also called bypass mode) to recover data when SQL Server
> marks a database suspect and the automatic recovery will fail.
>
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data. To get your data, use this
> script:
>
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:B40D6738-4AA5-4680-8F94-468E329606B4@.microsoft.com...
> > How do you put your database in Emergency mode.. Or how do you force it
> > from
> > Offline to something else so you can issue the Backup Log command.
> >
> >
> > Thanks
> > Hi,
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> As the DB is corrupt, you can execute:
> >>
> >> BACKUP LOG TO <disk device> WITH NO_TRUNCATE
> >>
> >> You may need to put the DB into Emergency mode.
> >>
> >> The NO_TRUNCATE tells SQL Server just to back the log to disk and not
> >> update
> >> the DB with LSN (as it can't as it is corrupt) and not empty the log.
> >>
> >> Regards
> >> --
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@.epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> >> news:A404F899-BDB1-44D4-BD8F-74FD74444155@.microsoft.com...
> >> >I had a hardware crash where my Datafile got corrupted but my Logfile
> >> >was
> >> >on
> >> > another array of disks so it was fine. Every two hours, I do a backup
> >> > of
> >> > my
> >> > Transaction Log. When my hardware crashed, it had be awhile since the
> >> > last
> >> > Transaction log backup. Can I do a restore to a point it time. I can
> >> > not
> >> > backup my current logfile because it has to be attached to a datafile
> >> > an
> >> > online in order to be backed up. I tried to restore an old COLD backup
> >> > of
> >> > my
> >> > datafile and attach the logfile to it to try to back it up and that did
> >> > not
> >> > work.. Any help would be appreciated. I need to recover to the point
> >> > of
> >> > hardware failure..
> >>
> >>
> >>
>
>