Restoring SQL server from log files

I am using SQL serve. We have a probelm after our log backup have been damag
ed.
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 da
tabase backup, then
restore an unbroken chain of log backups. I didn't quite follow your scenari
o, 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 dama
ged.
> 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 firs
t
> 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 dam
aged.
> 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 firs
t
> 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:
[vbcol=seagreen]
> 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:
>|||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...[vbcol=seagreen]
> By NT backup, i mean the file backups created by the Windows backup utilit
y.
> 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
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 wil
l
be possible to restore them info a different location and re-attach them
using sp_attach_db as a different database.
John
"bertiew" wrote:
[vbcol=seagreen]
> By NT backup, i mean the file backups created by the Windows backup utilit
y.
> 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:
>|||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 wor
ks
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:
[vbcol=seagreen]
> 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 w
ill
> be possible to restore them info a different location and re-attach them
> using sp_attach_db as a different database.
> John
> "bertiew" wrote:
>|||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:
[vbcol=seagreen]
> 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 w
orks
> 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:
>|||bertiew wrote:[vbcol=seagreen]
> 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 w
orks
> 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:
>
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 backu
p
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:
> 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
>