Dear all
Having read BOL, I was of the understanding that if a machine was lost
(anything but disk failure) then it was very difficult to recover the data.
The reason being that the data- and log-files were still "attached" to the
dead SQLServer and needed to be detached from it before they could be used
again; a difficult operation if the machine is dead.
However, someone suggested that this was not the case. If a machine dies
then it is a simple operation to physically disconnect the disks from the
dead machine and connect them to a new machine and continue working. This
assumes the Standard Edition of SQLServer (i.e. no clustering involved).
I can test this, but it will take a few days to set the equipment up, so I
wondered in the mean time whether anyone could confirm whether this was the
case. If so, then presumably a SAN would present an even simplier solution,
particularly if the disk set is a RAID5+1 configuration?
Thanks in advance
GriffGriff,
The SQL Server documentation say that you can attach a database if you first detached it.
You *might* be able to attach is even if you didn't detached it first, but consider this as one of
those "lucky" situations. It is not guaranteed or documented.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Griff" <Howling@.The.Moon> wrote in message news:e7lkMBAkEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Dear all
> Having read BOL, I was of the understanding that if a machine was lost
> (anything but disk failure) then it was very difficult to recover the data.
> The reason being that the data- and log-files were still "attached" to the
> dead SQLServer and needed to be detached from it before they could be used
> again; a difficult operation if the machine is dead.
> However, someone suggested that this was not the case. If a machine dies
> then it is a simple operation to physically disconnect the disks from the
> dead machine and connect them to a new machine and continue working. This
> assumes the Standard Edition of SQLServer (i.e. no clustering involved).
> I can test this, but it will take a few days to set the equipment up, so I
> wondered in the mean time whether anyone could confirm whether this was the
> case. If so, then presumably a SAN would present an even simplier solution,
> particularly if the disk set is a RAID5+1 configuration?
> Thanks in advance
> Griff
>|||Hi,
What is a server failure?
Which part(s) need to fail to give a server failure? CPU? Memory?
Motherboard? Disc Controller? Boot Disc? Master Database? Data drives? Log
Drives? PSU? etc?
You are highlighting the importance of DP (I prefer DP to DR - Disaster
Prevention is better than Cure). So, what can fail, what can you do to
prevent it? What do you do if it happens? Have you rehearsed for it? Does
the process work?
So a PSU blows up and takes the motherboard and CPU(s) with it. The
system/boot disc drive goes at the same time. Sounds like a server failure
to me. What do you do? Have DP? Then you may already have a standby server,
backup copies of databases on other computers, be using log shipping, and
have only to switch to standby... It is always better to be prepared before
the event than to rely on a toolkit to fish you out of some scenario after
an unpredictable event.
Recovering SQL Server databases in scenarios such as this is perhaps the
poorest documented part of SQL Server. What happens if the log drive dies at
run time? Or the data drive? Or the RAID controller? (That happened to me a
few weeks ago and was not pleasant, we did have DP in place however). We all
know the theory, but the answer is if you wish to get things back up and
running with least data-loss as the system is supposed to be designed, you
seem to have no choice but to ring MS 'cos if you ask here that is what they
will tell you to do.
So rule #1 for SQL Server DP: Don't lose the data.
Comments / constructive criticism welcome.
- Tim
"Griff" <Howling@.The.Moon> wrote in message
news:e7lkMBAkEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Dear all
> Having read BOL, I was of the understanding that if a machine was lost
> (anything but disk failure) then it was very difficult to recover the
> data.
> The reason being that the data- and log-files were still "attached" to the
> dead SQLServer and needed to be detached from it before they could be used
> again; a difficult operation if the machine is dead.
> However, someone suggested that this was not the case. If a machine dies
> then it is a simple operation to physically disconnect the disks from the
> dead machine and connect them to a new machine and continue working. This
> assumes the Standard Edition of SQLServer (i.e. no clustering involved).
> I can test this, but it will take a few days to set the equipment up, so I
> wondered in the mean time whether anyone could confirm whether this was
> the
> case. If so, then presumably a SAN would present an even simplier
> solution,
> particularly if the disk set is a RAID5+1 configuration?
> Thanks in advance
> Griff
>|||Hi Tim
I agree with you completely. We use a server with RAID5+1 disks, and
implement log shipping onto a stand-by server. However, our consultant
pointed out that this provides us with a way of getting the service up
really quickly, but with loss of data (back to the last log that was
shipped). He suggested that the way to lose no data (providing that the
disks were not damaged) was to simply to disconnect the scsi cable to the
down server and connect them to the standby server and that way no data was
lost (service might take longer to resume, but down time in our business is
perceived as better than loss of data). I just wished to question whether
this really was technically possible/reliable.
Griff|||Griff,
See my earlier reply. I suggest you ask the consultant where his strategy is documented. That should
end the discussion.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Griff" <Howling@.The.Moon> wrote in message news:OT$GeZBkEHA.1644@.tk2msftngp13.phx.gbl...
> Hi Tim
> I agree with you completely. We use a server with RAID5+1 disks, and
> implement log shipping onto a stand-by server. However, our consultant
> pointed out that this provides us with a way of getting the service up
> really quickly, but with loss of data (back to the last log that was
> shipped). He suggested that the way to lose no data (providing that the
> disks were not damaged) was to simply to disconnect the scsi cable to the
> down server and connect them to the standby server and that way no data was
> lost (service might take longer to resume, but down time in our business is
> perceived as better than loss of data). I just wished to question whether
> this really was technically possible/reliable.
> Griff
>sql
Showing posts with label bol. Show all posts
Showing posts with label bol. Show all posts
Friday, March 30, 2012
Loss of server
Dear all
Having read BOL, I was of the understanding that if a machine was lost
(anything but disk failure) then it was very difficult to recover the data.
The reason being that the data- and log-files were still "attached" to the
dead SQLServer and needed to be detached from it before they could be used
again; a difficult operation if the machine is dead.
However, someone suggested that this was not the case. If a machine dies
then it is a simple operation to physically disconnect the disks from the
dead machine and connect them to a new machine and continue working. This
assumes the Standard Edition of SQLServer (i.e. no clustering involved).
I can test this, but it will take a few days to set the equipment up, so I
wondered in the mean time whether anyone could confirm whether this was the
case. If so, then presumably a SAN would present an even simplier solution,
particularly if the disk set is a RAID5+1 configuration?
Thanks in advance
GriffGriff,
The SQL Server documentation say that you can attach a database if you first
detached it.
You *might* be able to attach is even if you didn't detached it first, but c
onsider this as one of
those "lucky" situations. It is not guaranteed or documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Griff" <Howling@.The.Moon> wrote in message news:e7lkMBAkEHA.3148@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> Dear all
> Having read BOL, I was of the understanding that if a machine was lost
> (anything but disk failure) then it was very difficult to recover the data
.
> The reason being that the data- and log-files were still "attached" to the
> dead SQLServer and needed to be detached from it before they could be used
> again; a difficult operation if the machine is dead.
> However, someone suggested that this was not the case. If a machine dies
> then it is a simple operation to physically disconnect the disks from the
> dead machine and connect them to a new machine and continue working. This
> assumes the Standard Edition of SQLServer (i.e. no clustering involved).
> I can test this, but it will take a few days to set the equipment up, so I
> wondered in the mean time whether anyone could confirm whether this was th
e
> case. If so, then presumably a SAN would present an even simplier solutio
n,
> particularly if the disk set is a RAID5+1 configuration?
> Thanks in advance
> Griff
>[/vbcol]|||Hi,
What is a server failure?
Which part(s) need to fail to give a server failure? CPU? Memory?
Motherboard? Disc Controller? Boot Disc? Master Database? Data drives? Log
Drives? PSU? etc?
You are highlighting the importance of DP (I prefer DP to DR - Disaster
Prevention is better than Cure). So, what can fail, what can you do to
prevent it? What do you do if it happens? Have you rehearsed for it? Does
the process work?
So a PSU blows up and takes the motherboard and CPU(s) with it. The
system/boot disc drive goes at the same time. Sounds like a server failure
to me. What do you do? Have DP? Then you may already have a standby server,
backup copies of databases on other computers, be using log shipping, and
have only to switch to standby... It is always better to be prepared before
the event than to rely on a toolkit to fish you out of some scenario after
an unpredictable event.
Recovering SQL Server databases in scenarios such as this is perhaps the
poorest documented part of SQL Server. What happens if the log drive dies at
run time? Or the data drive? Or the RAID controller? (That happened to me a
few weeks ago and was not pleasant, we did have DP in place however). We all
know the theory, but the answer is if you wish to get things back up and
running with least data-loss as the system is supposed to be designed, you
seem to have no choice but to ring MS 'cos if you ask here that is what they
will tell you to do.
So rule #1 for SQL Server DP: Don't lose the data.
Comments / constructive criticism welcome.
- Tim
"Griff" <Howling@.The.Moon> wrote in message
news:e7lkMBAkEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Dear all
> Having read BOL, I was of the understanding that if a machine was lost
> (anything but disk failure) then it was very difficult to recover the
> data.
> The reason being that the data- and log-files were still "attached" to the
> dead SQLServer and needed to be detached from it before they could be used
> again; a difficult operation if the machine is dead.
> However, someone suggested that this was not the case. If a machine dies
> then it is a simple operation to physically disconnect the disks from the
> dead machine and connect them to a new machine and continue working. This
> assumes the Standard Edition of SQLServer (i.e. no clustering involved).
> I can test this, but it will take a few days to set the equipment up, so I
> wondered in the mean time whether anyone could confirm whether this was
> the
> case. If so, then presumably a SAN would present an even simplier
> solution,
> particularly if the disk set is a RAID5+1 configuration?
> Thanks in advance
> Griff
>|||Hi Tim
I agree with you completely. We use a server with RAID5+1 disks, and
implement log shipping onto a stand-by server. However, our consultant
pointed out that this provides us with a way of getting the service up
really quickly, but with loss of data (back to the last log that was
shipped). He suggested that the way to lose no data (providing that the
disks were not damaged) was to simply to disconnect the scsi cable to the
down server and connect them to the standby server and that way no data was
lost (service might take longer to resume, but down time in our business is
perceived as better than loss of data). I just wished to question whether
this really was technically possible/reliable.
Griff|||Griff,
See my earlier reply. I suggest you ask the consultant where his strategy is
documented. That should
end the discussion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Griff" <Howling@.The.Moon> wrote in message news:OT$GeZBkEHA.1644@.tk2msftngp13.phx.gbl...[vb
col=seagreen]
> Hi Tim
> I agree with you completely. We use a server with RAID5+1 disks, and
> implement log shipping onto a stand-by server. However, our consultant
> pointed out that this provides us with a way of getting the service up
> really quickly, but with loss of data (back to the last log that was
> shipped). He suggested that the way to lose no data (providing that the
> disks were not damaged) was to simply to disconnect the scsi cable to the
> down server and connect them to the standby server and that way no data wa
s
> lost (service might take longer to resume, but down time in our business i
s
> perceived as better than loss of data). I just wished to question whether
> this really was technically possible/reliable.
> Griff
>[/vbcol]
Having read BOL, I was of the understanding that if a machine was lost
(anything but disk failure) then it was very difficult to recover the data.
The reason being that the data- and log-files were still "attached" to the
dead SQLServer and needed to be detached from it before they could be used
again; a difficult operation if the machine is dead.
However, someone suggested that this was not the case. If a machine dies
then it is a simple operation to physically disconnect the disks from the
dead machine and connect them to a new machine and continue working. This
assumes the Standard Edition of SQLServer (i.e. no clustering involved).
I can test this, but it will take a few days to set the equipment up, so I
wondered in the mean time whether anyone could confirm whether this was the
case. If so, then presumably a SAN would present an even simplier solution,
particularly if the disk set is a RAID5+1 configuration?
Thanks in advance
GriffGriff,
The SQL Server documentation say that you can attach a database if you first
detached it.
You *might* be able to attach is even if you didn't detached it first, but c
onsider this as one of
those "lucky" situations. It is not guaranteed or documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Griff" <Howling@.The.Moon> wrote in message news:e7lkMBAkEHA.3148@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> Dear all
> Having read BOL, I was of the understanding that if a machine was lost
> (anything but disk failure) then it was very difficult to recover the data
.
> The reason being that the data- and log-files were still "attached" to the
> dead SQLServer and needed to be detached from it before they could be used
> again; a difficult operation if the machine is dead.
> However, someone suggested that this was not the case. If a machine dies
> then it is a simple operation to physically disconnect the disks from the
> dead machine and connect them to a new machine and continue working. This
> assumes the Standard Edition of SQLServer (i.e. no clustering involved).
> I can test this, but it will take a few days to set the equipment up, so I
> wondered in the mean time whether anyone could confirm whether this was th
e
> case. If so, then presumably a SAN would present an even simplier solutio
n,
> particularly if the disk set is a RAID5+1 configuration?
> Thanks in advance
> Griff
>[/vbcol]|||Hi,
What is a server failure?
Which part(s) need to fail to give a server failure? CPU? Memory?
Motherboard? Disc Controller? Boot Disc? Master Database? Data drives? Log
Drives? PSU? etc?
You are highlighting the importance of DP (I prefer DP to DR - Disaster
Prevention is better than Cure). So, what can fail, what can you do to
prevent it? What do you do if it happens? Have you rehearsed for it? Does
the process work?
So a PSU blows up and takes the motherboard and CPU(s) with it. The
system/boot disc drive goes at the same time. Sounds like a server failure
to me. What do you do? Have DP? Then you may already have a standby server,
backup copies of databases on other computers, be using log shipping, and
have only to switch to standby... It is always better to be prepared before
the event than to rely on a toolkit to fish you out of some scenario after
an unpredictable event.
Recovering SQL Server databases in scenarios such as this is perhaps the
poorest documented part of SQL Server. What happens if the log drive dies at
run time? Or the data drive? Or the RAID controller? (That happened to me a
few weeks ago and was not pleasant, we did have DP in place however). We all
know the theory, but the answer is if you wish to get things back up and
running with least data-loss as the system is supposed to be designed, you
seem to have no choice but to ring MS 'cos if you ask here that is what they
will tell you to do.
So rule #1 for SQL Server DP: Don't lose the data.
Comments / constructive criticism welcome.
- Tim
"Griff" <Howling@.The.Moon> wrote in message
news:e7lkMBAkEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Dear all
> Having read BOL, I was of the understanding that if a machine was lost
> (anything but disk failure) then it was very difficult to recover the
> data.
> The reason being that the data- and log-files were still "attached" to the
> dead SQLServer and needed to be detached from it before they could be used
> again; a difficult operation if the machine is dead.
> However, someone suggested that this was not the case. If a machine dies
> then it is a simple operation to physically disconnect the disks from the
> dead machine and connect them to a new machine and continue working. This
> assumes the Standard Edition of SQLServer (i.e. no clustering involved).
> I can test this, but it will take a few days to set the equipment up, so I
> wondered in the mean time whether anyone could confirm whether this was
> the
> case. If so, then presumably a SAN would present an even simplier
> solution,
> particularly if the disk set is a RAID5+1 configuration?
> Thanks in advance
> Griff
>|||Hi Tim
I agree with you completely. We use a server with RAID5+1 disks, and
implement log shipping onto a stand-by server. However, our consultant
pointed out that this provides us with a way of getting the service up
really quickly, but with loss of data (back to the last log that was
shipped). He suggested that the way to lose no data (providing that the
disks were not damaged) was to simply to disconnect the scsi cable to the
down server and connect them to the standby server and that way no data was
lost (service might take longer to resume, but down time in our business is
perceived as better than loss of data). I just wished to question whether
this really was technically possible/reliable.
Griff|||Griff,
See my earlier reply. I suggest you ask the consultant where his strategy is
documented. That should
end the discussion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Griff" <Howling@.The.Moon> wrote in message news:OT$GeZBkEHA.1644@.tk2msftngp13.phx.gbl...[vb
col=seagreen]
> Hi Tim
> I agree with you completely. We use a server with RAID5+1 disks, and
> implement log shipping onto a stand-by server. However, our consultant
> pointed out that this provides us with a way of getting the service up
> really quickly, but with loss of data (back to the last log that was
> shipped). He suggested that the way to lose no data (providing that the
> disks were not damaged) was to simply to disconnect the scsi cable to the
> down server and connect them to the standby server and that way no data wa
s
> lost (service might take longer to resume, but down time in our business i
s
> perceived as better than loss of data). I just wished to question whether
> this really was technically possible/reliable.
> Griff
>[/vbcol]
Losing server means data loss even when transaction log is unhurt?
Hi,
I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
possible to switch over to the secondary log shipping server and recover up
to the point of failure when the primary data file has failed. But what
happens in all the other failure scenarios when the production log shipping
server is gone, the transaction log file is still available but it's
impossible to backup the last transaction log with the NO_TRUNCATE option
(since the server itself isn't running and probably the master database is
damaged)? Am I bound to lose all the transactions since the last transaction
log backup?
-- Thanks, Oskar.
Oscar
If you cannot run BACKUP LOG on production , you will have to run something
like that
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>
|||Oskar wrote:
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
> possible to switch over to the secondary log shipping server and recover up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last transaction
> log backup?
> -- Thanks, Oskar.
>
Yes, you will lose anything that occurred after the last log backup.
Backup as frequently as necessary to minimize the damage - if you can't
afford to lose 15 minutes of data, backup every 5 minutes.
Bringing the standby database online is as simple as running
RESTORE DATABASE standbyDBName WITH RECOVERY
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I think this will answer some of you questions (if I understand the issue
correctly)
http://msdn2.microsoft.com/en-us/library/ms179314.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>
|||Roger, thank you. Unfortunately this isn't what I'm after. Basically I wanted
to know if it's still possible to recover up to the point of failure in cases
when primary data file of a database and the server to which it was attached
are gone but the transaction log of the database is still intact. If that
happens there is no way I can issue a BACKUP LOG ... WITH NO_TRUNCATE (or any
other command) on the server because it's gone. Also mind that I don't have
MS SQL Server 2005 but 2000.
-- Thanks, Oskar
"Roger Wolter[MSFT]" wrote:
> I think this will answer some of you questions (if I understand the issue
> correctly)
> http://msdn2.microsoft.com/en-us/library/ms179314.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
>
>
|||But you must have a server someplace right? Your log shipping destination?
Can't you do the backup log command from there? Maybe I'm missing something
here.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...[vbcol=seagreen]
> Roger, thank you. Unfortunately this isn't what I'm after. Basically I
> wanted
> to know if it's still possible to recover up to the point of failure in
> cases
> when primary data file of a database and the server to which it was
> attached
> are gone but the transaction log of the database is still intact. If that
> happens there is no way I can issue a BACKUP LOG ... WITH NO_TRUNCATE (or
> any
> other command) on the server because it's gone. Also mind that I don't
> have
> MS SQL Server 2005 but 2000.
> -- Thanks, Oskar
> "Roger Wolter[MSFT]" wrote:
|||Here's the KB http://support.microsoft.com/kb/253817/en-us
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJr4L3yOHHA.4644@.TK2MSFTNGP03.phx.gbl...
> Yes, that should be possible:
> On some healthy SQL Server, you create a new database. Stop that SQL
> Server. Delete the two database files. "Slide" in your log file (ldf) from
> the production SQL Server where the log file were for this newly created
> database. Start this new SQL Server. Do the log backup (with NO_TRUNCATE).
> I believe that there's a KB describing this (search and you should find),
> but the steps are pretty straight forward.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...
>
|||Roger,
Log shipping destination of course would be available and I would be able to
do log backups there. The point is that log shipping destination would be
behind log shipping source (i.e. production database) in regard to the latest
transactions that happened between the time last backup was made on the
source and copied to the destination and the time of failure of the source.
So if the source is lost and I'm not able to make the last backup of those
transactions (with NO_TRUNCATE option), because the server itself is also
nonfunctional, then I'm losing those transactions which is unacceptable.
Sorry Roger, I can't explain it any better. Tibor seems to have got the point.
-- Thanks, Oskar.
"Roger Wolter[MSFT]" wrote:
> But you must have a server someplace right? Your log shipping destination?
> Can't you do the backup log command from there? Maybe I'm missing something
> here.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...
>
>
|||Thanks Roger. I think this is the one.
"Roger Wolter[MSFT]" wrote:
> Here's the KB http://support.microsoft.com/kb/253817/en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OJr4L3yOHHA.4644@.TK2MSFTNGP03.phx.gbl...
>
>
sql
I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
possible to switch over to the secondary log shipping server and recover up
to the point of failure when the primary data file has failed. But what
happens in all the other failure scenarios when the production log shipping
server is gone, the transaction log file is still available but it's
impossible to backup the last transaction log with the NO_TRUNCATE option
(since the server itself isn't running and probably the master database is
damaged)? Am I bound to lose all the transactions since the last transaction
log backup?
-- Thanks, Oskar.
Oscar
If you cannot run BACKUP LOG on production , you will have to run something
like that
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>
|||Oskar wrote:
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
> possible to switch over to the secondary log shipping server and recover up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last transaction
> log backup?
> -- Thanks, Oskar.
>
Yes, you will lose anything that occurred after the last log backup.
Backup as frequently as necessary to minimize the damage - if you can't
afford to lose 15 minutes of data, backup every 5 minutes.
Bringing the standby database online is as simple as running
RESTORE DATABASE standbyDBName WITH RECOVERY
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I think this will answer some of you questions (if I understand the issue
correctly)
http://msdn2.microsoft.com/en-us/library/ms179314.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>
|||Roger, thank you. Unfortunately this isn't what I'm after. Basically I wanted
to know if it's still possible to recover up to the point of failure in cases
when primary data file of a database and the server to which it was attached
are gone but the transaction log of the database is still intact. If that
happens there is no way I can issue a BACKUP LOG ... WITH NO_TRUNCATE (or any
other command) on the server because it's gone. Also mind that I don't have
MS SQL Server 2005 but 2000.
-- Thanks, Oskar
"Roger Wolter[MSFT]" wrote:
> I think this will answer some of you questions (if I understand the issue
> correctly)
> http://msdn2.microsoft.com/en-us/library/ms179314.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
>
>
|||But you must have a server someplace right? Your log shipping destination?
Can't you do the backup log command from there? Maybe I'm missing something
here.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...[vbcol=seagreen]
> Roger, thank you. Unfortunately this isn't what I'm after. Basically I
> wanted
> to know if it's still possible to recover up to the point of failure in
> cases
> when primary data file of a database and the server to which it was
> attached
> are gone but the transaction log of the database is still intact. If that
> happens there is no way I can issue a BACKUP LOG ... WITH NO_TRUNCATE (or
> any
> other command) on the server because it's gone. Also mind that I don't
> have
> MS SQL Server 2005 but 2000.
> -- Thanks, Oskar
> "Roger Wolter[MSFT]" wrote:
|||Here's the KB http://support.microsoft.com/kb/253817/en-us
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJr4L3yOHHA.4644@.TK2MSFTNGP03.phx.gbl...
> Yes, that should be possible:
> On some healthy SQL Server, you create a new database. Stop that SQL
> Server. Delete the two database files. "Slide" in your log file (ldf) from
> the production SQL Server where the log file were for this newly created
> database. Start this new SQL Server. Do the log backup (with NO_TRUNCATE).
> I believe that there's a KB describing this (search and you should find),
> but the steps are pretty straight forward.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...
>
|||Roger,
Log shipping destination of course would be available and I would be able to
do log backups there. The point is that log shipping destination would be
behind log shipping source (i.e. production database) in regard to the latest
transactions that happened between the time last backup was made on the
source and copied to the destination and the time of failure of the source.
So if the source is lost and I'm not able to make the last backup of those
transactions (with NO_TRUNCATE option), because the server itself is also
nonfunctional, then I'm losing those transactions which is unacceptable.
Sorry Roger, I can't explain it any better. Tibor seems to have got the point.
-- Thanks, Oskar.
"Roger Wolter[MSFT]" wrote:
> But you must have a server someplace right? Your log shipping destination?
> Can't you do the backup log command from there? Maybe I'm missing something
> here.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:DCD111CF-E527-4C22-81EE-E84027ADDCD2@.microsoft.com...
>
>
|||Thanks Roger. I think this is the one.
"Roger Wolter[MSFT]" wrote:
> Here's the KB http://support.microsoft.com/kb/253817/en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OJr4L3yOHHA.4644@.TK2MSFTNGP03.phx.gbl...
>
>
sql
Losing server means data loss even when transaction log is unhurt?
Hi,
I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
possible to switch over to the secondary log shipping server and recover up
to the point of failure when the primary data file has failed. But what
happens in all the other failure scenarios when the production log shipping
server is gone, the transaction log file is still available but it's
impossible to backup the last transaction log with the NO_TRUNCATE option
(since the server itself isn't running and probably the master database is
damaged)? Am I bound to lose all the transactions since the last transaction
log backup?
-- Thanks, Oskar.Oscar
If you cannot run BACKUP LOG on production , you will have to run something
like that
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>|||Oskar wrote:
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
is
> possible to switch over to the secondary log shipping server and recover u
p
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log shippin
g
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last transacti
on
> log backup?
> -- Thanks, Oskar.
>
Yes, you will lose anything that occurred after the last log backup.
Backup as frequently as necessary to minimize the damage - if you can't
afford to lose 15 minutes of data, backup every 5 minutes.
Bringing the standby database online is as simple as running
RESTORE DATABASE standbyDBName WITH RECOVERY
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I think this will answer some of you questions (if I understand the issue
correctly)
http://msdn2.microsoft.com/en-us/library/ms179314.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>
I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it is
possible to switch over to the secondary log shipping server and recover up
to the point of failure when the primary data file has failed. But what
happens in all the other failure scenarios when the production log shipping
server is gone, the transaction log file is still available but it's
impossible to backup the last transaction log with the NO_TRUNCATE option
(since the server itself isn't running and probably the master database is
damaged)? Am I bound to lose all the transactions since the last transaction
log backup?
-- Thanks, Oskar.Oscar
If you cannot run BACKUP LOG on production , you will have to run something
like that
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>|||Oskar wrote:
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
is
> possible to switch over to the secondary log shipping server and recover u
p
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log shippin
g
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last transacti
on
> log backup?
> -- Thanks, Oskar.
>
Yes, you will lose anything that occurred after the last log backup.
Backup as frequently as necessary to minimize the damage - if you can't
afford to lose 15 minutes of data, backup every 5 minutes.
Bringing the standby database online is as simple as running
RESTORE DATABASE standbyDBName WITH RECOVERY
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I think this will answer some of you questions (if I understand the issue
correctly)
http://msdn2.microsoft.com/en-us/library/ms179314.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BCCD71E3-BB89-4BEE-B601-03E58ECBFA79@.microsoft.com...
> Hi,
> I have log shipping with two MS SQL Servers 2000 SP3. The BOL say that it
> is
> possible to switch over to the secondary log shipping server and recover
> up
> to the point of failure when the primary data file has failed. But what
> happens in all the other failure scenarios when the production log
> shipping
> server is gone, the transaction log file is still available but it's
> impossible to backup the last transaction log with the NO_TRUNCATE option
> (since the server itself isn't running and probably the master database is
> damaged)? Am I bound to lose all the transactions since the last
> transaction
> log backup?
> -- Thanks, Oskar.
>
Monday, March 12, 2012
Lookup finds match on empty reference table
Hi all,
In BOL it says: "The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. "
I have a lookup transformation which is supposed to find a match on two fields in the reference dataset (a table in my case) but strangely, when I execute my package and the reference table is empty the lookup still finds match for each row of my input dataset.
Does anyone have an idea why? I could'nt find anything about that in BOL.
Sbastien.
If the lookup is returning rows, yet now rows exists, it cannot be matching, the returned columns if any will be null. You can however allow this behaviour, by ignoring errors. A failed lookup match is classed as an error, and can be set under the Configure Error Outputs button/screen. See if you have set Ignore for errors.|||Indeed I have configured the error to "Ignore errors" but I thought it will simply avoid the package to fail if it couldn't find any match. I don't want to use Redirect rows either as I have nothing to do with the non matching rows.|||Sebastian,
The solution here then is quite simple. Use a Conditional Split to filter out the columns that have NULL values in the lookup columns - thereby giving you the rows that yielded a match.
-Jamie
Subscribe to:
Comments (Atom)