Restoring SQL server from log files

I am using SQL serve. We have a probelm after our log backup have been damaged.
Problem is we have only log files for the daily backups. ie., after the
entire datbase files were backed up few days ago, it has been a daily
routine of
- backup log file with NT backup
- backup log with SQL 'BACKUP log" command
The SQL log backup is toast now.
The dates on the log files are different on the first and last day.
Replacing the log with the last days log files doesn't seem to work
spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
invalid..
spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
(database ID 7) could not recover. Contact Technical Support...
Is there a way we can just restore the daily log file copies from the first
day and roll forward the database ? Is there a 'RESTORE log.. WITH
NORECOVERY" option without using an archive? Or can I do roll forward by
restarting the server each time replacing the log file?
Any help is appreciated.
-JBThe only way to do any type if "incremental" restore is to restore from a database backup, then
restore an unbroken chain of log backups. I didn't quite follow your scenario, but that is what you
need to do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bertiew" <bertiew@.discussions.microsoft.com> wrote in message
news:3E26B852-C0D5-4424-A423-D1A2AC411873@.microsoft.com...
>I am using SQL serve. We have a probelm after our log backup have been damaged.
> Problem is we have only log files for the daily backups. ie., after the
> entire datbase files were backed up few days ago, it has been a daily
> routine of
> - backup log file with NT backup
> - backup log with SQL 'BACKUP log" command
> The SQL log backup is toast now.
> The dates on the log files are different on the first and last day.
> Replacing the log with the last days log files doesn't seem to work
> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> invalid..
> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> (database ID 7) could not recover. Contact Technical Support...
> Is there a way we can just restore the daily log file copies from the first
> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> NORECOVERY" option without using an archive? Or can I do roll forward by
> restarting the server each time replacing the log file?
> Any help is appreciated.
> -JB
>|||Hi
I am not clear on what you mean by
- backup log file with NT backup !
Is this the backup file created by
- backup log with SQL 'BACKUP log" command ?
When restoring the log you can use the NORECOVERY option when you have a
subsequent log file to restore.
Check out the topic "How to restore to the point of failure" and possibly
"How to restore to a point in time" and "Reducing Recovery Time" in Books
online.
John
"bertiew" wrote:
> I am using SQL serve. We have a probelm after our log backup have been damaged.
> Problem is we have only log files for the daily backups. ie., after the
> entire datbase files were backed up few days ago, it has been a daily
> routine of
> - backup log file with NT backup
> - backup log with SQL 'BACKUP log" command
> The SQL log backup is toast now.
> The dates on the log files are different on the first and last day.
> Replacing the log with the last days log files doesn't seem to work
> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> invalid..
> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> (database ID 7) could not recover. Contact Technical Support...
> Is there a way we can just restore the daily log file copies from the first
> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> NORECOVERY" option without using an archive? Or can I do roll forward by
> restarting the server each time replacing the log file?
> Any help is appreciated.
> -JB
>|||By NT backup, i mean the file backups created by the Windows backup utility.
Not the one created by the SQL backup command. In short they are copies of
the ldf files.
Can these be restored? With the NO RECOVERY option?
Thanks,
-JB
"John Bell" wrote:
> Hi
> I am not clear on what you mean by
> - backup log file with NT backup !
> Is this the backup file created by
> - backup log with SQL 'BACKUP log" command ?
> When restoring the log you can use the NORECOVERY option when you have a
> subsequent log file to restore.
> Check out the topic "How to restore to the point of failure" and possibly
> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> online.
> John
>
>
> "bertiew" wrote:
> > I am using SQL serve. We have a probelm after our log backup have been damaged.
> >
> > Problem is we have only log files for the daily backups. ie., after the
> > entire datbase files were backed up few days ago, it has been a daily
> > routine of
> > - backup log file with NT backup
> > - backup log with SQL 'BACKUP log" command
> >
> > The SQL log backup is toast now.
> >
> > The dates on the log files are different on the first and last day.
> > Replacing the log with the last days log files doesn't seem to work
> >
> > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > invalid..
> > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > (database ID 7) could not recover. Contact Technical Support...
> >
> > Is there a way we can just restore the daily log file copies from the first
> > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > NORECOVERY" option without using an archive? Or can I do roll forward by
> > restarting the server each time replacing the log file?
> >
> > Any help is appreciated.
> >
> > -JB
> >|||No,. See my other post. Any type of incremental restore requires you to have performed backups from
SQL Ser4ver and such restore starts with RESTORE DATABASE and then a number of RESTORE LOG. All from
SQL Server backup files, not raw database files.
Seems you have a good opportunity to revise your backup strategy.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bertiew" <bertiew@.discussions.microsoft.com> wrote in message
news:C74AD621-98A4-4DC3-A0D1-7D4F4188BAF5@.microsoft.com...
> By NT backup, i mean the file backups created by the Windows backup utility.
> Not the one created by the SQL backup command. In short they are copies of
> the ldf files.
> Can these be restored? With the NO RECOVERY option?
> Thanks,
> -JB
> "John Bell" wrote:
>> Hi
>> I am not clear on what you mean by
>> - backup log file with NT backup !
>> Is this the backup file created by
>> - backup log with SQL 'BACKUP log" command ?
>> When restoring the log you can use the NORECOVERY option when you have a
>> subsequent log file to restore.
>> Check out the topic "How to restore to the point of failure" and possibly
>> "How to restore to a point in time" and "Reducing Recovery Time" in Books
>> online.
>> John
>>
>>
>> "bertiew" wrote:
>> > I am using SQL serve. We have a probelm after our log backup have been damaged.
>> >
>> > Problem is we have only log files for the daily backups. ie., after the
>> > entire datbase files were backed up few days ago, it has been a daily
>> > routine of
>> > - backup log file with NT backup
>> > - backup log with SQL 'BACKUP log" command
>> >
>> > The SQL log backup is toast now.
>> >
>> > The dates on the log files are different on the first and last day.
>> > Replacing the log with the last days log files doesn't seem to work
>> >
>> > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
>> > invalid..
>> > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
>> > (database ID 7) could not recover. Contact Technical Support...
>> >
>> > Is there a way we can just restore the daily log file copies from the first
>> > day and roll forward the database ? Is there a 'RESTORE log.. WITH
>> > NORECOVERY" option without using an archive? Or can I do roll forward by
>> > restarting the server each time replacing the log file?
>> >
>> > Any help is appreciated.
>> >
>> > -JB
>> >|||Hi
Unless you have stopped SQL Server or detached the database before doing
your NT Backup, then it is unlikely that any mdf or ldf files on your NT
backup are any use. If you have both a data (mdf) and log (ldf) files it will
be possible to restore them info a different location and re-attach them
using sp_attach_db as a different database.
John
"bertiew" wrote:
> By NT backup, i mean the file backups created by the Windows backup utility.
> Not the one created by the SQL backup command. In short they are copies of
> the ldf files.
> Can these be restored? With the NO RECOVERY option?
> Thanks,
> -JB
> "John Bell" wrote:
> > Hi
> >
> > I am not clear on what you mean by
> > - backup log file with NT backup !
> > Is this the backup file created by
> > - backup log with SQL 'BACKUP log" command ?
> >
> > When restoring the log you can use the NORECOVERY option when you have a
> > subsequent log file to restore.
> >
> > Check out the topic "How to restore to the point of failure" and possibly
> > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > online.
> >
> > John
> >
> >
> >
> >
> >
> > "bertiew" wrote:
> >
> > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > >
> > > Problem is we have only log files for the daily backups. ie., after the
> > > entire datbase files were backed up few days ago, it has been a daily
> > > routine of
> > > - backup log file with NT backup
> > > - backup log with SQL 'BACKUP log" command
> > >
> > > The SQL log backup is toast now.
> > >
> > > The dates on the log files are different on the first and last day.
> > > Replacing the log with the last days log files doesn't seem to work
> > >
> > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > invalid..
> > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > (database ID 7) could not recover. Contact Technical Support...
> > >
> > > Is there a way we can just restore the daily log file copies from the first
> > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > restarting the server each time replacing the log file?
> > >
> > > Any help is appreciated.
> > >
> > > -JB
> > >|||Here's what I have observed
backups are
(1)data files, log files
(2)logfiles
(3)logfiles
(4)logfiles
(5)logfiles
(6)logfiles
I can make sqlsvr recover
with data files (1) and any of logfiles from (1), (2), or (3)
and the data is recovered in the state it was at the time of backup of the
logfile
But if I use logfiles from (4), I get the LSN error.
I am presuming the log file got reused as the log wrapped around. So can I
make sqlsvr do something like this?
a) Start sqlsvr with data files from (1) and log files from (3) <== This works
b) After recovery is done, shut down sqlsvr
c) Replace log with log files from (4), and recover again <== does'nt work.
Can I work around this?
Thanks,
-JB
"John Bell" wrote:
> Hi
> Unless you have stopped SQL Server or detached the database before doing
> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> be possible to restore them info a different location and re-attach them
> using sp_attach_db as a different database.
> John
> "bertiew" wrote:
> > By NT backup, i mean the file backups created by the Windows backup utility.
> > Not the one created by the SQL backup command. In short they are copies of
> > the ldf files.
> >
> > Can these be restored? With the NO RECOVERY option?
> >
> > Thanks,
> > -JB
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I am not clear on what you mean by
> > > - backup log file with NT backup !
> > > Is this the backup file created by
> > > - backup log with SQL 'BACKUP log" command ?
> > >
> > > When restoring the log you can use the NORECOVERY option when you have a
> > > subsequent log file to restore.
> > >
> > > Check out the topic "How to restore to the point of failure" and possibly
> > > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > > online.
> > >
> > > John
> > >
> > >
> > >
> > >
> > >
> > > "bertiew" wrote:
> > >
> > > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > > >
> > > > Problem is we have only log files for the daily backups. ie., after the
> > > > entire datbase files were backed up few days ago, it has been a daily
> > > > routine of
> > > > - backup log file with NT backup
> > > > - backup log with SQL 'BACKUP log" command
> > > >
> > > > The SQL log backup is toast now.
> > > >
> > > > The dates on the log files are different on the first and last day.
> > > > Replacing the log with the last days log files doesn't seem to work
> > > >
> > > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > > invalid..
> > > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > > (database ID 7) could not recover. Contact Technical Support...
> > > >
> > > > Is there a way we can just restore the daily log file copies from the first
> > > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > > restarting the server each time replacing the log file?
> > > >
> > > > Any help is appreciated.
> > > >
> > > > -JB
> > > >|||Hi
If these are NT backups then the best you can do is restore the files from
(1) and use sp_attach_db. If (1) is a Full SQL Server Backup and 2-6 are SQL
Log backups then follow the process already described by Tibor and also in
the topic "Reducing Recovery Time" in books online.
John
"bertiew" wrote:
> Here's what I have observed
> backups are
> (1)data files, log files
> (2)logfiles
> (3)logfiles
> (4)logfiles
> (5)logfiles
> (6)logfiles
> I can make sqlsvr recover
> with data files (1) and any of logfiles from (1), (2), or (3)
> and the data is recovered in the state it was at the time of backup of the
> logfile
> But if I use logfiles from (4), I get the LSN error.
> I am presuming the log file got reused as the log wrapped around. So can I
> make sqlsvr do something like this?
> a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> b) After recovery is done, shut down sqlsvr
> c) Replace log with log files from (4), and recover again <== does'nt work.
> Can I work around this?
> Thanks,
> -JB
> "John Bell" wrote:
> > Hi
> >
> > Unless you have stopped SQL Server or detached the database before doing
> > your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> > backup are any use. If you have both a data (mdf) and log (ldf) files it will
> > be possible to restore them info a different location and re-attach them
> > using sp_attach_db as a different database.
> >
> > John
> >
> > "bertiew" wrote:
> >
> > > By NT backup, i mean the file backups created by the Windows backup utility.
> > > Not the one created by the SQL backup command. In short they are copies of
> > > the ldf files.
> > >
> > > Can these be restored? With the NO RECOVERY option?
> > >
> > > Thanks,
> > > -JB
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I am not clear on what you mean by
> > > > - backup log file with NT backup !
> > > > Is this the backup file created by
> > > > - backup log with SQL 'BACKUP log" command ?
> > > >
> > > > When restoring the log you can use the NORECOVERY option when you have a
> > > > subsequent log file to restore.
> > > >
> > > > Check out the topic "How to restore to the point of failure" and possibly
> > > > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > > > online.
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "bertiew" wrote:
> > > >
> > > > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > > > >
> > > > > Problem is we have only log files for the daily backups. ie., after the
> > > > > entire datbase files were backed up few days ago, it has been a daily
> > > > > routine of
> > > > > - backup log file with NT backup
> > > > > - backup log with SQL 'BACKUP log" command
> > > > >
> > > > > The SQL log backup is toast now.
> > > > >
> > > > > The dates on the log files are different on the first and last day.
> > > > > Replacing the log with the last days log files doesn't seem to work
> > > > >
> > > > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > > > invalid..
> > > > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > > > (database ID 7) could not recover. Contact Technical Support...
> > > > >
> > > > > Is there a way we can just restore the daily log file copies from the first
> > > > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > > > restarting the server each time replacing the log file?
> > > > >
> > > > > Any help is appreciated.
> > > > >
> > > > > -JB
> > > > >|||bertiew wrote:
> Here's what I have observed
> backups are
> (1)data files, log files
> (2)logfiles
> (3)logfiles
> (4)logfiles
> (5)logfiles
> (6)logfiles
> I can make sqlsvr recover
> with data files (1) and any of logfiles from (1), (2), or (3)
> and the data is recovered in the state it was at the time of backup of the
> logfile
> But if I use logfiles from (4), I get the LSN error.
> I am presuming the log file got reused as the log wrapped around. So can I
> make sqlsvr do something like this?
> a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> b) After recovery is done, shut down sqlsvr
> c) Replace log with log files from (4), and recover again <== does'nt work.
> Can I work around this?
> Thanks,
> -JB
> "John Bell" wrote:
>> Hi
>> Unless you have stopped SQL Server or detached the database before doing
>> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
>> backup are any use. If you have both a data (mdf) and log (ldf) files it will
>> be possible to restore them info a different location and re-attach them
>> using sp_attach_db as a different database.
>> John
>> "bertiew" wrote:
>> By NT backup, i mean the file backups created by the Windows backup utility.
>> Not the one created by the SQL backup command. In short they are copies of
>> the ldf files.
>> Can these be restored? With the NO RECOVERY option?
>> Thanks,
>> -JB
>> "John Bell" wrote:
>> Hi
>> I am not clear on what you mean by
>> - backup log file with NT backup !
>> Is this the backup file created by
>> - backup log with SQL 'BACKUP log" command ?
>> When restoring the log you can use the NORECOVERY option when you have a
>> subsequent log file to restore.
>> Check out the topic "How to restore to the point of failure" and possibly
>> "How to restore to a point in time" and "Reducing Recovery Time" in Books
>> online.
>> John
>>
>>
>> "bertiew" wrote:
>> I am using SQL serve. We have a probelm after our log backup have been damaged.
>> Problem is we have only log files for the daily backups. ie., after the
>> entire datbase files were backed up few days ago, it has been a daily
>> routine of
>> - backup log file with NT backup
>> - backup log with SQL 'BACKUP log" command
>> The SQL log backup is toast now.
>> The dates on the log files are different on the first and last day.
>> Replacing the log with the last days log files doesn't seem to work
>> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
>> invalid..
>> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
>> (database ID 7) could not recover. Contact Technical Support...
>> Is there a way we can just restore the daily log file copies from the first
>> day and roll forward the database ? Is there a 'RESTORE log.. WITH
>> NORECOVERY" option without using an archive? Or can I do roll forward by
>> restarting the server each time replacing the log file?
>> Any help is appreciated.
>> -JB
Like the other ones, I'm a bit confused about what it is you've done.
An NT Backup will not backup neither a database file nor a logfile
unless you've stopped the SQL server service, or the files has been
detached. Also when you have NT Backups of logfiles, you can not restore
these using SQL server Restore command.
If I follow your above mentioned backup steps, I'd assume that you can
restore the datafile from (1), restore the logfile from (6) and then try
to run sp_attach_db and then hope it works. Here I assume that it's NT
Backup files you've got and that you haven't mingled around with SQL
server backup command in between.
As already mentioned, you should read up on BACKUP/RESTORE in Books On
Line, because is seem like you are missing some basic understanding of
how it works.
Regards
Steen|||Actually you can backup the files while they are in use, either using VSS or
by enabling SQL writer service (SQL 2005).
I did read up about the SQL backup and restore features. I understand that
my question is a bit unorthodox.
I can restore data 1 and log 3. My take is that in log backup 4 ,the log
files get reused. (as SQL server log circles back to the beginning of the
file). If I were to
draw a picture of the backup of the log files, I would presume it were
something like this (at least based on the SQL 2005 documentation of the log
architecture)
Log file backup 1 ===Log file backup 2 =========Log file backup 3 =============Log file backup 4 ++++++========== <-- ++ part of log file reused.
Log file backup 5 ++++++++++++++==
My question is this: Can I make SQL server somehow recover with log file
backup 3 (be done with the part of the log that got reused in log file backup
4) and then make it continue recovery after I switch log files? (the 'O'ther
database company does this - so maybe MS SQL too?)
Thanks,
-JB
"Steen Persson (DK)" wrote:
> bertiew wrote:
> > Here's what I have observed
> >
> > backups are
> > (1)data files, log files
> > (2)logfiles
> > (3)logfiles
> > (4)logfiles
> > (5)logfiles
> > (6)logfiles
> >
> > I can make sqlsvr recover
> > with data files (1) and any of logfiles from (1), (2), or (3)
> > and the data is recovered in the state it was at the time of backup of the
> > logfile
> >
> > But if I use logfiles from (4), I get the LSN error.
> >
> > I am presuming the log file got reused as the log wrapped around. So can I
> > make sqlsvr do something like this?
> > a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> > b) After recovery is done, shut down sqlsvr
> > c) Replace log with log files from (4), and recover again <== does'nt work.
> >
> > Can I work around this?
> >
> > Thanks,
> > -JB
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> Unless you have stopped SQL Server or detached the database before doing
> >> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> >> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> >> be possible to restore them info a different location and re-attach them
> >> using sp_attach_db as a different database.
> >>
> >> John
> >>
> >> "bertiew" wrote:
> >>
> >> By NT backup, i mean the file backups created by the Windows backup utility.
> >> Not the one created by the SQL backup command. In short they are copies of
> >> the ldf files.
> >>
> >> Can these be restored? With the NO RECOVERY option?
> >>
> >> Thanks,
> >> -JB
> >>
> >> "John Bell" wrote:
> >>
> >> Hi
> >>
> >> I am not clear on what you mean by
> >> - backup log file with NT backup !
> >> Is this the backup file created by
> >> - backup log with SQL 'BACKUP log" command ?
> >>
> >> When restoring the log you can use the NORECOVERY option when you have a
> >> subsequent log file to restore.
> >>
> >> Check out the topic "How to restore to the point of failure" and possibly
> >> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> >> online.
> >>
> >> John
> >>
> >>
> >>
> >>
> >>
> >> "bertiew" wrote:
> >>
> >> I am using SQL serve. We have a probelm after our log backup have been damaged.
> >>
> >> Problem is we have only log files for the daily backups. ie., after the
> >> entire datbase files were backed up few days ago, it has been a daily
> >> routine of
> >> - backup log file with NT backup
> >> - backup log with SQL 'BACKUP log" command
> >>
> >> The SQL log backup is toast now.
> >>
> >> The dates on the log files are different on the first and last day.
> >> Replacing the log with the last days log files doesn't seem to work
> >>
> >> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> >> invalid..
> >> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> >> (database ID 7) could not recover. Contact Technical Support...
> >>
> >> Is there a way we can just restore the daily log file copies from the first
> >> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> >> NORECOVERY" option without using an archive? Or can I do roll forward by
> >> restarting the server each time replacing the log file?
> >>
> >> Any help is appreciated.
> >>
> >> -JB
> >>
> Like the other ones, I'm a bit confused about what it is you've done.
> An NT Backup will not backup neither a database file nor a logfile
> unless you've stopped the SQL server service, or the files has been
> detached. Also when you have NT Backups of logfiles, you can not restore
> these using SQL server Restore command.
> If I follow your above mentioned backup steps, I'd assume that you can
> restore the datafile from (1), restore the logfile from (6) and then try
> to run sp_attach_db and then hope it works. Here I assume that it's NT
> Backup files you've got and that you haven't mingled around with SQL
> server backup command in between.
> As already mentioned, you should read up on BACKUP/RESTORE in Books On
> Line, because is seem like you are missing some basic understanding of
> how it works.
> Regards
> Steen
>|||Hi
You did not mention that you were using SQL 2005, SQL Writer or Volume
Shadow Copy Service in your original email, and it is still not clear if you
are actually using them.
To perform point in time recovery you will need to use Full Recovery model,
and the log file will not be overwritten unless it has been backed up i.e.
your situation will not occur. Once backed up SQL Server may re-use the
truncated inactive part of the log.
John
"bertiew" wrote:
> Actually you can backup the files while they are in use, either using VSS or
> by enabling SQL writer service (SQL 2005).
> I did read up about the SQL backup and restore features. I understand that
> my question is a bit unorthodox.
> I can restore data 1 and log 3. My take is that in log backup 4 ,the log
> files get reused. (as SQL server log circles back to the beginning of the
> file). If I were to
> draw a picture of the backup of the log files, I would presume it were
> something like this (at least based on the SQL 2005 documentation of the log
> architecture)
> Log file backup 1 ===> Log file backup 2 =========> Log file backup 3 =============> Log file backup 4 ++++++========== <-- ++ part of log file reused.
> Log file backup 5 ++++++++++++++==> My question is this: Can I make SQL server somehow recover with log file
> backup 3 (be done with the part of the log that got reused in log file backup
> 4) and then make it continue recovery after I switch log files? (the 'O'ther
> database company does this - so maybe MS SQL too?)
> Thanks,
> -JB
> "Steen Persson (DK)" wrote:
> > bertiew wrote:
> > > Here's what I have observed
> > >
> > > backups are
> > > (1)data files, log files
> > > (2)logfiles
> > > (3)logfiles
> > > (4)logfiles
> > > (5)logfiles
> > > (6)logfiles
> > >
> > > I can make sqlsvr recover
> > > with data files (1) and any of logfiles from (1), (2), or (3)
> > > and the data is recovered in the state it was at the time of backup of the
> > > logfile
> > >
> > > But if I use logfiles from (4), I get the LSN error.
> > >
> > > I am presuming the log file got reused as the log wrapped around. So can I
> > > make sqlsvr do something like this?
> > > a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> > > b) After recovery is done, shut down sqlsvr
> > > c) Replace log with log files from (4), and recover again <== does'nt work.
> > >
> > > Can I work around this?
> > >
> > > Thanks,
> > > -JB
> > >
> > > "John Bell" wrote:
> > >
> > >> Hi
> > >>
> > >> Unless you have stopped SQL Server or detached the database before doing
> > >> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> > >> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> > >> be possible to restore them info a different location and re-attach them
> > >> using sp_attach_db as a different database.
> > >>
> > >> John
> > >>
> > >> "bertiew" wrote:
> > >>
> > >> By NT backup, i mean the file backups created by the Windows backup utility.
> > >> Not the one created by the SQL backup command. In short they are copies of
> > >> the ldf files.
> > >>
> > >> Can these be restored? With the NO RECOVERY option?
> > >>
> > >> Thanks,
> > >> -JB
> > >>
> > >> "John Bell" wrote:
> > >>
> > >> Hi
> > >>
> > >> I am not clear on what you mean by
> > >> - backup log file with NT backup !
> > >> Is this the backup file created by
> > >> - backup log with SQL 'BACKUP log" command ?
> > >>
> > >> When restoring the log you can use the NORECOVERY option when you have a
> > >> subsequent log file to restore.
> > >>
> > >> Check out the topic "How to restore to the point of failure" and possibly
> > >> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > >> online.
> > >>
> > >> John
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "bertiew" wrote:
> > >>
> > >> I am using SQL serve. We have a probelm after our log backup have been damaged.
> > >>
> > >> Problem is we have only log files for the daily backups. ie., after the
> > >> entire datbase files were backed up few days ago, it has been a daily
> > >> routine of
> > >> - backup log file with NT backup
> > >> - backup log with SQL 'BACKUP log" command
> > >>
> > >> The SQL log backup is toast now.
> > >>
> > >> The dates on the log files are different on the first and last day.
> > >> Replacing the log with the last days log files doesn't seem to work
> > >>
> > >> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > >> invalid..
> > >> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > >> (database ID 7) could not recover. Contact Technical Support...
> > >>
> > >> Is there a way we can just restore the daily log file copies from the first
> > >> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > >> NORECOVERY" option without using an archive? Or can I do roll forward by
> > >> restarting the server each time replacing the log file?
> > >>
> > >> Any help is appreciated.
> > >>
> > >> -JB
> > >>
> >
> > Like the other ones, I'm a bit confused about what it is you've done.
> > An NT Backup will not backup neither a database file nor a logfile
> > unless you've stopped the SQL server service, or the files has been
> > detached. Also when you have NT Backups of logfiles, you can not restore
> > these using SQL server Restore command.
> >
> > If I follow your above mentioned backup steps, I'd assume that you can
> > restore the datafile from (1), restore the logfile from (6) and then try
> > to run sp_attach_db and then hope it works. Here I assume that it's NT
> > Backup files you've got and that you haven't mingled around with SQL
> > server backup command in between.
> >
> > As already mentioned, you should read up on BACKUP/RESTORE in Books On
> > Line, because is seem like you are missing some basic understanding of
> > how it works.
> >
> > Regards
> > Steen
> >|||bertiew wrote:
> Actually you can backup the files while they are in use, either using VSS or
> by enabling SQL writer service (SQL 2005).
> I did read up about the SQL backup and restore features. I understand that
> my question is a bit unorthodox.
> I can restore data 1 and log 3. My take is that in log backup 4 ,the log
> files get reused. (as SQL server log circles back to the beginning of the
> file). If I were to
> draw a picture of the backup of the log files, I would presume it were
> something like this (at least based on the SQL 2005 documentation of the log
> architecture)
> Log file backup 1 ===> Log file backup 2 =========> Log file backup 3 =============> Log file backup 4 ++++++========== <-- ++ part of log file reused.
> Log file backup 5 ++++++++++++++==> My question is this: Can I make SQL server somehow recover with log file
> backup 3 (be done with the part of the log that got reused in log file backup
> 4) and then make it continue recovery after I switch log files? (the 'O'ther
> database company does this - so maybe MS SQL too?)
> Thanks,
> -JB
>
I still think that you are mixing things up a little bit. An NT Backup
(or similar file backup) is not the same as a BACKUP LOG command in the
way it treats the file. I don't know the SQL writer option in detail,
but to me it looks like it's only enabling you to make a regular file
copy of your logfile. It doesn't truncate the log or anything else like
a regular BACKUP LOG command does. This means that if you use this to
backup your logfile, it will just take a backup of whatever is in your
logfile at that time, and the logfile will still contain "old" data
because it's not being truncated.
I've haven't tried the SQL writer service my self, but I still think
that the best way to set up a backup routine is to do a database backup
e.g. once a day, and then run log backups a number of times during the
day. These backup files can then be backed/copied to somewhere else
since they are now regular files. By doing this, you can always restore
your database backup and then logfiles up to the point in time you need.
It's my feeling that the setup you have now, is a mix of different ways
of backing up your data and that's why it's causing you troubles.
Regards
Steen