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

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.
>

lose data

In my applications (web version and client version)
sometime my users in the day after loss data!
I think are a transaction problem but
I don't know why...
Have you any suggests for my problem...
thank to all
Pippo
2 things you might try...
Run Profiler to capture inserts, updates, and deletes to see what is going
on. or
use Lumigents tool ( Log Exploerer) to search the transaction log and do the
same thing.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"PIPPO" <anonymous@.discussions.microsoft.com> wrote in message
news:118a01c4ab8f$00744770$a601280a@.phx.gbl...
> In my applications (web version and client version)
> sometime my users in the day after loss data!
> I think are a transaction problem but
> I don't know why...
> Have you any suggests for my problem...
> thank to all
> Pippo
|||- check for rollback of transactions
- check also if some process is updating your initial rows.

>--Original Message--
>2 things you might try...
>Run Profiler to capture inserts, updates, and deletes to
see what is going
>on. or
>use Lumigents tool ( Log Exploerer) to search the
transaction log and do the
>same thing.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"PIPPO" <anonymous@.discussions.microsoft.com> wrote in
message
>news:118a01c4ab8f$00744770$a601280a@.phx.gbl...
>
>.
>

lose data

In my applications (web version and client version)
sometime my users in the day after loss data!
I think are a transaction problem but
I don't know why...
Have you any suggests for my problem...
thank to all
Pippo2 things you might try...
Run Profiler to capture inserts, updates, and deletes to see what is going
on. or
use Lumigents tool ( Log Exploerer) to search the transaction log and do the
same thing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"PIPPO" <anonymous@.discussions.microsoft.com> wrote in message
news:118a01c4ab8f$00744770$a601280a@.phx.gbl...
> In my applications (web version and client version)
> sometime my users in the day after loss data!
> I think are a transaction problem but
> I don't know why...
> Have you any suggests for my problem...
> thank to all
> Pippo|||- check for rollback of transactions
- check also if some process is updating your initial rows.
>--Original Message--
>2 things you might try...
>Run Profiler to capture inserts, updates, and deletes to
see what is going
>on. or
>use Lumigents tool ( Log Exploerer) to search the
transaction log and do the
>same thing.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"PIPPO" <anonymous@.discussions.microsoft.com> wrote in
message
>news:118a01c4ab8f$00744770$a601280a@.phx.gbl...
>> In my applications (web version and client version)
>> sometime my users in the day after loss data!
>> I think are a transaction problem but
>> I don't know why...
>> Have you any suggests for my problem...
>> thank to all
>> Pippo
>
>.
>sql

Looping through sysDatabases to perform maintenance

from sql 2000:
I need to generate some metrics on the size of transaction logs on all
databases for a given server. I was thinking I could loop through
sysDatabases to get the name of every database. However, when I try to do
something like:
...
While @.@.Fetch_Status = 0 Begin
Use @.cDBName
--Do some stuff here...
Fetch next From curDBList Into @.cDBName
End
the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
where I can dynamically specifiy the name of the DB with the USE command?
thanks in advance.mystical potato (mysticalpotato@.discussions.microsoft.com) writes:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
The easiest is probably to to use sp_MSforeachdb. Here is a fairly
stupid example:
sp_MSforeachdb 'SELECT ''?'', COUNT(*) FROM sysobjects'
Note that sp_MSforeachdb is not a documented function, and thus not
supported. Nevertheless, it's fairly popular.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
A different approach may be to use the Perfmon counters to track this,
especially if you wish to pinpoint a sudden increase. A step further along
the system monitoring approach would be to look into using MOM to gather and
report this for you.
John
"mystical potato" <mysticalpotato@.discussions.microsoft.com> wrote in
message news:D5DE41B3-6065-422E-8C02-DFFE9167C213@.microsoft.com...
> from sql 2000:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
> thanks in advance.|||Try using:
dbcc sqlperf(logspace)
AMB
"mystical potato" wrote:

> from sql 2000:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
> thanks in advance.

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than 24 hoursLook at Tracy's suggestion at:
http://www.realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html#extended
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:OaYXTC9%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
>

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than
24 hoursOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Posted to .tools
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:ux9eBD9%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
> looking for script to backup Transaction log and delete any thing older
> than 24 hours
>|||Omran Abdurrahman wrote:
> looking for script to backup Transaction log and delete any thing older than
> 24 hours
>
Start here:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Omran,
If you are using SQL 2005, look at the new XP_Delete_File stored
procedure. Otherwise, Tracy is on the right track. You can store your
backups in a table and run cmd queries with xp_cmdshell to delete files
older than your threshold. This does not need to be a persisted table.
You can also use the dir command to populate a temp table, and delete
based off that.
Delete bak files over 46 hours old
Declare @.deletedate nvarchar(255). @.Path nvarchar(200)
set @.Path = '\\fileserver\db_backup\'
set @.deletedate = cast(dateadd(hh,-46,getdate()) as varchar(255))
EXECUTE master.dbo.xp_delete_file 0,@.Path,N'bak',@.deletedate
Personally I would disregard the comments from Arnie Rowland. It is too
bad he does not offer constructive comments.
Terry
Tracy McKibben wrote:
> Omran Abdurrahman wrote:
> > looking for script to backup Transaction log and delete any thing older than
> > 24 hours
> >
> >
> Start here:
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

looking for script to backup Transaction log and delete any thing older than 24 hours

looking for script to backup Transaction log and delete any thing older than
24 hoursOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Posted to .tools
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Omran Abdurrahman" <omranab@.comcast.net> wrote in message
news:ux9eBD9%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
> looking for script to backup Transaction log and delete any thing older
> than 24 hours
>|||Omran,
If you are using SQL 2005, look at the new XP_Delete_File stored
procedure. Otherwise, Tracy is on the right track. You can store your
backups in a table and run cmd queries with xp_cmdshell to delete files
older than your threshold. This does not need to be a persisted table.
You can also use the dir command to populate a temp table, and delete
based off that.
Delete bak files over 46 hours old
Declare @.deletedate nvarchar(255). @.Path nvarchar(200)
set @.Path = '\\fileserver\db_backup'
set @.deletedate = cast(dateadd(hh,-46,getdate()) as varchar(255))
EXECUTE master.dbo.xp_delete_file 0,@.Path,N'bak',@.deletedate
Personally I would disregard the comments from Arnie Rowland. It is too
bad he does not offer constructive comments.
Terry
Tracy McKibben wrote:
> Omran Abdurrahman wrote:
> Start here:
> http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com