I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
MohI'm not entirely certain about all 3 but there are at least 2 steps in a
recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing" committed
transactions (they may have been written to the transaction log, as this
always happens first, but not yet written to the data file(s)). If the
server is shutdown properly, then this phase will should be very short,
if not non-existent. The roll-back phase is "undoing" transactions that
had not been committed at the recovery point (so at the point the server
was shut down there may have been some transactions in progress that
hadn't been committed yet). These 2 phases will put the data in a
consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the
database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: Understanding How Restore and
Recovery of Backups Work
<http://msdn2.microsoft.com/en-us/li...8SQL.90%29.aspx>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (Database Engine Availability Enhancements
<http://msdn2.microsoft.com/en-us/li...8SQL.90%29.aspx> ).
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Moh wrote:
>I have seen this message in Windows event log after restarting database.
>3450:
>Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
>(approximately 2152 more seconds) (Phase 2 of 3)
>Question is:
>What are these three phases.
>Thx,
>Moh
>|||Just a note that in SQL 2005 its only Enterprise edition that allows the dat
abase to be accessed before the rol back phase is complete.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:OJ64mwqYGHA.1016@.
TK2MSFTNGP03.phx.gbl...
I'm not entirely certain about all 3 but there are at least 2 steps in a rec
overy (whether a restore or a DB startup) and they are the roll-forward and
the roll-back. The roll-forward is "redoing" committed transactions (they m
ay have been written to the transaction log, as this always happens first, b
ut not yet written to the data file(s)). If the server is shutdown properly
, then this phase will should be very short, if not non-existent. The roll-
back phase is "undoing" transactions that had not been committed at the reco
very point (so at the point the server was shut down there may have been som
e transactions in progress that hadn't been committed yet). These 2 phases
will put the data in a consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the dat
abase "online" so it will accept user connections (in a RESTORE process the
very 1st phase (before roll-forward and then roll-back) is the data copy pha
se but this wouldn't be necessary for a startup recovery because the data wo
uld already be in the data files). There's a fairly good page about it in B
OL: Understanding How Restore and Recovery of Backups Work
With SQL 2000 the database is unusable until the end of the roll-back phase.
With SQL 2005 the database is now usable after the end of the roll-forward
phase. From memory they coined this "fast recovery" or something like that
(Database Engine Availability Enhancements).
Hope this helps.
mike hodgson
http://sqlnerd.blogspot.com
Moh wrote:
I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
Showing posts with label windows. Show all posts
Showing posts with label windows. Show all posts
Friday, March 30, 2012
Recovery phases
I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
MohThis is a multi-part message in MIME format.
--080209040300000504020608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I'm not entirely certain about all 3 but there are at least 2 steps in a
recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing" committed
transactions (they may have been written to the transaction log, as this
always happens first, but not yet written to the data file(s)). If the
server is shutdown properly, then this phase will should be very short,
if not non-existent. The roll-back phase is "undoing" transactions that
had not been committed at the recovery point (so at the point the server
was shut down there may have been some transactions in progress that
hadn't been committed yet). These 2 phases will put the data in a
consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the
database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: Understanding How Restore and
Recovery of Backups Work
<http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (Database Engine Availability Enhancements
<http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx>).
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Moh wrote:
>I have seen this message in Windows event log after restarting database.
>3450:
>Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
>(approximately 2152 more seconds) (Phase 2 of 3)
>Question is:
>What are these three phases.
>Thx,
>Moh
>
--080209040300000504020608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not entirely certain about all 3 but there are at least 2 steps
in a recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing"
committed transactions (they may have been written to the transaction
log, as this always happens first, but not yet written to the data
file(s)). If the server is shutdown properly, then this phase will
should be very short, if not non-existent. The roll-back phase is
"undoing" transactions that had not been committed at the recovery
point (so at the point the server was shut down there may have been
some transactions in progress that hadn't been committed yet). These 2
phases will put the data in a consistent state as at the recovery point.<br>
<br>
I'm guessing the 3rd stage in the startup recovery would be bringing
the database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: <a
href="http://links.10026.com/?link=Understanding">http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx">Understanding
How Restore and Recovery of Backups Work</a><br>
<br>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (<a
href="http://links.10026.com/?link=Database">http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx">Database
Engine Availability Enhancements</a>).<br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Moh wrote:
<blockquote cite="mid8C953261-676E-41D5-80E7-4A2064EB9823@.microsoft.com"
type="cite">
<pre wrap="">I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
</pre>
</blockquote>
</body>
</html>
--080209040300000504020608--|||This is a multi-part message in MIME format.
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Just a note that in SQL 2005 its only Enterprise edition that allows the =database to be accessed before the rol back phase is complete.
-- Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OJ64mwqYGHA.1016@.TK2MSFTNGP03.phx.gbl...
I'm not entirely certain about all 3 but there are at least 2 steps in =a recovery (whether a restore or a DB startup) and they are the =roll-forward and the roll-back. The roll-forward is "redoing" committed =transactions (they may have been written to the transaction log, as this =always happens first, but not yet written to the data file(s)). If the =server is shutdown properly, then this phase will should be very short, =if not non-existent. The roll-back phase is "undoing" transactions that =had not been committed at the recovery point (so at the point the server =was shut down there may have been some transactions in progress that =hadn't been committed yet). These 2 phases will put the data in a =consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing =the database "online" so it will accept user connections (in a RESTORE =process the very 1st phase (before roll-forward and then roll-back) is =the data copy phase but this wouldn't be necessary for a startup =recovery because the data would already be in the data files). There's =a fairly good page about it in BOL: Understanding How Restore and =Recovery of Backups Work
With SQL 2000 the database is unusable until the end of the roll-back =phase. With SQL 2005 the database is now usable after the end of the =roll-forward phase. From memory they coined this "fast recovery" or =something like that (Database Engine Availability Enhancements).
Hope this helps.
--
mike hodgson
http://sqlnerd.blogspot.com=20
Moh wrote: I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just a note that in SQL 2005 its only =Enterprise edition that allows the database to be accessed before the rol back =phase is complete.
-- Simon SabinSQL Server MVPhttp://sqljunkies.com/weblog/simons">http://sqljunkies.com/weblog=/simons
"Mike Hodgson" wrote =in message news:OJ64mwqYGHA.1016=@.TK2MSFTNGP03.phx.gbl...I'm not entirely certain about all 3 but there are at least 2 steps in a =recovery (whether a restore or a DB startup) and they are the roll-forward and =the roll-back. The roll-forward is "redoing" committed transactions =(they may have been written to the transaction log, as this always happens =first, but not yet written to the data file(s)). If the server is =shutdown properly, then this phase will should be very short, if not non-existent. The roll-back phase is "undoing" transactions that =had not been committed at the recovery point (so at the point the server was =shut down there may have been some transactions in progress that hadn't been =committed yet). These 2 phases will put the data in a consistent state as =at the recovery point.I'm guessing the 3rd stage in the startup =recovery would be bringing the database "online" so it will accept user =connections (in a RESTORE process the very 1st phase (before roll-forward and then =roll-back) is the data copy phase but this wouldn't be necessary for a startup =recovery because the data would already be in the data files). There's a =fairly good page about it in BOL: Understanding How Restore and Recovery of Backups WorkWith SQL 2000 the =database is unusable until the end of the roll-back phase. With SQL 2005 =the database is now usable after the end of the roll-forward phase. =From memory they coined this "fast recovery" or something like that (Database Engine Availability Enhancements).Hope this =helps.
--mike =hodgsonhttp://sqlnerd.blogspot.com Moh wrote: I have seen this message in Windows event =log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620--sql
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
MohThis is a multi-part message in MIME format.
--080209040300000504020608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I'm not entirely certain about all 3 but there are at least 2 steps in a
recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing" committed
transactions (they may have been written to the transaction log, as this
always happens first, but not yet written to the data file(s)). If the
server is shutdown properly, then this phase will should be very short,
if not non-existent. The roll-back phase is "undoing" transactions that
had not been committed at the recovery point (so at the point the server
was shut down there may have been some transactions in progress that
hadn't been committed yet). These 2 phases will put the data in a
consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the
database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: Understanding How Restore and
Recovery of Backups Work
<http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (Database Engine Availability Enhancements
<http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx>).
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Moh wrote:
>I have seen this message in Windows event log after restarting database.
>3450:
>Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
>(approximately 2152 more seconds) (Phase 2 of 3)
>Question is:
>What are these three phases.
>Thx,
>Moh
>
--080209040300000504020608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not entirely certain about all 3 but there are at least 2 steps
in a recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing"
committed transactions (they may have been written to the transaction
log, as this always happens first, but not yet written to the data
file(s)). If the server is shutdown properly, then this phase will
should be very short, if not non-existent. The roll-back phase is
"undoing" transactions that had not been committed at the recovery
point (so at the point the server was shut down there may have been
some transactions in progress that hadn't been committed yet). These 2
phases will put the data in a consistent state as at the recovery point.<br>
<br>
I'm guessing the 3rd stage in the startup recovery would be bringing
the database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: <a
href="http://links.10026.com/?link=Understanding">http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx">Understanding
How Restore and Recovery of Backups Work</a><br>
<br>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (<a
href="http://links.10026.com/?link=Database">http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx">Database
Engine Availability Enhancements</a>).<br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Moh wrote:
<blockquote cite="mid8C953261-676E-41D5-80E7-4A2064EB9823@.microsoft.com"
type="cite">
<pre wrap="">I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
</pre>
</blockquote>
</body>
</html>
--080209040300000504020608--|||This is a multi-part message in MIME format.
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Just a note that in SQL 2005 its only Enterprise edition that allows the =database to be accessed before the rol back phase is complete.
-- Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OJ64mwqYGHA.1016@.TK2MSFTNGP03.phx.gbl...
I'm not entirely certain about all 3 but there are at least 2 steps in =a recovery (whether a restore or a DB startup) and they are the =roll-forward and the roll-back. The roll-forward is "redoing" committed =transactions (they may have been written to the transaction log, as this =always happens first, but not yet written to the data file(s)). If the =server is shutdown properly, then this phase will should be very short, =if not non-existent. The roll-back phase is "undoing" transactions that =had not been committed at the recovery point (so at the point the server =was shut down there may have been some transactions in progress that =hadn't been committed yet). These 2 phases will put the data in a =consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing =the database "online" so it will accept user connections (in a RESTORE =process the very 1st phase (before roll-forward and then roll-back) is =the data copy phase but this wouldn't be necessary for a startup =recovery because the data would already be in the data files). There's =a fairly good page about it in BOL: Understanding How Restore and =Recovery of Backups Work
With SQL 2000 the database is unusable until the end of the roll-back =phase. With SQL 2005 the database is now usable after the end of the =roll-forward phase. From memory they coined this "fast recovery" or =something like that (Database Engine Availability Enhancements).
Hope this helps.
--
mike hodgson
http://sqlnerd.blogspot.com=20
Moh wrote: I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just a note that in SQL 2005 its only =Enterprise edition that allows the database to be accessed before the rol back =phase is complete.
-- Simon SabinSQL Server MVPhttp://sqljunkies.com/weblog/simons">http://sqljunkies.com/weblog=/simons
"Mike Hodgson"
--mike =hodgsonhttp://sqlnerd.blogspot.com Moh wrote: I have seen this message in Windows event =log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620--sql
Wednesday, March 28, 2012
Recovery Model Full
I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,The recovery model concept *replaces* the two old database options "trunc."
and "select into".
Setting trunc to TRUE is the very same thing as setting recovery model to si
mple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server a
s
> operating system. I am trying to set sp_dboption database_name, 'trunc. l
og
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute sp_dboptio
n
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,|||That's really there for backwards compatibility. Use ALTER DATABASE.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,|||Joe,
SQL Server doesnt allow you truncate the log on checkpoint when the
recovery model is FULL. ie...either a database can be in Simple Recovery
Model (truncate log on checkpoint) or Full Reovery Model but not both.
Jayesh
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server
> as
> operating system. I am trying to set sp_dboption database_name, 'trunc.
> log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute
> sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,The recovery model concept *replaces* the two old database options "trunc."
and "select into".
Setting trunc to TRUE is the very same thing as setting recovery model to si
mple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server a
s
> operating system. I am trying to set sp_dboption database_name, 'trunc. l
og
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute sp_dboptio
n
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,|||That's really there for backwards compatibility. Use ALTER DATABASE.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,|||Joe,
SQL Server doesnt allow you truncate the log on checkpoint when the
recovery model is FULL. ie...either a database can be in Simple Recovery
Model (truncate log on checkpoint) or Full Reovery Model but not both.
Jayesh
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server
> as
> operating system. I am trying to set sp_dboption database_name, 'trunc.
> log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute
> sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,
Recovery Model Full
I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,The recovery model concept *replaces* the two old database options "trunc." and "select into".
Setting trunc to TRUE is the very same thing as setting recovery model to simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
> operating system. I am trying to set sp_dboption database_name, 'trunc. log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,|||That's really there for backwards compatibility. Use ALTER DATABASE.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,|||Joe,
SQL Server doesnt allow you truncate the log on checkpoint when the
recovery model is FULL. ie...either a database can be in Simple Recovery
Model (truncate log on checkpoint) or Full Reovery Model but not both.
Jayesh
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server
> as
> operating system. I am trying to set sp_dboption database_name, 'trunc.
> log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute
> sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,The recovery model concept *replaces* the two old database options "trunc." and "select into".
Setting trunc to TRUE is the very same thing as setting recovery model to simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
> operating system. I am trying to set sp_dboption database_name, 'trunc. log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,|||That's really there for backwards compatibility. Use ALTER DATABASE.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,|||Joe,
SQL Server doesnt allow you truncate the log on checkpoint when the
recovery model is FULL. ie...either a database can be in Simple Recovery
Model (truncate log on checkpoint) or Full Reovery Model but not both.
Jayesh
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server
> as
> operating system. I am trying to set sp_dboption database_name, 'trunc.
> log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute
> sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,
Monday, March 26, 2012
Recovery from windows failure
Hi all,
We've got a problem with our server in that Windows has totally died - a routine upgrade appears to have gone wrong and the repair function of the Windows installer keeps getting stuck in a loop so I've given up on that front. What I was wondering is:
1. If I install a fresh copy of Windows 2003 R2 over the top of the existing install will that kill MSSQL Sever 2005 and/or Windows Sharepoint Services?
2. If so, is there a simple way to get them back?
3. If I install another copy of Windows on the D: drive is there a recommended way to move the MSSQL databases & Sharepoint to the newer install or a way of pointing the new install at the old databases?
I'm not an admin person (i'm a developer normally) so I haven't got a lot of background in this stuff so any tips, pointers or ideas are welcome. If this is covered somewhere on the MS site I'm happy to read but I'm just getting lost at the moment.
And, yes, I realise that backups are a good thing but the latest backups are not new enough for some items. The backup process has been adjusted accordingly.
Thanks
Michael.serious problem...sympathizing...|||
Howdy,
I am also a newbie; and have run into a similar problem as you "Mycle". I am trying not to lose all my info / data a my C: drive aswell. My system just keeps looping , and looping in the setup / upgrade installation process; and stops at the 39min mark. Then i get this "Fatal" error msg. I cannot even get into the recovery screen.
"The signature windows XP Pro upgrade is invalid, code error 426. The service is not started.
and then we continue to dance and boot / loop again.
I've exhausted my eyes for nearly half a day now and nothing.
If you find something could you hopefully send me in the right direction, please and thanks, this also goes out to anyone else that may also have some insite, thanks again.
'Ruff'
specs: Running Windows XP Pro; the upgrade was on the account of windows crashing and not booting back up. ( 100 times)
|||Have you had any luck yet? I'm almost at the point where i have to format the drive.
please help if you have any ideas for myself, please and thanks inadvance.
Ruffnready
Recovery from windows failure
Hi all,
We've got a problem with our server in that Windows has totally died - a routine upgrade appears to have gone wrong and the repair function of the Windows installer keeps getting stuck in a loop so I've given up on that front. What I was wondering is:
1. If I install a fresh copy of Windows 2003 R2 over the top of the existing install will that kill MSSQL Sever 2005 and/or Windows Sharepoint Services?
2. If so, is there a simple way to get them back?
3. If I install another copy of Windows on the D: drive is there a recommended way to move the MSSQL databases & Sharepoint to the newer install or a way of pointing the new install at the old databases?
I'm not an admin person (i'm a developer normally) so I haven't got a lot of background in this stuff so any tips, pointers or ideas are welcome. If this is covered somewhere on the MS site I'm happy to read but I'm just getting lost at the moment.
And, yes, I realise that backups are a good thing but the latest backups are not new enough for some items. The backup process has been adjusted accordingly.
Thanks
Michael.
serious problem...sympathizing...|||
Howdy,
I am also a newbie; and have run into a similar problem as you "Mycle". I am trying not to lose all my info / data a my C: drive aswell. My system just keeps looping , and looping in the setup / upgrade installation process; and stops at the 39min mark. Then i get this "Fatal" error msg. I cannot even get into the recovery screen.
"The signature windows XP Pro upgrade is invalid, code error 426. The service is not started.
and then we continue to dance and boot / loop again.
I've exhausted my eyes for nearly half a day now and nothing.
If you find something could you hopefully send me in the right direction, please and thanks, this also goes out to anyone else that may also have some insite, thanks again.
'Ruff'
specs: Running Windows XP Pro; the upgrade was on the account of windows crashing and not booting back up. ( 100 times)
|||Have you had any luck yet? I'm almost at the point where i have to format the drive.
please help if you have any ideas for myself, please and thanks inadvance.
Ruffnready
Friday, March 23, 2012
Recovering DB from deleted files
Hi All,
Using sql 2000 on windows 200 server
I am trying to recover a SQL 2000 DB from crashed.
Now I only have the .LDF file of the DB
is there any way i can use to recover the db from the >LDF file
Thanx,
mateHi,
No, you cant recover a database with just the LDF file. If you have the MDF
file then you can try attaching the database using
SP_ATTACH_DB procedure.
In this scenario, the only way to recover the database is to RESTORE the
database from last successful full datbase backup
and apply the subsequent transaction logs.
Thanks
Hari
MCDBA
"zoro24" <zoro24@.hotmail.com> wrote in message
news:OZiWis2DEHA.240@.tk2msftngp13.phx.gbl...
>
> Hi All,
> Using sql 2000 on windows 200 server
> I am trying to recover a SQL 2000 DB from crashed.
> Now I only have the .LDF file of the DB
> is there any way i can use to recover the db from the >LDF file
>
> Thanx,
> mate|||If you only have the LDF file, you have a problem. Are you certain you don't
have the mdf file as well? Or a backup?
The ldf file only contains the modifications performed in the database, and
if you never did a database backup, the file is emptied automatically
regularly. There exists log readers (see the link page on my web site), but
there's a high risk that the information is no longer there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"zoro24" <zoro24@.hotmail.com> wrote in message
news:OZiWis2DEHA.240@.tk2msftngp13.phx.gbl...
>
> Hi All,
> Using sql 2000 on windows 200 server
> I am trying to recover a SQL 2000 DB from crashed.
> Now I only have the .LDF file of the DB
> is there any way i can use to recover the db from the >LDF file
>
> Thanx,
> mate
Using sql 2000 on windows 200 server
I am trying to recover a SQL 2000 DB from crashed.
Now I only have the .LDF file of the DB
is there any way i can use to recover the db from the >LDF file
Thanx,
mateHi,
No, you cant recover a database with just the LDF file. If you have the MDF
file then you can try attaching the database using
SP_ATTACH_DB procedure.
In this scenario, the only way to recover the database is to RESTORE the
database from last successful full datbase backup
and apply the subsequent transaction logs.
Thanks
Hari
MCDBA
"zoro24" <zoro24@.hotmail.com> wrote in message
news:OZiWis2DEHA.240@.tk2msftngp13.phx.gbl...
>
> Hi All,
> Using sql 2000 on windows 200 server
> I am trying to recover a SQL 2000 DB from crashed.
> Now I only have the .LDF file of the DB
> is there any way i can use to recover the db from the >LDF file
>
> Thanx,
> mate|||If you only have the LDF file, you have a problem. Are you certain you don't
have the mdf file as well? Or a backup?
The ldf file only contains the modifications performed in the database, and
if you never did a database backup, the file is emptied automatically
regularly. There exists log readers (see the link page on my web site), but
there's a high risk that the information is no longer there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"zoro24" <zoro24@.hotmail.com> wrote in message
news:OZiWis2DEHA.240@.tk2msftngp13.phx.gbl...
>
> Hi All,
> Using sql 2000 on windows 200 server
> I am trying to recover a SQL 2000 DB from crashed.
> Now I only have the .LDF file of the DB
> is there any way i can use to recover the db from the >LDF file
>
> Thanx,
> mate
Tuesday, March 20, 2012
recover northstar
I'm new to SQL server.
We have a windows 2003 server, and I installed SQL server
2000 on C and database file on D. I tried to follow a
book to use SQL server to backup Northwind database on
another disk E:
First I backuped the northwind database on E, then I
tried to restore it to E: and gave it another name in the
restore as field: E:\test.ldf E:\test.mdf
It restored OK, then I saw two files in E: test.ldf. and
test.mdf
Then I thought I can delete these two testing files.
It didn't allow me to do so and gave message you can't
delete them because another program is using it. Then I
close SQL server, this time I can delete them.
But when I start SQL server again, I found the NOrthwind
database is missing, no items and suspect shown there.
The worst thing is I deleted the backup file too before I
found this.
What should I do now to recover the northstar database,
do I need to reinstall it? and I can't figure out why I
deleted NOrthwind database in C while I'm not intend to
do so?
Thanks for any suggestionsAnn,
Its a bad practice to delete the mdf and ldf files directly from the OS
level irrespective whether SQL Server is running or not. did you check the
recycle bin if the files are there?If you want to delete a database, use the
T-SQL command DROP DATABASE or enterprise manager after making sure that no
connection is accessing it.If you have only deleted the .mdf and .ldf files
for the test database then it shouldnt affect Northwind database.May be ,
accidentally, you deleted the other files too.Is the Northwind database
being shown as 'suspect'? If so, it may be because the files are
missing.Doublecheck that by referring the errorlogs.Try deleting the
database. The script to recreate Northwind is also installed with sql
setup.Search for the file named 'instnwnd.sql ' in the SQL Server machine
and execute it .If done properly, this would recreate Northwind complete
with data and all stuff.You can either use Query analyzer or osql utility as
explained in:
Northwind Sample Database
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_north_2ch1.asp?frame=true
In case you dont find the file in the server, you can find a copy in the SQL
setup CD.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ann" <xiaowei.cao@.state.mn.us> wrote in message
news:0b4501c34cab$75c46670$a001280a@.phx.gbl...
> I'm new to SQL server.
> We have a windows 2003 server, and I installed SQL server
> 2000 on C and database file on D. I tried to follow a
> book to use SQL server to backup Northwind database on
> another disk E:
> First I backuped the northwind database on E, then I
> tried to restore it to E: and gave it another name in the
> restore as field: E:\test.ldf E:\test.mdf
> It restored OK, then I saw two files in E: test.ldf. and
> test.mdf
> Then I thought I can delete these two testing files.
> It didn't allow me to do so and gave message you can't
> delete them because another program is using it. Then I
> close SQL server, this time I can delete them.
> But when I start SQL server again, I found the NOrthwind
> database is missing, no items and suspect shown there.
> The worst thing is I deleted the backup file too before I
> found this.
> What should I do now to recover the northstar database,
> do I need to reinstall it? and I can't figure out why I
> deleted NOrthwind database in C while I'm not intend to
> do so?
> Thanks for any suggestions
>
>|||Ann,
Don't know the exact steps, but I think what you may have done is
restored the Northwind database from the backup file and told it to move
the physical database files (.mdf (data) and .ldf (log)) to the E drive.
This did not create a new database with the new file names, it restored
the existing database into the new filenames, and deleted the original
database files.
When you tried to delete the files and were told the files were in use,
SQL Server was using them.
When you deleted these files from disk after stopping SQL server, it
caused SQL server to mark the database suspect (unavailable/corrupted).
SQL server databases are not file based databases, you should ignore any
files created by SQL server except the backup files generated by the
backup process.
To create a copy of a database, backup the database, create a new
database with a different database name, restore the backup file to this
new database you just created. Make sure you check the force restore
flag in the options tab of the restore dialog.
Abel Fontes
Ann wrote:
> I'm new to SQL server.
> We have a windows 2003 server, and I installed SQL server
> 2000 on C and database file on D. I tried to follow a
> book to use SQL server to backup Northwind database on
> another disk E:
> First I backuped the northwind database on E, then I
> tried to restore it to E: and gave it another name in the
> restore as field: E:\test.ldf E:\test.mdf
> It restored OK, then I saw two files in E: test.ldf. and
> test.mdf
> Then I thought I can delete these two testing files.
> It didn't allow me to do so and gave message you can't
> delete them because another program is using it. Then I
> close SQL server, this time I can delete them.
> But when I start SQL server again, I found the NOrthwind
> database is missing, no items and suspect shown there.
> The worst thing is I deleted the backup file too before I
> found this.
> What should I do now to recover the northstar database,
> do I need to reinstall it? and I can't figure out why I
> deleted NOrthwind database in C while I'm not intend to
> do so?
> Thanks for any suggestions
>
>
We have a windows 2003 server, and I installed SQL server
2000 on C and database file on D. I tried to follow a
book to use SQL server to backup Northwind database on
another disk E:
First I backuped the northwind database on E, then I
tried to restore it to E: and gave it another name in the
restore as field: E:\test.ldf E:\test.mdf
It restored OK, then I saw two files in E: test.ldf. and
test.mdf
Then I thought I can delete these two testing files.
It didn't allow me to do so and gave message you can't
delete them because another program is using it. Then I
close SQL server, this time I can delete them.
But when I start SQL server again, I found the NOrthwind
database is missing, no items and suspect shown there.
The worst thing is I deleted the backup file too before I
found this.
What should I do now to recover the northstar database,
do I need to reinstall it? and I can't figure out why I
deleted NOrthwind database in C while I'm not intend to
do so?
Thanks for any suggestionsAnn,
Its a bad practice to delete the mdf and ldf files directly from the OS
level irrespective whether SQL Server is running or not. did you check the
recycle bin if the files are there?If you want to delete a database, use the
T-SQL command DROP DATABASE or enterprise manager after making sure that no
connection is accessing it.If you have only deleted the .mdf and .ldf files
for the test database then it shouldnt affect Northwind database.May be ,
accidentally, you deleted the other files too.Is the Northwind database
being shown as 'suspect'? If so, it may be because the files are
missing.Doublecheck that by referring the errorlogs.Try deleting the
database. The script to recreate Northwind is also installed with sql
setup.Search for the file named 'instnwnd.sql ' in the SQL Server machine
and execute it .If done properly, this would recreate Northwind complete
with data and all stuff.You can either use Query analyzer or osql utility as
explained in:
Northwind Sample Database
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_north_2ch1.asp?frame=true
In case you dont find the file in the server, you can find a copy in the SQL
setup CD.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ann" <xiaowei.cao@.state.mn.us> wrote in message
news:0b4501c34cab$75c46670$a001280a@.phx.gbl...
> I'm new to SQL server.
> We have a windows 2003 server, and I installed SQL server
> 2000 on C and database file on D. I tried to follow a
> book to use SQL server to backup Northwind database on
> another disk E:
> First I backuped the northwind database on E, then I
> tried to restore it to E: and gave it another name in the
> restore as field: E:\test.ldf E:\test.mdf
> It restored OK, then I saw two files in E: test.ldf. and
> test.mdf
> Then I thought I can delete these two testing files.
> It didn't allow me to do so and gave message you can't
> delete them because another program is using it. Then I
> close SQL server, this time I can delete them.
> But when I start SQL server again, I found the NOrthwind
> database is missing, no items and suspect shown there.
> The worst thing is I deleted the backup file too before I
> found this.
> What should I do now to recover the northstar database,
> do I need to reinstall it? and I can't figure out why I
> deleted NOrthwind database in C while I'm not intend to
> do so?
> Thanks for any suggestions
>
>|||Ann,
Don't know the exact steps, but I think what you may have done is
restored the Northwind database from the backup file and told it to move
the physical database files (.mdf (data) and .ldf (log)) to the E drive.
This did not create a new database with the new file names, it restored
the existing database into the new filenames, and deleted the original
database files.
When you tried to delete the files and were told the files were in use,
SQL Server was using them.
When you deleted these files from disk after stopping SQL server, it
caused SQL server to mark the database suspect (unavailable/corrupted).
SQL server databases are not file based databases, you should ignore any
files created by SQL server except the backup files generated by the
backup process.
To create a copy of a database, backup the database, create a new
database with a different database name, restore the backup file to this
new database you just created. Make sure you check the force restore
flag in the options tab of the restore dialog.
Abel Fontes
Ann wrote:
> I'm new to SQL server.
> We have a windows 2003 server, and I installed SQL server
> 2000 on C and database file on D. I tried to follow a
> book to use SQL server to backup Northwind database on
> another disk E:
> First I backuped the northwind database on E, then I
> tried to restore it to E: and gave it another name in the
> restore as field: E:\test.ldf E:\test.mdf
> It restored OK, then I saw two files in E: test.ldf. and
> test.mdf
> Then I thought I can delete these two testing files.
> It didn't allow me to do so and gave message you can't
> delete them because another program is using it. Then I
> close SQL server, this time I can delete them.
> But when I start SQL server again, I found the NOrthwind
> database is missing, no items and suspect shown there.
> The worst thing is I deleted the backup file too before I
> found this.
> What should I do now to recover the northstar database,
> do I need to reinstall it? and I can't figure out why I
> deleted NOrthwind database in C while I'm not intend to
> do so?
> Thanks for any suggestions
>
>
Friday, March 9, 2012
Recover A Database From Another Hard Disk With A Corrupted Windows 2000 Installation.
Hello,
I am trying to recover a SQL Server 7 database from another hard disk
drive that has a corrupted Windows 2000 Advanced Server installation. I
am not able to repair the corrupted Windows 2000 Advanced Server
installation but the file system is intact. I have installed a new copy
of SQL Server 7 onto a new hard disk and have used the sp_attach_db
system stored procedure to attach the database from the old hard drive
into the new installation of SQL Server 7 on the new hard drive. The
database shows up in the Enterprise Manager View and all the data and
structure are there, but when I try to run a Web site that uses ODBC
connections to the database I get the following error:
ODBC Error Code = 08001 (Unable to connect to data source)
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server
not found.
I have double-checked that all the NTFS permissions, ODBC DSNs, and Web
Server settings are correct and when I run the SQL Server ODBC driver
test at the completion of each ODBC DSN setup, it is successfull.
1) Why am I getting this ODBC error?
2) What steps have I missed in the recovery process?
3) If I have done something wrong recovering my database, can I still
recover it since I still have: a) The old master database file and log
file; b) The old database (non-master) file and log file; c)A backed-up
(Enterprise Manager -> Right Click A Database -> All Tasks -> Backup
Database) copy of the old database (non-master) file; ?
My System Specifications:
-Windows 2000 Advanced Server
-SQL Server 7
-IIS 5.0
-Cold Fusion Server 4.5 Professional Full Edition
Thanks For Your Time,
NathanIn my first post, I gave the wrong error. The error should read:
ODBC Error Code = S0002 (Base table not found)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'dboLogs'.
Sorry for the inconvenience. -Nate|||Nevermind...I have solved the problem myself. The problem was in the
ODBC DSN configuration. When I setup a SQL Server 7 ODBC DSN, there is
a checkbox "Change the default database to:" with a disabled drop-down
list showing the name of the database that I was trying to connect to.
The way the name is displayed, it looks as though SQL Server is
connecting to this database by default. In reality, I had to checkmark
the box for SQL Server to connect to this database.
I hope this helps the next database professional from wasting as much
time as I (...'d hate to admit that I...) did.
Monday, February 20, 2012
Record lost with UPDATE statements
Hi,
I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.
I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.
I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements.
But somehow ,ONCE IN A WHILE, when executing the stored procedure
with about 1000 rows updated, I lost 10-20 records from TABLE_HEAD
(seems like 10-20 records were deleted) , and all data rows in
TABLE_DETAILS were updated correctly (even details of lost rows of
TABLE_HEAD).
In update procedure, I update both part of primary key and other
columns with having WHERE condition.
Please help , I really don't know why this happens.
Thanks in advance
Nipon WongtrakulHave you checked if there are any triggers on the table you are updating?
Test any trigger code to see if it is handling updates of the primary key
columns correctly.
--
David Portas
SQL Server MVP
--|||Hi
Using a surrogate key will remove the need to update the details table.
Posting DDL (Create table etc) and example data (as insert statements) along
with the statements you are using may help to highlight other problems.
Also once using profiler may show something being missed.
John
"Nipon" <niponw@.yahoo.com> wrote in message
news:4c537316.0406141729.6e3cee68@.posting.google.c om...
> Hi,
> I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.
> I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
> going to update by using a stored procedure with UPDATE statements.
> But somehow ,ONCE IN A WHILE, when executing the stored procedure
> with about 1000 rows updated, I lost 10-20 records from TABLE_HEAD
> (seems like 10-20 records were deleted) , and all data rows in
> TABLE_DETAILS were updated correctly (even details of lost rows of
> TABLE_HEAD).
> In update procedure, I update both part of primary key and other
> columns with having WHERE condition.
> Please help , I really don't know why this happens.
> Thanks in advance
> Nipon Wongtrakul|||>> I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
>> But somehow ,ONCE IN A WHILE, when executing the stored procedure
with about 1000 rows updated, I lost 10-20 records [sic] from
TABLE_HEAD (seems like 10-20 records [sic] were deleted), and all data
rows in TABLE_DETAILS were updated correctly (even details of lost
rows of TABLE_HEAD). <<
If there is no header for a set of details, then the ON DELETE CASCADE
should have removed them for you. Likewise, the ON UPDATE CASCADE
action should have done some of the work for you between the PK-FK.
>> In update procedure, I update both part of primary key and other
columns with having WHERE condition. <<
We need to see code to debug it. It could be:
1) If you use a locator like IDENTITY as a key, and then update the
natural key, you can get the relationships out of synch.
2) There is a TRIGGER doing strange things.
3) The updates are not in the same transaction
4) The UPDATE has a FROM or other proprietary clause that does strange
things.
5) Something else.
Subscribe to:
Posts (Atom)