database problem: log

Showing posts with label log. Show all posts

Wednesday, March 28, 2012

restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>

restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
Rick
Restore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegro ups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
sql

restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). Yo
u can try
sp_attach_single_file_db, but as you didn't detach first, it might not work.
If you're still out of
luck, open a case with MS Support and see if they have any tricks up their s
leeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>

restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong ?
TIA
atte,
Hernn
atte,
Hernn
ohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me ?
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong ?
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
|
|||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me ?
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong ?
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>

restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
atte,
Hernn
atte,
Hernnohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
||||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>

restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
--
atte,
Hernán
--
atte,
Hernánohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernán
|
| --
| atte,
| Hernán
|
||||Already replied in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernán
> "bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernán
> |
> | --
> | atte,
> | Hernán
> |
> |
>

Restoring Transaction Logs

I have a customer that needs to restore a database to
midnite on 12/31. They have a full database backup at
3pm, 2 transaction log without truncate backups and a
transaction log with truncate at midnite. I would assume
that you would run a restore on the full database backup
and then run a restore on the midnite trans log with
truncate with recover database selected. Would it be
necessary to include any of the transaction logs without
tuncate in the restore?
What is confusing to me is the transaction log backups
keep decreasing in size. I would think that if you don't
truncate, the sizes of the transaction log backups would
keep increasing until you truncated. Any help would be
appriciated.
ZackIn principle you will need to restore the most recent full db backup, =then ALL log backups since the full backup. The cause of your confusion =is (I guess) that backing up a log makes the space available for re-use, =The final log backup (that you describe as "transaction log with =truncate at midnite" is I expect not really a log backup at all, can you =confirm if this was taken via the SQL statement BACKUP LOG ... WITH =TRUNCATE ONLY, if so it has truncated the log not backed it up. In which =case the best you can get is to restore the full backup (WITH =NORECOVERY), the first log (WITH NORECOVERY) and the second log (WITH =RECOVERY).
If youre midnight log backup is a 'TRUNCATE ONLY" you are expposed from =a recovery point of view until 3pm the following day, since once the log =has been truncated it cannot be used to rollforward transactions until =the next full backup has been taken.
Hope that helps
Mike John
"Zack Godwin" <zgodwin@.freightsystemsinc.com> wrote in message =news:052b01c3d46b$0a7254c0$a001280a@.phx.gbl...
> I have a customer that needs to restore a database to > midnite on 12/31. They have a full database backup at > 3pm, 2 transaction log without truncate backups and a > transaction log with truncate at midnite. I would assume > that you would run a restore on the full database backup > and then run a restore on the midnite trans log with > truncate with recover database selected. Would it be > necessary to include any of the transaction logs without > tuncate in the restore? > > What is confusing to me is the transaction log backups > keep decreasing in size. I would think that if you don't > truncate, the sizes of the transaction log backups would > keep increasing until you truncated. Any help would be > appriciated.
> > Zack

Restoring Transaction log

Hi everyone,
First off, any help will be appreciated.
The scenario is that a chunk of data from one of the tables in a
database have been mistakenly deleted. I have done a complete backup of
the database and the backed up the log. I attempted restoring the
Database first with teh Norecpvery option and that worked. when I tried
restoring the log file with the STOPAT clause I get an error. I am
reproducing the script at the error below. Please help if you can
'Restoring the Database - Successful, with message shown below
RESTORE DATABASE MatriEdu2006
FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
file 1.
Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
1.
RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
(6.766 MB/sec).
'Restore Log - Failed, with error message shown below
RESTORE LOG MatriEdu2006
FROM DISK = 'C:\EducationMatrix_Log'
WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 537000000112000001, which
is too early to apply to the database. A more recent log backup that
includes LSN 553000000066000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
>>> On 12/10/2006 at 5:40 PM, in message
<1165797639.616739.281890@.80g2000cwy.googlegroups. com>,
highflier<nbarnard@.au.loreal.com> wrote:
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001,
> which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
You need to find a full backup made *before* the data loss, along with
all the logs made between that full and the time of the data loss.
|||This also indicates that something else is wrong here. If an LSN terminates
with a number that precedes the LSN of an earlier transaction log, that
transaction log needs to be skipped during a restore.
In your case below, the 537000000112000001 does exactly this so figure out
which TRN this is and when you run the restore and it comes time to restore
from this TRN log, skip it and go on to the next one. The database choked
on a transaction before one log completed but fixed itself during the next
one so all the stuff in that log is not needed as the transaction completed
during the execution of the next log.
Regards,
Jamie
"highflier" wrote:

> Hi everyone,
> First off, any help will be appreciated.
> The scenario is that a chunk of data from one of the tables in a
> database have been mistakenly deleted. I have done a complete backup of
> the database and the backed up the log. I attempted restoring the
> Database first with teh Norecpvery option and that worked. when I tried
> restoring the log file with the STOPAT clause I get an error. I am
> reproducing the script at the error below. Please help if you can
> 'Restoring the Database - Successful, with message shown below
> RESTORE DATABASE MatriEdu2006
> FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
> MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
> MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
> Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
> file 1.
> Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
> 1.
> RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
> (6.766 MB/sec).
>
> 'Restore Log - Failed, with error message shown below
> RESTORE LOG MatriEdu2006
> FROM DISK = 'C:\EducationMatrix_Log'
> WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001, which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
sql

Restoring Transaction log

Hi everyone,
First off, any help will be appreciated.
The scenario is that a chunk of data from one of the tables in a
database have been mistakenly deleted. I have done a complete backup of
the database and the backed up the log. I attempted restoring the
Database first with teh Norecpvery option and that worked. when I tried
restoring the log file with the STOPAT clause I get an error. I am
reproducing the script at the error below. Please help if you can
'Restoring the Database - Successful, with message shown below
RESTORE DATABASE MatriEdu2006
FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
file 1.
Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
1.
RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
(6.766 MB/sec).
'Restore Log - Failed, with error message shown below
RESTORE LOG MatriEdu2006
FROM DISK = 'C:\EducationMatrix_Log'
WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 537000000112000001, which
is too early to apply to the database. A more recent log backup that
includes LSN 553000000066000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.>>> On 12/10/2006 at 5:40 PM, in message
<1165797639.616739.281890@.80g2000cwy.googlegroups.com>,
highflier<nbarnard@.au.loreal.com> wrote:
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001,
> which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
You need to find a full backup made *before* the data loss, along with
all the logs made between that full and the time of the data loss.|||This also indicates that something else is wrong here. If an LSN terminates
with a number that precedes the LSN of an earlier transaction log, that
transaction log needs to be skipped during a restore.
In your case below, the 537000000112000001 does exactly this so figure out
which TRN this is and when you run the restore and it comes time to restore
from this TRN log, skip it and go on to the next one. The database choked
on a transaction before one log completed but fixed itself during the next
one so all the stuff in that log is not needed as the transaction completed
during the execution of the next log.
Regards,
Jamie
"highflier" wrote:

> Hi everyone,
> First off, any help will be appreciated.
> The scenario is that a chunk of data from one of the tables in a
> database have been mistakenly deleted. I have done a complete backup of
> the database and the backed up the log. I attempted restoring the
> Database first with teh Norecpvery option and that worked. when I tried
> restoring the log file with the STOPAT clause I get an error. I am
> reproducing the script at the error below. Please help if you can
> 'Restoring the Database - Successful, with message shown below
> RESTORE DATABASE MatriEdu2006
> FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
> MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
> MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
> Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
> file 1.
> Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
> 1.
> RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
> (6.766 MB/sec).
>
> 'Restore Log - Failed, with error message shown below
> RESTORE LOG MatriEdu2006
> FROM DISK = 'C:\EducationMatrix_Log'
> WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001, which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>

Restoring Transaction log

Restoring Tran. Log files

I need to restore a database on my test server from that on my production
server.
Here's what i've done so far:
1) Created a new db on my testing server named 'main_test'
2) Restored 'main_test' using .bak file from my production db.
Now, I have several transaction log files I wan to incorporate as well.
When I use Enterprise Manager and select my first trn log file and select
'Transaction Log' on the 'General' tab, I get the following error:
"The preceeding resotre operation did not specify WITH NORECOVERY or WITH
STANDBY....."
Should I use Query Analyzer for this? What is the syntax?Eric,
After applying the trnasaction log, go to "options" tab and select "Leave
database nonoperational, but able to restore additional transaction logs", o
r
"Leave database operational. No additional transaction logs can be restored"
if you are restoring the last transaction log backup.
AMB
"Eric" wrote:

> I need to restore a database on my test server from that on my production
> server.
> Here's what i've done so far:
> 1) Created a new db on my testing server named 'main_test'
> 2) Restored 'main_test' using .bak file from my production db.
> Now, I have several transaction log files I wan to incorporate as well.
> When I use Enterprise Manager and select my first trn log file and select
> 'Transaction Log' on the 'General' tab, I get the following error:
> "The preceeding resotre operation did not specify WITH NORECOVERY or WITH
> STANDBY....."
> Should I use Query Analyzer for this? What is the syntax?
>|||Correction,

> After applying the trnasaction log, go to "options" tab and select "Leave
Before applying ...
AMB
"Alejandro Mesa" wrote:
> Eric,
> After applying the trnasaction log, go to "options" tab and select "Leave
> database nonoperational, but able to restore additional transaction logs",
or
> "Leave database operational. No additional transaction logs can be restore
d"
> if you are restoring the last transaction log backup.
>
> AMB
> "Eric" wrote:
>

Monday, March 26, 2012

Restoring the source db with log shipping.

Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.
If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas

"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.
|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.
|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas

"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.

Restoring the source db with log shipping.

Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.sql

Restoring the source db with log shipping.

Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.

Friday, March 23, 2012

Restoring SQL2000 Transaction Logs to SQL2005

Hey all,

Just went through migrating to SQL 2005 as well and we have a need to apply log shipping between SQL 2000 and SQL 2005. Well not shipping per say in the automated fashion that SQL 2005 offers but rather we need to apply SQL 2000 transaction logs to a SQL 2005 instance. Though the database is still in SQL 2000 version 80 and the secondary database needs to be available in read only. (STANDBY MODE)

When I attempt to restore either a FULL backup or transaction log I get the error:

RESTORE DATABASE is terminating abnormally. This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY

The only way I could restore the FULL was by using the WITH RECOVERY OR NORECOVERY option during the restore process. The issue with this is I need the database in standby by mode in order to continue to append transaction logs.

Anybody have any thoughts how I might be able to get around this?

Thanks

Eric

The physical structure of the database has changed between SQL2000 and SQL 2005. Since the transaction logs contain low-level data, you cannot apply SQL 2000 transaction logs to a SQL 2005 database, or vice-versa

|||

Bummer, ya figure MS would build the 2005 transactional restore with a backward compatibility method, (in the since of restoring a database in STANDYBY mode) though what’s the point of keeping a database in (80) SQL 2000 mode if it truly is a semi-upgraded 2005 database.
Please correct me if I am wrong here. but I understand the reason why the 2000 versioned database is it can't be restored in stand by mode without created a log of is own during the upgrade.( not to repeat myself but if the destination database is in SQL 2000 format yet is attached to a SQL 2005 instance) it shouldn't have a issue, that is if SQL 2005 was truly backward compatible.

Thanks

|||

OK, There are several things here, and I'll try to address them all.

First off, there is a common misconception about compatibility modes and what they are.

Setting a compatibility mode governs how TSQL is interpereted and behaves, but does NOT have any impact to the physical structure of the database itself. So, a SQL 2005 database in 80 compatibility mode will have a SQL 2005 structure in the database files, but will respond to TSQL as if it were a SQL 2000 database. Meaning that any deprecated/removed keywords will be honored, and any behaviors which have been changed will act as they did in SQL 2000.

When you upgrade to SQL 2005, the database structures are upgraded at the point in time when the database is recovered.

So, you can continue to apply SQL 2000-based logs to the database as long as it is never recovered. Unfortunately, you cannot access it for any purpose other than applying logs. You cannot put it in standby mode, as that implies recovering the database, which triggers the update. You also cannot create a database snapshot without recovering/upgrading the database.

|||

Hmm, well that make since. i was a little shady on the compatibility modes though if the database structure does not change, they whats the big deal with attaching a once sql 2000 database to a 2005 instance and restoring a sql 2000 transaction file to it. ( during the attach process does the database automatically get upgraded?)

Thanks

|||

Part of attaching a database involves recovering it. This triggers the upgrading of the structures.

There is currently not a CREATE DATABASE FOR ATTACH WITH NORECOVERY.

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

Wednesday, March 28, 2012

restoring without log

restoring without log

restoring without log

restoring with StopBeforeMark

restoring with StopBeforeMark

restoring with StopBeforeMark

Restoring Transaction Logs

Restoring Transaction log

Restoring Transaction log

Restoring Transaction log

Restoring Tran. Log files

Monday, March 26, 2012

Restoring the source db with log shipping.

Restoring the source db with log shipping.

Restoring the source db with log shipping.

Friday, March 23, 2012

Restoring SQL2000 Transaction Logs to SQL2005

Restoring SQL server from log files

Restoring SQL server from log files

Restoring SQL server from log files

database problem

Blog Archive

About Me