Drive lost - log files were on that drive - wondering about option

I've got a customer who re-booted their SQL server (SQL 2000) and when it
came up the F: drive - with all the log files - was gone.
They are going to attempt to get the F: drive back on-line - hopefully long
enough to detach the DB's and copy the LOG files to another drive.
If that does not work - we see two options at this moment.
We have backups, including TRANSACTION log backups every hour - last one was
at 3:00 pm today. The server was re-booted just before the 4:00 cycle - so
we have about 50 minutes of "unknown" work that was not backed up. We
realize we could restore all the DB's from the backup/transaction log backups
at get us back to 3:00 pm.
Other option would be to attempt to start the DB's with new empty logs. Not
so comfortable with this option. How do we check that the DB's were
closed/shutdown properly when the server rebooted - so we know that we aren't
going to be missing rollback/commit operations.
Thanks for any help you can offer - I might not be back till Sunday
afternoon to check this thread - but only have till Tuesday AM to get the box
back up and running.If you can't get your log files back, I suggest you first copy all data
files elsewhere for safe keeping. Then try to attach the databases. If a
database is at a consistent state and has a single log file, SQL Server will
recreate the log and no data will be lost.
For databases that cannot be attached, I suggest you restore from database
and transaction log backups. You can also salvage what you can for the lost
50 minutes by rebuilding logs from the copied data files, running DBCCs and
reconciling. However, you will not have logical or physical data integrity
after recreating logs for a 'dirty' database. You are wise to be
uncomfortable with using those databases as the live version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
> I've got a customer who re-booted their SQL server (SQL 2000) and when it
> came up the F: drive - with all the log files - was gone.
> They are going to attempt to get the F: drive back on-line - hopefully
> long
> enough to detach the DB's and copy the LOG files to another drive.
> If that does not work - we see two options at this moment.
> We have backups, including TRANSACTION log backups every hour - last one
> was
> at 3:00 pm today. The server was re-booted just before the 4:00 cycle -
> so
> we have about 50 minutes of "unknown" work that was not backed up. We
> realize we could restore all the DB's from the backup/transaction log
> backups
> at get us back to 3:00 pm.
> Other option would be to attempt to start the DB's with new empty logs.
> Not
> so comfortable with this option. How do we check that the DB's were
> closed/shutdown properly when the server rebooted - so we know that we
> aren't
> going to be missing rollback/commit operations.
> Thanks for any help you can offer - I might not be back till Sunday
> afternoon to check this thread - but only have till Tuesday AM to get the
> box
> back up and running.|||Dan - thanks for the response - kind of confirmed what I was expecting.
We do only have single LOG files...
How does the server know that the DB is in a consistent state? The server
was shutdown with the log files still accessible - it was the re-boot moment
that the F: drive disappered. If I can guarantee that the DB received all
logged data during shutdown (is that possible?) - then I can use these DB's
with empty logs - right?
Several of the DB's are our design - we have APPCONNECT tables that will
tell me who was connected and what time they jumped in. We also have TDATE
columns in all our tables that indicate GETDATE() timestamps of last
INSERT/UPDATE of the row.
Some of the DB's are not ours - so I'm going to have less ability to even do
a reconciliation between RESTORED DB and REATTACHED-WITH-EMPTY LOG DB's...
Thanks again!
"Dan Guzman" wrote:
> If you can't get your log files back, I suggest you first copy all data
> files elsewhere for safe keeping. Then try to attach the databases. If a
> database is at a consistent state and has a single log file, SQL Server will
> recreate the log and no data will be lost.
> For databases that cannot be attached, I suggest you restore from database
> and transaction log backups. You can also salvage what you can for the lost
> 50 minutes by rebuilding logs from the copied data files, running DBCCs and
> reconciling. However, you will not have logical or physical data integrity
> after recreating logs for a 'dirty' database. You are wise to be
> uncomfortable with using those databases as the live version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
> > I've got a customer who re-booted their SQL server (SQL 2000) and when it
> > came up the F: drive - with all the log files - was gone.
> >
> > They are going to attempt to get the F: drive back on-line - hopefully
> > long
> > enough to detach the DB's and copy the LOG files to another drive.
> >
> > If that does not work - we see two options at this moment.
> >
> > We have backups, including TRANSACTION log backups every hour - last one
> > was
> > at 3:00 pm today. The server was re-booted just before the 4:00 cycle -
> > so
> > we have about 50 minutes of "unknown" work that was not backed up. We
> > realize we could restore all the DB's from the backup/transaction log
> > backups
> > at get us back to 3:00 pm.
> >
> > Other option would be to attempt to start the DB's with new empty logs.
> > Not
> > so comfortable with this option. How do we check that the DB's were
> > closed/shutdown properly when the server rebooted - so we know that we
> > aren't
> > going to be missing rollback/commit operations.
> >
> > Thanks for any help you can offer - I might not be back till Sunday
> > afternoon to check this thread - but only have till Tuesday AM to get the
> > box
> > back up and running.
>
>|||> If I can guarantee that the DB received all
> logged data during shutdown (is that possible?) - then I can use these
> DB's
> with empty logs - right?
I don't recall the exact implementation details but the primary data file
contains information indicating whether or not a database was cleanly
shutdown. SQL Server checks this and will not create an new log file during
the attach unless it is safe to do so.
So the way to check it to try it - detach the suspect databases and then
attempt to attach specifying only the primary data file. If the shutdown
was clean, SQL Server will create a new log and you are good to go without
issues. If you get errors because the database shutdown wasn't clean, you
should restore from backups and accept the 50 minute data loss.
It's up to you whether you should go through the extra effort salvage and
data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:3FF3320E-7B51-4F0F-9F3F-806B24A9E7DB@.microsoft.com...
> Dan - thanks for the response - kind of confirmed what I was expecting.
> We do only have single LOG files...
> How does the server know that the DB is in a consistent state? The server
> was shutdown with the log files still accessible - it was the re-boot
> moment
> that the F: drive disappered. If I can guarantee that the DB received all
> logged data during shutdown (is that possible?) - then I can use these
> DB's
> with empty logs - right?
> Several of the DB's are our design - we have APPCONNECT tables that will
> tell me who was connected and what time they jumped in. We also have
> TDATE
> columns in all our tables that indicate GETDATE() timestamps of last
> INSERT/UPDATE of the row.
> Some of the DB's are not ours - so I'm going to have less ability to even
> do
> a reconciliation between RESTORED DB and REATTACHED-WITH-EMPTY LOG DB's...
> Thanks again!
> "Dan Guzman" wrote:
>> If you can't get your log files back, I suggest you first copy all data
>> files elsewhere for safe keeping. Then try to attach the databases. If
>> a
>> database is at a consistent state and has a single log file, SQL Server
>> will
>> recreate the log and no data will be lost.
>> For databases that cannot be attached, I suggest you restore from
>> database
>> and transaction log backups. You can also salvage what you can for the
>> lost
>> 50 minutes by rebuilding logs from the copied data files, running DBCCs
>> and
>> reconciling. However, you will not have logical or physical data
>> integrity
>> after recreating logs for a 'dirty' database. You are wise to be
>> uncomfortable with using those databases as the live version.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
>> > I've got a customer who re-booted their SQL server (SQL 2000) and when
>> > it
>> > came up the F: drive - with all the log files - was gone.
>> >
>> > They are going to attempt to get the F: drive back on-line - hopefully
>> > long
>> > enough to detach the DB's and copy the LOG files to another drive.
>> >
>> > If that does not work - we see two options at this moment.
>> >
>> > We have backups, including TRANSACTION log backups every hour - last
>> > one
>> > was
>> > at 3:00 pm today. The server was re-booted just before the 4:00
>> > cycle -
>> > so
>> > we have about 50 minutes of "unknown" work that was not backed up. We
>> > realize we could restore all the DB's from the backup/transaction log
>> > backups
>> > at get us back to 3:00 pm.
>> >
>> > Other option would be to attempt to start the DB's with new empty logs.
>> > Not
>> > so comfortable with this option. How do we check that the DB's were
>> > closed/shutdown properly when the server rebooted - so we know that we
>> > aren't
>> > going to be missing rollback/commit operations.
>> >
>> > Thanks for any help you can offer - I might not be back till Sunday
>> > afternoon to check this thread - but only have till Tuesday AM to get
>> > the
>> > box
>> > back up and running.
>>