I was mistakenly logged into the wrong server this morning and deleted all
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Wouldn't it be better to just do differentials hourly instead of log backups?
You could do that, but log backups has lot of advantages to diff backups. These includes
Point in time restore.
Ability to backup log if database becomes toast (zero data loss)
Ability to backup log, and restore the most recent db backup and all subsequent log backup sin case
you get a corruption of the database.
Etc.
In short, log backups has so many advantages so you don't want to trade it for diff backups just for
convenience. But sometimes you want to do both db, diff *and* log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OnZRT6xEHHA.4740@.TK2MSFTNGP03.phx.gbl...
> And while on the topic of backups, I setup this process based on input from this group. It just
> seems that it ends up having an inordinate amount of files to have to restore to get back online.
> Wouldn't it be better to just do differentials hourly instead of log backups? Then in a failure
> you'd never have more than 2 files to restore.
> Just wondering.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>>information!!
>> I've been doing weekly full backups with hourly log file backups.
>> I have 2 questions...
>> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
>> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
>> backup from last saturday it restores fine. When I go to restore the first log it tells me the
>> log is too recent and there is an earlier one with LSN###....
>> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
>> tried a RESTORE LOG using the full backup to get the log from there and it gives the same
>> message.
>> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
>> in that file with checkboxes to select the backupset to restore. My full backups do not show
>> this. The list is empty so I cannot go on. The restore seems successful by script. My script
>> statements originated from the "Generate Script" button when doing backups through the interface.
>> Am I missing something here? Below is a sample backup statement:
>> BACKUP DATABASE [Products]
>> FILEGROUP = N'PRIMARY'
>> TO DISK = @.strBackupFile
>> WITH NOFORMAT, NOINIT,
>> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>>
>> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the database
> in a recovering state and it cannot be accessed!!
Perhaps just a refresh problem in your GUI?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OppZhXxEHHA.3224@.TK2MSFTNGP04.phx.gbl...
> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the database
> in a recovering state and it cannot be accessed!! How can this happen? What have I done wrong
> here?
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>>information!!
>> I've been doing weekly full backups with hourly log file backups.
>> I have 2 questions...
>> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
>> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
>> backup from last saturday it restores fine. When I go to restore the first log it tells me the
>> log is too recent and there is an earlier one with LSN###....
>> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
>> tried a RESTORE LOG using the full backup to get the log from there and it gives the same
>> message.
>> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
>> in that file with checkboxes to select the backupset to restore. My full backups do not show
>> this. The list is empty so I cannot go on. The restore seems successful by script. My script
>> statements originated from the "Generate Script" button when doing backups through the interface.
>> Am I missing something here? Below is a sample backup statement:
>> BACKUP DATABASE [Products]
>> FILEGROUP = N'PRIMARY'
>> TO DISK = @.strBackupFile
>> WITH NOFORMAT, NOINIT,
>> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>>
>> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
> backup from last saturday it restores fine. When I go to restore the first log it tells me the
> log is too recent and there is an earlier one with LSN###....
You need to hunt down that log backup. You can check the backup history tables in the msdb database,
which can give you a clue where it is.
> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
> in that file with checkboxes to select the backupset to restore. My full backups do not show
> this. The list is empty so I cannot go on.
I suggest you read up on the RESTORE command and use RESTORE HEADERONLY and RESTORE FILELISTONLY so
you can manage a restore when the GUI is acting up on you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
> backup from last saturday it restores fine. When I go to restore the first log it tells me the
> log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
> tried a RESTORE LOG using the full backup to get the log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
> in that file with checkboxes to select the backupset to restore. My full backups do not show
> this. The list is empty so I cannot go on. The restore seems successful by script. My script
> statements originated from the "Generate Script" button when doing backups through the interface.
> Am I missing something here? Below is a sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
2326ac wrote:
