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 backu
ps?
You could do that, but log backups has lot of advantages to diff backups. Th
ese 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 subsequ
ent 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 f
or 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 fro
m this group. It just
> seems that it ends up having an inordinate amount of files to have to rest
ore to get back online.
> Wouldn't it be better to just do differentials hourly instead of log backu
ps? 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...
>|||> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the d
atabase
> 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 s
till shows the database
> in a recovering state and it cannot be accessed!! How can this happen? Wh
at have I done wrong
> here?
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>|||> 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. Whe
n I restore the full
> backup from last saturday it restores fine. When I go to restore the firs
t 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 tabl
es 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 y
ou 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 mor
ning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. Whe
n I restore the full
> backup from last saturday it restores fine. When I go to restore the firs
t 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 y
ou 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!!!
>
Showing posts with label logged. Show all posts
Showing posts with label logged. Show all posts
Monday, March 12, 2012
HELP ! Production down and backups not restoring properly!
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!!!
>
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!!!
>
HELP ! Production down and backups not restoring properly!
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!!!
>
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!!!
>
Friday, March 9, 2012
Help - Update Field in SQL Server
I am trying to write a password change page for my website. I want logged in users to be able to change their password which is stored in a SQL table. I was able to get the following code to work
Dim myCommand As New SqlCommand( _
"cmdChangePassword @.Email='"+request("email")+"', @.OldPassword='"+TextBox1.text+"',@.NewPassword='"+TextBox3.text+"'", myConnection)
This information is passed to a stored procedure which updates the table. However, I want to have a return value that shows that the password was changed. I changed the code as follows:
Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50).Value = request("email")
Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = "+TextBox1.text+"
Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = "+TextBox3.text+"
With this new code the password is not being changed. However, I am not receiving any errors.
BDyou can check your db if the pwd is being changed, to see if the sp is working properly.
(1) in your SP, you can return an integer 0/1 for success or failure and appropriately throw a msgbox saying pswd has been changed.
and modify your code slightly and have an output parameter.
or
(2) query the db again with username=@.username and pwd=@.newpswd and see if you get any records. to make sure you get the xact record you can add more conditions. so if you do get a record, then the db has been updated with the new pswd.
apparently, this one requires an xtra trip to the db.
HTH|||The SP is set to return the integer. I haven't added that code yet. I have tested the SP and know that it works. However, the password is not being changed. The email address, old password and new password need to be passed to the SP. As long as the email address and old password match, the password will be changed. I am assuming there is something wrong with my code and that those items are not being passed successfully.|||heres an example of the code that i am using
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
Dim myDataReader As SqlDataReader
myConnection = New SqlConnection("server=local;database=Northwind;Integrated Security=SSPI ")
myCommand = New SqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = "finalize_movein"
myCommand.CommandType = CommandType.StoredProcedure
'input parameter
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.userid"
myParameter.Direction = ParameterDirection.Input
myParameter.SqlDbType = SqlDbType.int
myParameter.Value = id
myCommand.Parameters.Add(myParameter)'output parameter:
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.finalcnum"
myParameter.Direction = ParameterDirection.Output
myParameter.SqlDbType = SqlDbType.int
myCommand.Parameters.Add(myParameter)' Open the connection to the SQL Server
myConnection.Open()
myCommand.ExecuteNonQuery()return convert.toint32((myCommand.Parameters("@.finalcnum").Value))
myconnection.close
and have your SP defined like this :
CREATE PROCEDURE finalize_movein (@.userid int ,@.finalcnum bigint OUTPUT) as
begin...
HTH|||BD - please wrap your code in CODE tags to make it distinguishable from your comments.
It looks to me that your problem is in the way you are setting your parameter values. For some reason you have surrounded them with double quotes(") and plus signs(+). Remove those and you should have better luck. I changed the way you were setting up your @.email parameter to make it more consistent with the rest of your code, plus I added a ReturnValue parameter:
|||That worked! Thanks for your help!
Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedureDim myParm0 As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Integer)
myParm0.Direction = ParameterDirection.ReturnValueDim myParm1 As SqlParameter = myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = request("email")Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = TextBox1.textDim myParm3 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm3.Direction = ParameterDirection.Input
myParm3.Value = TextBox3.text
Brian|||Sorry, I have one more question. I am now able to change the password. However, I want to display a message to the user that the password has been changed. I have the following variable defined.
Code
Dim Result As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int)
Result.Direction = ParameterDirection.ReturnValue
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
if Result.Value>0 then MSG.text= "Your password has been changed"
myCommand.Connection.Close()
End Code
In my html I have the following:
<asp:Label id="MSG" runat="server"></asp:Label
Any ideas what I am missing?
Brian|||Normally, the ReturnValue will be 0 if there are no errors. I don't know what your stored procedure looks like, but assuming it follows normal practice, this line:
if Result.Value>0 then MSG.text= "Your password has been changed"should be this:
if myCommand.Parameters("@.ReturnValue").Value=0 then MSG.text= "Your password has been changed"Terri|||That's perfect. Thanks again for everybody's help.
Brian
Subscribe to:
Posts (Atom)