When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
the transaction log just keep getting bigger and bigger.
Will doing a Complete backup and backup the transcation log as part of the
maintenance plan truncate the transaction log ?
Thanks.> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
Truncate - yes.
Stop the growth - probably.
Make it smaller - no
Please read the information in BOL regarding the recovery model and how to
choose one that fits your needs. It sounds like you haven't given
sufficient thought to this entire process - including how you use the
various backups to actually recover your system in the event of a loss or
failure.|||Tlog backup will truncate the inactive part i.e. commited transactions of
the log file and minimize the log. Full backup do not shrink tlog.
"fniles" <fniles@.pfmail.com> wrote in message
news:On6exDdJHHA.4384@.TK2MSFTNGP03.phx.gbl...
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
> Thanks.
>|||fniles (fniles@.pfmail.com) writes:
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
If you never back it up, that's the way it goes.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
Yes, but as other have said, it will not shrink it. To shrink you need to
use DBCC SHRINKFILE, but you should only shrink if have been any exceptional
event, as for instance not having backed up the transaction log for the
last six months. If you back up the log regularly, the log will grow to
the size it needs, and you should let it stay that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hello,
If it is FULL recovery; please schedule a FULL database backup once a day
and schedule the transaction log backup every 30 minutes or so...
This will help you to recover the database and keep the LDF file grown in
control.
Thanks
Hari
"fniles" <fniles@.pfmail.com> wrote in message
news:On6exDdJHHA.4384@.TK2MSFTNGP03.phx.gbl...
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
> Thanks.
>
2012年2月26日星期日
Full Recovery Model and Bulk Recovery Model.
What is the major difference b/w Full Recovery Model and Bulk Recovery
Model.
Thanks
NOOR
On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>What is the major difference b/w Full Recovery Model and Bulk Recovery
>Model.
>Thanks
>NOOR
>
Hi Noor,
This is explained in Books Online. Use the index to find "Recovery
models", then select the chapter "Selecting a Recovery Model" for a short
breakdown on the differences. You can follow the links in that article to
find more detailed explanations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
or Full Log or Both ?
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s8h6h05841p5816abr8i1i1cao9ltf0us9@.4ax.com...
> On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>
> Hi Noor,
> This is explained in Books Online. Use the index to find "Recovery
> models", then select the chapter "Selecting a Recovery Model" for a short
> breakdown on the differences. You can follow the links in that article to
> find more detailed explanations.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
>Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
>or Full Log or Both ?
>Thanks
>NOOR
Hi Noor,
I'm sorry, but I don't understand your question. If you ask again using
different words, I might be able to help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Basically I still don't understand the major difference of Bulk Recovery
Model and Full Recovery Model, can you distiguish in one sentence like what
is the major difference b/w in both.
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
Log
> Hi Noor,
> I'm sorry, but I don't understand your question. If you ask again using
> different words, I might be able to help.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||There are certain operations that under the right conditions can do what is
referred to as a "Minimally logged" operation. These include operations
such as a Bulk Load (BCP, DTS, BULK INSERT), Create and Reindexing and
SELECT INTO. Normally in "Full" recovery mode any operation is fully
logged. Meaning each and every row or data change is logged to the
transaction log so you can fully recover (either roll back or forward) from
it. When you have a "Minimally logged" operation and the Recovery Model is
set to "Bulk Logged" or "Simple" SQL Server will only log a minimal amount
of information and not the actual data. For instance if you issue a Bulk
Insert and insert 1 million rows in a ""Minimally logged" operation the only
thing that gets logged to the transaction log is the page or extent that was
modified during the operation. So instead of a million rows logged you have
just a few entries. This makes for a much more efficient data load. But
when you backup up the log file it will then copy the entire extent that was
touched in this operation to the backup file resulting in a huge log backup
file. So if you have to recover from after a "Minimally logged" operation
you still can but not to a point in time as in "Full" mode. This is due to
the fact the entire extent is replaced and not just the row or page that was
modified. Certain reindexing commands can do a "Minimally logged" operation
if in "Bulk" or "Simple" mode as well. There is more information in BOL for
these and you want to look up "minimally logged bulk copy" in BOL as well to
see what conditions must be met in order to do a minimally logged load.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:OevS4z7eEHA.3028@.TK2MSFTNGP12.phx.gbl...
> Basically I still don't understand the major difference of Bulk Recovery
> Model and Full Recovery Model, can you distiguish in one sentence like
what[vbcol=seagreen]
> is the major difference b/w in both.
> Thanks
> NOOR
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com...
Bulk
> Log
>
|||On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
>Basically I still don't understand the major difference of Bulk Recovery
>Model and Full Recovery Model, can you distiguish in one sentence like what
>is the major difference b/w in both.
>Thanks
>NOOR
Hi Noor,
The major difference in one sentence:
"The Bulk-Logged model provides (...) lower log space consumption for
certain large-scale operations (for example, create index or bulk copy).
It does this at the expense of some flexibility of point-in-time
recovery."
In a few sentences:
"The Bulk-Logged Recovery model provides protection against media failure
combined with the best performance and minimal log space usage for certain
large-scale or bulk copy operations. These operations are minimally
logged:
* SELECT INTO.
* Bulk load operations (bcp and BULK INSERT).
* CREATE INDEX (including indexed views).
* text and image operations (WRITETEXT and UPDATETEXT).
In a Bulk-Logged Recovery model, the data loss exposure for these bulk
copy operations is greater than in the Full Recovery model. While the bulk
copy operations are fully logged under the Full Recovery model, they are
minimally logged and cannot be controlled on an operation-by-operation
basis under the Bulk-Logged Recovery model."
Both these quotes are directly from Books Online. Did you follow my advise
to read up on this subject first?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Andrew and Hugo.
Noor
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7l57h0p2gbd7qpcfrsfdpevogc8idbdeng@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
what
> Hi Noor,
> The major difference in one sentence:
> "The Bulk-Logged model provides (...) lower log space consumption for
> certain large-scale operations (for example, create index or bulk copy).
> It does this at the expense of some flexibility of point-in-time
> recovery."
> In a few sentences:
> "The Bulk-Logged Recovery model provides protection against media failure
> combined with the best performance and minimal log space usage for certain
> large-scale or bulk copy operations. These operations are minimally
> logged:
> * SELECT INTO.
> * Bulk load operations (bcp and BULK INSERT).
> * CREATE INDEX (including indexed views).
> * text and image operations (WRITETEXT and UPDATETEXT).
> In a Bulk-Logged Recovery model, the data loss exposure for these bulk
> copy operations is greater than in the Full Recovery model. While the bulk
> copy operations are fully logged under the Full Recovery model, they are
> minimally logged and cannot be controlled on an operation-by-operation
> basis under the Bulk-Logged Recovery model."
> Both these quotes are directly from Books Online. Did you follow my advise
> to read up on this subject first?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Model.
Thanks
NOOR
On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>What is the major difference b/w Full Recovery Model and Bulk Recovery
>Model.
>Thanks
>NOOR
>
Hi Noor,
This is explained in Books Online. Use the index to find "Recovery
models", then select the chapter "Selecting a Recovery Model" for a short
breakdown on the differences. You can follow the links in that article to
find more detailed explanations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
or Full Log or Both ?
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s8h6h05841p5816abr8i1i1cao9ltf0us9@.4ax.com...
> On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>
> Hi Noor,
> This is explained in Books Online. Use the index to find "Recovery
> models", then select the chapter "Selecting a Recovery Model" for a short
> breakdown on the differences. You can follow the links in that article to
> find more detailed explanations.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
>Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
>or Full Log or Both ?
>Thanks
>NOOR
Hi Noor,
I'm sorry, but I don't understand your question. If you ask again using
different words, I might be able to help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Basically I still don't understand the major difference of Bulk Recovery
Model and Full Recovery Model, can you distiguish in one sentence like what
is the major difference b/w in both.
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
Log
> Hi Noor,
> I'm sorry, but I don't understand your question. If you ask again using
> different words, I might be able to help.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||There are certain operations that under the right conditions can do what is
referred to as a "Minimally logged" operation. These include operations
such as a Bulk Load (BCP, DTS, BULK INSERT), Create and Reindexing and
SELECT INTO. Normally in "Full" recovery mode any operation is fully
logged. Meaning each and every row or data change is logged to the
transaction log so you can fully recover (either roll back or forward) from
it. When you have a "Minimally logged" operation and the Recovery Model is
set to "Bulk Logged" or "Simple" SQL Server will only log a minimal amount
of information and not the actual data. For instance if you issue a Bulk
Insert and insert 1 million rows in a ""Minimally logged" operation the only
thing that gets logged to the transaction log is the page or extent that was
modified during the operation. So instead of a million rows logged you have
just a few entries. This makes for a much more efficient data load. But
when you backup up the log file it will then copy the entire extent that was
touched in this operation to the backup file resulting in a huge log backup
file. So if you have to recover from after a "Minimally logged" operation
you still can but not to a point in time as in "Full" mode. This is due to
the fact the entire extent is replaced and not just the row or page that was
modified. Certain reindexing commands can do a "Minimally logged" operation
if in "Bulk" or "Simple" mode as well. There is more information in BOL for
these and you want to look up "minimally logged bulk copy" in BOL as well to
see what conditions must be met in order to do a minimally logged load.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:OevS4z7eEHA.3028@.TK2MSFTNGP12.phx.gbl...
> Basically I still don't understand the major difference of Bulk Recovery
> Model and Full Recovery Model, can you distiguish in one sentence like
what[vbcol=seagreen]
> is the major difference b/w in both.
> Thanks
> NOOR
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com...
Bulk
> Log
>
|||On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
>Basically I still don't understand the major difference of Bulk Recovery
>Model and Full Recovery Model, can you distiguish in one sentence like what
>is the major difference b/w in both.
>Thanks
>NOOR
Hi Noor,
The major difference in one sentence:
"The Bulk-Logged model provides (...) lower log space consumption for
certain large-scale operations (for example, create index or bulk copy).
It does this at the expense of some flexibility of point-in-time
recovery."
In a few sentences:
"The Bulk-Logged Recovery model provides protection against media failure
combined with the best performance and minimal log space usage for certain
large-scale or bulk copy operations. These operations are minimally
logged:
* SELECT INTO.
* Bulk load operations (bcp and BULK INSERT).
* CREATE INDEX (including indexed views).
* text and image operations (WRITETEXT and UPDATETEXT).
In a Bulk-Logged Recovery model, the data loss exposure for these bulk
copy operations is greater than in the Full Recovery model. While the bulk
copy operations are fully logged under the Full Recovery model, they are
minimally logged and cannot be controlled on an operation-by-operation
basis under the Bulk-Logged Recovery model."
Both these quotes are directly from Books Online. Did you follow my advise
to read up on this subject first?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Andrew and Hugo.
Noor
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7l57h0p2gbd7qpcfrsfdpevogc8idbdeng@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
what
> Hi Noor,
> The major difference in one sentence:
> "The Bulk-Logged model provides (...) lower log space consumption for
> certain large-scale operations (for example, create index or bulk copy).
> It does this at the expense of some flexibility of point-in-time
> recovery."
> In a few sentences:
> "The Bulk-Logged Recovery model provides protection against media failure
> combined with the best performance and minimal log space usage for certain
> large-scale or bulk copy operations. These operations are minimally
> logged:
> * SELECT INTO.
> * Bulk load operations (bcp and BULK INSERT).
> * CREATE INDEX (including indexed views).
> * text and image operations (WRITETEXT and UPDATETEXT).
> In a Bulk-Logged Recovery model, the data loss exposure for these bulk
> copy operations is greater than in the Full Recovery model. While the bulk
> copy operations are fully logged under the Full Recovery model, they are
> minimally logged and cannot be controlled on an operation-by-operation
> basis under the Bulk-Logged Recovery model."
> Both these quotes are directly from Books Online. Did you follow my advise
> to read up on this subject first?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Full Recovery Model - log doesnt grow?
Hi all,
I have a SQL Server 2000 database that is using the Full recovery
model. The database is purely receiving inserts (and plenty of them)
with maybe some view/table creation for reporting.
In this state I would expect the log to grow ad infinitum but it gets
to about 32% used and then empties.
The log is not being backed up at all so am I missing something else?
Cheers
Deedeebeeay@.gmail.com (deebeeay@.gmail.com) writes:
> I have a SQL Server 2000 database that is using the Full recovery
> model. The database is purely receiving inserts (and plenty of them)
> with maybe some view/table creation for reporting.
> In this state I would expect the log to grow ad infinitum but it gets
> to about 32% used and then empties.
> The log is not being backed up at all so am I missing something else?
Did you ever take a backup of the database since you set it to full
recovery? I believe you are effectively in simple more, until you have
a full backup. To wit, without a backup of the database, the log is very
useful on its own.
--
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|||Ah that's probably it. I've only just been given visibility of the
database and I don't think a backup has ever been done.
Many thanks for your help!
Full recovery backup
Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
Ant
Hello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>
|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?
|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
Ant
Hello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>
|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?
|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
Full recovery backup
Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how ca
n
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/feat...cle.php/3530616
http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/feat...cle.php/3530616
> http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how ca
n
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/feat...cle.php/3530616
http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/feat...cle.php/3530616
> http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
Full recovery backup
Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how
> > can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>> Hello,
>> Point in time:-
>> A point in time recovery is restoring a database to a specified date and
>> time. When you have completed a point in time recovery, your database
>> will be in the state it was at the specific date and time you identified
>> when restoring your database. A point in time recovery is a method to
>> recover
>> your database to any point in time since the last database backup
>> Take a look into below URL for datails:-
>> http://www.databasejournal.com/features/mssql/article.php/3530616
>> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>>
>> Thanks
>> Hari
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>> > Hi,
>> > I've just discovered the 'Recovery model options' in properties page of
>> > a
>> > table in Enterprise manager.
>> >
>> > I checked books on line which stated that Full recovery mode ' can
>> > recover
>> > from any point in time'. Are they referring to backup/restores? If so
>> > how
>> > can
>> > this be done at any point in time. Otherwise, what are they referring
>> > to?
>> >
>> > Thanks for your time in answering this question
>> > Ant
>>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how
> > can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>> Hello,
>> Point in time:-
>> A point in time recovery is restoring a database to a specified date and
>> time. When you have completed a point in time recovery, your database
>> will be in the state it was at the specific date and time you identified
>> when restoring your database. A point in time recovery is a method to
>> recover
>> your database to any point in time since the last database backup
>> Take a look into below URL for datails:-
>> http://www.databasejournal.com/features/mssql/article.php/3530616
>> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>>
>> Thanks
>> Hari
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>> > Hi,
>> > I've just discovered the 'Recovery model options' in properties page of
>> > a
>> > table in Enterprise manager.
>> >
>> > I checked books on line which stated that Full recovery mode ' can
>> > recover
>> > from any point in time'. Are they referring to backup/restores? If so
>> > how
>> > can
>> > this be done at any point in time. Otherwise, what are they referring
>> > to?
>> >
>> > Thanks for your time in answering this question
>> > Ant
>>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
Full Model Recovery (Backup)
I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zip backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?
Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?
|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?
Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?
|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>
Full Model Recovery (Backup)
I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zi
p backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:5
9pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm.
At 12:30am I zip the file and remove. This zipped file goes with the previo
us days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to
remove inactive entries at each hour backup. Can I shrink the file after ea
ch hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of
the night it is ove 50gigs. Would a simple model work with a differential wi
th init option work better?
After each differential I would zip and remove the the backup to another dri
ve.
In the event of a disaster I would restore from the Full I did at 12:00am an
d use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup
.
> This controls the growth of transaction log file(LDF). Probably as you do
by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file afte
r
> each hour backup with out damaging the previous backup?
>
>
p backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:5
9pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm.
At 12:30am I zip the file and remove. This zipped file goes with the previo
us days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to
remove inactive entries at each hour backup. Can I shrink the file after ea
ch hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of
the night it is ove 50gigs. Would a simple model work with a differential wi
th init option work better?
After each differential I would zip and remove the the backup to another dri
ve.
In the event of a disaster I would restore from the Full I did at 12:00am an
d use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup
.
> This controls the growth of transaction log file(LDF). Probably as you do
by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file afte
r
> each hour backup with out damaging the previous backup?
>
>
Full Model Recovery (Backup)
I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zip backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> > I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
> zip backup and remove, shrink log file.
> >
> > Along with the FULL backup I run Transaction log backups from 7:00am to
> 11:59pm
> > once an hour. Append the file. The file has grown to over 33gigs by
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> >
> > My question is, how can I keep the transaction log trimmed? I have it set
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> > I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
> zip backup and remove, shrink log file.
> >
> > Along with the FULL backup I run Transaction log backups from 7:00am to
> 11:59pm
> > once an hour. Append the file. The file has grown to over 33gigs by
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> >
> > My question is, how can I keep the transaction log trimmed? I have it set
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>
2012年2月24日星期五
Full Database Back and Diffrential Database Backup
I have one full database back and 4 differential database backup.
(Recovery model is simple) In the recovery process, I have restored the
full database backup with No_recovery option. After that should i
restore all the 4 differential backup or the most recent differential
backup? Please explain.
RegardsHi
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Praveen" <apveen@.gmail.com> wrote in message
news:1143010815.337892.295070@.e56g2000cwe.googlegroups.com...
>I have one full database back and 4 differential database backup.
> (Recovery model is simple) In the recovery process, I have restored the
> full database backup with No_recovery option. After that should i
> restore all the 4 differential backup or the most recent differential
> backup? Please explain.
> Regards
>|||You have to restore first full backup and latest differential backup
you have taken after last full backup.
Regards
Amish Shah|||thank you for your replay. but i have a doubt. how only full database
backup and most recent diffrentail backup is enough to recover the
database?. why the intermediate diffrential backups are not required?.
Internally what is happening?.
BOL does not specify the backgroud process of recovery process while
applying the diffrential backup after full database back up?.|||Differential backups always store the changes from the last FULL backup. So
the previous Differential backups are not needed only the last one.
--
Andrew J. Kelly SQL MVP
"Praveen" <apveen@.gmail.com> wrote in message
news:1143029248.422816.10680@.j33g2000cwa.googlegroups.com...
> thank you for your replay. but i have a doubt. how only full database
> backup and most recent diffrentail backup is enough to recover the
> database?. why the intermediate diffrential backups are not required?.
> Internally what is happening?.
> BOL does not specify the backgroud process of recovery process while
> applying the diffrential backup after full database back up?.
>|||I got the answer from this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
thanks for all.
(Recovery model is simple) In the recovery process, I have restored the
full database backup with No_recovery option. After that should i
restore all the 4 differential backup or the most recent differential
backup? Please explain.
RegardsHi
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Praveen" <apveen@.gmail.com> wrote in message
news:1143010815.337892.295070@.e56g2000cwe.googlegroups.com...
>I have one full database back and 4 differential database backup.
> (Recovery model is simple) In the recovery process, I have restored the
> full database backup with No_recovery option. After that should i
> restore all the 4 differential backup or the most recent differential
> backup? Please explain.
> Regards
>|||You have to restore first full backup and latest differential backup
you have taken after last full backup.
Regards
Amish Shah|||thank you for your replay. but i have a doubt. how only full database
backup and most recent diffrentail backup is enough to recover the
database?. why the intermediate diffrential backups are not required?.
Internally what is happening?.
BOL does not specify the backgroud process of recovery process while
applying the diffrential backup after full database back up?.|||Differential backups always store the changes from the last FULL backup. So
the previous Differential backups are not needed only the last one.
--
Andrew J. Kelly SQL MVP
"Praveen" <apveen@.gmail.com> wrote in message
news:1143029248.422816.10680@.j33g2000cwa.googlegroups.com...
> thank you for your replay. but i have a doubt. how only full database
> backup and most recent diffrentail backup is enough to recover the
> database?. why the intermediate diffrential backups are not required?.
> Internally what is happening?.
> BOL does not specify the backgroud process of recovery process while
> applying the diffrential backup after full database back up?.
>|||I got the answer from this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
thanks for all.
Full Database Back and Diffrential Database Backup
I have one full database back and 4 differential database backup.
(Recovery model is simple) In the recovery process, I have restored the
full database backup with No_recovery option. After that should i
restore all the 4 differential backup or the most recent differential
backup? Please explain.
RegardsHi
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
"Praveen" <apveen@.gmail.com> wrote in message
news:1143010815.337892.295070@.e56g2000cwe.googlegroups.com...
>I have one full database back and 4 differential database backup.
> (Recovery model is simple) In the recovery process, I have restored the
> full database backup with No_recovery option. After that should i
> restore all the 4 differential backup or the most recent differential
> backup? Please explain.
> Regards
>|||You have to restore first full backup and latest differential backup
you have taken after last full backup.
Regards
Amish Shah|||thank you for your replay. but i have a doubt. how only full database
backup and most recent diffrentail backup is enough to recover the
database?. why the intermediate diffrential backups are not required?.
Internally what is happening?.
BOL does not specify the backgroud process of recovery process while
applying the diffrential backup after full database back up?.|||Differential backups always store the changes from the last FULL backup. So
the previous Differential backups are not needed only the last one.
Andrew J. Kelly SQL MVP
"Praveen" <apveen@.gmail.com> wrote in message
news:1143029248.422816.10680@.j33g2000cwa.googlegroups.com...
> thank you for your replay. but i have a doubt. how only full database
> backup and most recent diffrentail backup is enough to recover the
> database?. why the intermediate diffrential backups are not required?.
> Internally what is happening?.
> BOL does not specify the backgroud process of recovery process while
> applying the diffrential backup after full database back up?.
>|||I got the answer from this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
thanks for all.
(Recovery model is simple) In the recovery process, I have restored the
full database backup with No_recovery option. After that should i
restore all the 4 differential backup or the most recent differential
backup? Please explain.
RegardsHi
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
"Praveen" <apveen@.gmail.com> wrote in message
news:1143010815.337892.295070@.e56g2000cwe.googlegroups.com...
>I have one full database back and 4 differential database backup.
> (Recovery model is simple) In the recovery process, I have restored the
> full database backup with No_recovery option. After that should i
> restore all the 4 differential backup or the most recent differential
> backup? Please explain.
> Regards
>|||You have to restore first full backup and latest differential backup
you have taken after last full backup.
Regards
Amish Shah|||thank you for your replay. but i have a doubt. how only full database
backup and most recent diffrentail backup is enough to recover the
database?. why the intermediate diffrential backups are not required?.
Internally what is happening?.
BOL does not specify the backgroud process of recovery process while
applying the diffrential backup after full database back up?.|||Differential backups always store the changes from the last FULL backup. So
the previous Differential backups are not needed only the last one.
Andrew J. Kelly SQL MVP
"Praveen" <apveen@.gmail.com> wrote in message
news:1143029248.422816.10680@.j33g2000cwa.googlegroups.com...
> thank you for your replay. but i have a doubt. how only full database
> backup and most recent diffrentail backup is enough to recover the
> database?. why the intermediate diffrential backups are not required?.
> Internally what is happening?.
> BOL does not specify the backgroud process of recovery process while
> applying the diffrential backup after full database back up?.
>|||I got the answer from this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
thanks for all.
Full Database Back and Diffrential Database Backup
I have one full database back and 4 differential database backup.
(Recovery model is simple) In the recovery process, I have restored the
full database backup with No_recovery option. After that should i
restore all the 4 differential backup or the most recent differential
backup? Please explain.
Regards
Hi
http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
--administaiting best practices
"Praveen" <apveen@.gmail.com> wrote in message
news:1143010815.337892.295070@.e56g2000cwe.googlegr oups.com...
>I have one full database back and 4 differential database backup.
> (Recovery model is simple) In the recovery process, I have restored the
> full database backup with No_recovery option. After that should i
> restore all the 4 differential backup or the most recent differential
> backup? Please explain.
> Regards
>
|||You have to restore first full backup and latest differential backup
you have taken after last full backup.
Regards
Amish Shah
|||thank you for your replay. but i have a doubt. how only full database
backup and most recent diffrentail backup is enough to recover the
database?. why the intermediate diffrential backups are not required?.
Internally what is happening?.
BOL does not specify the backgroud process of recovery process while
applying the diffrential backup after full database back up?.
|||Differential backups always store the changes from the last FULL backup. So
the previous Differential backups are not needed only the last one.
Andrew J. Kelly SQL MVP
"Praveen" <apveen@.gmail.com> wrote in message
news:1143029248.422816.10680@.j33g2000cwa.googlegro ups.com...
> thank you for your replay. but i have a doubt. how only full database
> backup and most recent diffrentail backup is enough to recover the
> database?. why the intermediate diffrential backups are not required?.
> Internally what is happening?.
> BOL does not specify the backgroud process of recovery process while
> applying the diffrential backup after full database back up?.
>
|||I got the answer from this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
thanks for all.
(Recovery model is simple) In the recovery process, I have restored the
full database backup with No_recovery option. After that should i
restore all the 4 differential backup or the most recent differential
backup? Please explain.
Regards
Hi
http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
--administaiting best practices
"Praveen" <apveen@.gmail.com> wrote in message
news:1143010815.337892.295070@.e56g2000cwe.googlegr oups.com...
>I have one full database back and 4 differential database backup.
> (Recovery model is simple) In the recovery process, I have restored the
> full database backup with No_recovery option. After that should i
> restore all the 4 differential backup or the most recent differential
> backup? Please explain.
> Regards
>
|||You have to restore first full backup and latest differential backup
you have taken after last full backup.
Regards
Amish Shah
|||thank you for your replay. but i have a doubt. how only full database
backup and most recent diffrentail backup is enough to recover the
database?. why the intermediate diffrential backups are not required?.
Internally what is happening?.
BOL does not specify the backgroud process of recovery process while
applying the diffrential backup after full database back up?.
|||Differential backups always store the changes from the last FULL backup. So
the previous Differential backups are not needed only the last one.
Andrew J. Kelly SQL MVP
"Praveen" <apveen@.gmail.com> wrote in message
news:1143029248.422816.10680@.j33g2000cwa.googlegro ups.com...
> thank you for your replay. but i have a doubt. how only full database
> backup and most recent diffrentail backup is enough to recover the
> database?. why the intermediate diffrential backups are not required?.
> Internally what is happening?.
> BOL does not specify the backgroud process of recovery process while
> applying the diffrential backup after full database back up?.
>
|||I got the answer from this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
thanks for all.
full backup and transaction log truncate
Hi,
I'm
about how the backup system works in SQL Server. I'm using
the BULK LOGGED recovery model, I do regular full backups but my log
file is growing very large.
BOL says:
Full database backup, which backs up the entire database including the
transaction log
and it also says the BACKUP LOG truncates the log file, so I assumed
that BACKUP DATABASE would truncate the log file as well, but it doesn't
seem to be doing that. So the question is:
Does doing a full backup using BACKUP DATABASE truncate the log file?
Although I might be by the statement in BOL I actually hope
that BACKUP DATABASE does not truncate the log file, that way I can do
full backups whenever I want, for whatever reason and these backups will
not interfere with an automated full/differential/trans log backup system.
Thanks.BACKUP DATABASE does not truncate the inactive log portion. This means that
full backups do not interrupt a log backup sequence. If your most recent
backup is bad or incomplete, you can use an older backup as a starting point
and restore more logs to get back to the desired recovery point in time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John" <no@.spam> wrote in message
news:edF0K$deGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log file
> is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed that
> BACKUP DATABASE would truncate the log file as well, but it doesn't seem
> to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope that
> BACKUP DATABASE does not truncate the log file, that way I can do full
> backups whenever I want, for whatever reason and these backups will not
> interfere with an automated full/differential/trans log backup system.
> Thanks.|||John,
You have to backup the log in order to allow sql server to re-use the VLF
before the MinLSN. If these VLFs can not be reused and we reach the end of
the transaction log, then sql server will have to expand it.
Truncating the Transaction Log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/?kbid=873235
AMB
"John" wrote:
> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log
> file is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed
> that BACKUP DATABASE would truncate the log file as well, but it doesn't
> seem to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope
> that BACKUP DATABASE does not truncate the log file, that way I can do
> full backups whenever I want, for whatever reason and these backups will
> not interfere with an automated full/differential/trans log backup system.
> Thanks.
>
I'm
about how the backup system works in SQL Server. I'm usingthe BULK LOGGED recovery model, I do regular full backups but my log
file is growing very large.
BOL says:
Full database backup, which backs up the entire database including the
transaction log
and it also says the BACKUP LOG truncates the log file, so I assumed
that BACKUP DATABASE would truncate the log file as well, but it doesn't
seem to be doing that. So the question is:
Does doing a full backup using BACKUP DATABASE truncate the log file?
Although I might be
by the statement in BOL I actually hopethat BACKUP DATABASE does not truncate the log file, that way I can do
full backups whenever I want, for whatever reason and these backups will
not interfere with an automated full/differential/trans log backup system.
Thanks.BACKUP DATABASE does not truncate the inactive log portion. This means that
full backups do not interrupt a log backup sequence. If your most recent
backup is bad or incomplete, you can use an older backup as a starting point
and restore more logs to get back to the desired recovery point in time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John" <no@.spam> wrote in message
news:edF0K$deGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm
about how the backup system works in SQL Server. I'm using> the BULK LOGGED recovery model, I do regular full backups but my log file
> is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed that
> BACKUP DATABASE would truncate the log file as well, but it doesn't seem
> to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be
by the statement in BOL I actually hope that> BACKUP DATABASE does not truncate the log file, that way I can do full
> backups whenever I want, for whatever reason and these backups will not
> interfere with an automated full/differential/trans log backup system.
> Thanks.|||John,
You have to backup the log in order to allow sql server to re-use the VLF
before the MinLSN. If these VLFs can not be reused and we reach the end of
the transaction log, then sql server will have to expand it.
Truncating the Transaction Log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/?kbid=873235
AMB
"John" wrote:
> Hi,
> I'm
about how the backup system works in SQL Server. I'm using> the BULK LOGGED recovery model, I do regular full backups but my log
> file is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed
> that BACKUP DATABASE would truncate the log file as well, but it doesn't
> seem to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be
by the statement in BOL I actually hope> that BACKUP DATABASE does not truncate the log file, that way I can do
> full backups whenever I want, for whatever reason and these backups will
> not interfere with an automated full/differential/trans log backup system.
> Thanks.
>
订阅：
博文 (Atom)