Hi
Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
with options for BACKUP LOG statement.NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to
attempt the backup regardless of the state of the database. Consequently, a
backup taken with NO_TRUNCATE might have incomplete metadata. This option
allows backing up the log in situations where the database is damaged.
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state.
NORECOVERY is useful when failing over to a secondary database or when
saving the tail of the log before a RESTORE operation.
For more info about BACKUP command go to the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> Hi
> Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> with options for BACKUP LOG statement.
>|||Hi Ekrem,
Can you please explain the difference with an example.
"Ekrem ?nsoy" wrote:
> NO_TRUNCATE
> Specifies that the log not be truncated and causes the Database Engine to
> attempt the backup regardless of the state of the database. Consequently,
a
> backup taken with NO_TRUNCATE might have incomplete metadata. This option
> allows backing up the log in situations where the database is damaged.
> NORECOVERY
> Backs up the tail of the log and leaves the database in the RESTORING stat
e.
> NORECOVERY is useful when failing over to a secondary database or when
> saving the tail of the log before a RESTORE operation.
> For more info about BACKUP command go to the following link:
> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
> --
> Ekrem ?nsoy
>
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
>|||There are three ways of Recovery State:
1-RECOVERY
2-NORECOVERY
3-STANDBY
If you restore your database in recovery, then your database will be online
and your users will be able to perform any operation against it.
If you restore your database in NORECOVERY then your users will not be able
to query your database.
If you restore your database in STANDBY then your users will be able to use
only SELECT against it.
Every recovery state has a purpose. You should read about this from BOL for
more information.
For example NORECOVERY and STANDBY states are being used in Log Shipping. If
you use NORECOVERY for your Log Shipping solution then your users will not
be able to query against the database on your secondary server. If you
restore your secondary database then your users will be able to query (only
SELECT) against it however your users will be disconnected each time for the
new transactions to be applied to the secondary database.
Example for no_truncate:
BACKUP LOG AdventureWorks TO DISK = N'C:\test\tiesto.bak' WITH NO_TRUNCATE
This hint is used for inaccessible database. Tibor has an article about
this, you can read it for more info:
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AFC8BD66-6474-4837-A80A-B9D3FF0068E3@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> Can you please explain the difference with an example.
> "Ekrem ?nsoy" wrote:
>
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Tuesday, March 27, 2012
Difference between NORECOVERY and NO_TRUNCATE
Hi
Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
with options for BACKUP LOG statement.NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to
attempt the backup regardless of the state of the database. Consequently, a
backup taken with NO_TRUNCATE might have incomplete metadata. This option
allows backing up the log in situations where the database is damaged.
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state.
NORECOVERY is useful when failing over to a secondary database or when
saving the tail of the log before a RESTORE operation.
For more info about BACKUP command go to the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
--
Ekrem Ã?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> Hi
> Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> with options for BACKUP LOG statement.
>|||Hi Ekrem,
Can you please explain the difference with an example.
"Ekrem Ã?nsoy" wrote:
> NO_TRUNCATE
> Specifies that the log not be truncated and causes the Database Engine to
> attempt the backup regardless of the state of the database. Consequently, a
> backup taken with NO_TRUNCATE might have incomplete metadata. This option
> allows backing up the log in situations where the database is damaged.
> NORECOVERY
> Backs up the tail of the log and leaves the database in the RESTORING state.
> NORECOVERY is useful when failing over to a secondary database or when
> saving the tail of the log before a RESTORE operation.
> For more info about BACKUP command go to the following link:
> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
> --
> Ekrem Ã?nsoy
>
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> > Hi
> >
> > Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> > with options for BACKUP LOG statement.
> >
> >
>|||There are three ways of Recovery State:
1-RECOVERY
2-NORECOVERY
3-STANDBY
If you restore your database in recovery, then your database will be online
and your users will be able to perform any operation against it.
If you restore your database in NORECOVERY then your users will not be able
to query your database.
If you restore your database in STANDBY then your users will be able to use
only SELECT against it.
Every recovery state has a purpose. You should read about this from BOL for
more information.
For example NORECOVERY and STANDBY states are being used in Log Shipping. If
you use NORECOVERY for your Log Shipping solution then your users will not
be able to query against the database on your secondary server. If you
restore your secondary database then your users will be able to query (only
SELECT) against it however your users will be disconnected each time for the
new transactions to be applied to the secondary database.
Example for no_truncate:
BACKUP LOG AdventureWorks TO DISK = N'C:\test\tiesto.bak' WITH NO_TRUNCATE
This hint is used for inaccessible database. Tibor has an article about
this, you can read it for more info:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Ekrem Ã?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AFC8BD66-6474-4837-A80A-B9D3FF0068E3@.microsoft.com...
> Hi Ekrem,
> Can you please explain the difference with an example.
> "Ekrem Ã?nsoy" wrote:
>> NO_TRUNCATE
>> Specifies that the log not be truncated and causes the Database Engine to
>> attempt the backup regardless of the state of the database. Consequently,
>> a
>> backup taken with NO_TRUNCATE might have incomplete metadata. This option
>> allows backing up the log in situations where the database is damaged.
>> NORECOVERY
>> Backs up the tail of the log and leaves the database in the RESTORING
>> state.
>> NORECOVERY is useful when failing over to a secondary database or when
>> saving the tail of the log before a RESTORE operation.
>> For more info about BACKUP command go to the following link:
>> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
>> --
>> Ekrem Ã?nsoy
>>
>> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
>> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
>> > Hi
>> >
>> > Can some body explain me the difference between NORECOVERY and
>> > NO_TRUNCATE
>> > with options for BACKUP LOG statement.
>> >
>> >sql
Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
with options for BACKUP LOG statement.NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to
attempt the backup regardless of the state of the database. Consequently, a
backup taken with NO_TRUNCATE might have incomplete metadata. This option
allows backing up the log in situations where the database is damaged.
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state.
NORECOVERY is useful when failing over to a secondary database or when
saving the tail of the log before a RESTORE operation.
For more info about BACKUP command go to the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
--
Ekrem Ã?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> Hi
> Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> with options for BACKUP LOG statement.
>|||Hi Ekrem,
Can you please explain the difference with an example.
"Ekrem Ã?nsoy" wrote:
> NO_TRUNCATE
> Specifies that the log not be truncated and causes the Database Engine to
> attempt the backup regardless of the state of the database. Consequently, a
> backup taken with NO_TRUNCATE might have incomplete metadata. This option
> allows backing up the log in situations where the database is damaged.
> NORECOVERY
> Backs up the tail of the log and leaves the database in the RESTORING state.
> NORECOVERY is useful when failing over to a secondary database or when
> saving the tail of the log before a RESTORE operation.
> For more info about BACKUP command go to the following link:
> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
> --
> Ekrem Ã?nsoy
>
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> > Hi
> >
> > Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> > with options for BACKUP LOG statement.
> >
> >
>|||There are three ways of Recovery State:
1-RECOVERY
2-NORECOVERY
3-STANDBY
If you restore your database in recovery, then your database will be online
and your users will be able to perform any operation against it.
If you restore your database in NORECOVERY then your users will not be able
to query your database.
If you restore your database in STANDBY then your users will be able to use
only SELECT against it.
Every recovery state has a purpose. You should read about this from BOL for
more information.
For example NORECOVERY and STANDBY states are being used in Log Shipping. If
you use NORECOVERY for your Log Shipping solution then your users will not
be able to query against the database on your secondary server. If you
restore your secondary database then your users will be able to query (only
SELECT) against it however your users will be disconnected each time for the
new transactions to be applied to the secondary database.
Example for no_truncate:
BACKUP LOG AdventureWorks TO DISK = N'C:\test\tiesto.bak' WITH NO_TRUNCATE
This hint is used for inaccessible database. Tibor has an article about
this, you can read it for more info:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Ekrem Ã?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AFC8BD66-6474-4837-A80A-B9D3FF0068E3@.microsoft.com...
> Hi Ekrem,
> Can you please explain the difference with an example.
> "Ekrem Ã?nsoy" wrote:
>> NO_TRUNCATE
>> Specifies that the log not be truncated and causes the Database Engine to
>> attempt the backup regardless of the state of the database. Consequently,
>> a
>> backup taken with NO_TRUNCATE might have incomplete metadata. This option
>> allows backing up the log in situations where the database is damaged.
>> NORECOVERY
>> Backs up the tail of the log and leaves the database in the RESTORING
>> state.
>> NORECOVERY is useful when failing over to a secondary database or when
>> saving the tail of the log before a RESTORE operation.
>> For more info about BACKUP command go to the following link:
>> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
>> --
>> Ekrem Ã?nsoy
>>
>> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
>> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
>> > Hi
>> >
>> > Can some body explain me the difference between NORECOVERY and
>> > NO_TRUNCATE
>> > with options for BACKUP LOG statement.
>> >
>> >sql
Difference between NORECOVERY and NO_TRUNCATE
Hi
Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
with options for BACKUP LOG statement.
NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to
attempt the backup regardless of the state of the database. Consequently, a
backup taken with NO_TRUNCATE might have incomplete metadata. This option
allows backing up the log in situations where the database is damaged.
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state.
NORECOVERY is useful when failing over to a secondary database or when
saving the tail of the log before a RESTORE operation.
For more info about BACKUP command go to the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> Hi
> Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> with options for BACKUP LOG statement.
>
|||Hi Ekrem,
Can you please explain the difference with an example.
"Ekrem ?nsoy" wrote:
> NO_TRUNCATE
> Specifies that the log not be truncated and causes the Database Engine to
> attempt the backup regardless of the state of the database. Consequently, a
> backup taken with NO_TRUNCATE might have incomplete metadata. This option
> allows backing up the log in situations where the database is damaged.
> NORECOVERY
> Backs up the tail of the log and leaves the database in the RESTORING state.
> NORECOVERY is useful when failing over to a secondary database or when
> saving the tail of the log before a RESTORE operation.
> For more info about BACKUP command go to the following link:
> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
> --
> Ekrem ?nsoy
>
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
>
|||There are three ways of Recovery State:
1-RECOVERY
2-NORECOVERY
3-STANDBY
If you restore your database in recovery, then your database will be online
and your users will be able to perform any operation against it.
If you restore your database in NORECOVERY then your users will not be able
to query your database.
If you restore your database in STANDBY then your users will be able to use
only SELECT against it.
Every recovery state has a purpose. You should read about this from BOL for
more information.
For example NORECOVERY and STANDBY states are being used in Log Shipping. If
you use NORECOVERY for your Log Shipping solution then your users will not
be able to query against the database on your secondary server. If you
restore your secondary database then your users will be able to query (only
SELECT) against it however your users will be disconnected each time for the
new transactions to be applied to the secondary database.
Example for no_truncate:
BACKUP LOG AdventureWorks TO DISK = N'C:\test\tiesto.bak' WITH NO_TRUNCATE
This hint is used for inaccessible database. Tibor has an article about
this, you can read it for more info:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AFC8BD66-6474-4837-A80A-B9D3FF0068E3@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> Can you please explain the difference with an example.
> "Ekrem ?nsoy" wrote:
Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
with options for BACKUP LOG statement.
NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to
attempt the backup regardless of the state of the database. Consequently, a
backup taken with NO_TRUNCATE might have incomplete metadata. This option
allows backing up the log in situations where the database is damaged.
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state.
NORECOVERY is useful when failing over to a secondary database or when
saving the tail of the log before a RESTORE operation.
For more info about BACKUP command go to the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> Hi
> Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> with options for BACKUP LOG statement.
>
|||Hi Ekrem,
Can you please explain the difference with an example.
"Ekrem ?nsoy" wrote:
> NO_TRUNCATE
> Specifies that the log not be truncated and causes the Database Engine to
> attempt the backup regardless of the state of the database. Consequently, a
> backup taken with NO_TRUNCATE might have incomplete metadata. This option
> allows backing up the log in situations where the database is damaged.
> NORECOVERY
> Backs up the tail of the log and leaves the database in the RESTORING state.
> NORECOVERY is useful when failing over to a secondary database or when
> saving the tail of the log before a RESTORE operation.
> For more info about BACKUP command go to the following link:
> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
> --
> Ekrem ?nsoy
>
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
>
|||There are three ways of Recovery State:
1-RECOVERY
2-NORECOVERY
3-STANDBY
If you restore your database in recovery, then your database will be online
and your users will be able to perform any operation against it.
If you restore your database in NORECOVERY then your users will not be able
to query your database.
If you restore your database in STANDBY then your users will be able to use
only SELECT against it.
Every recovery state has a purpose. You should read about this from BOL for
more information.
For example NORECOVERY and STANDBY states are being used in Log Shipping. If
you use NORECOVERY for your Log Shipping solution then your users will not
be able to query against the database on your secondary server. If you
restore your secondary database then your users will be able to query (only
SELECT) against it however your users will be disconnected each time for the
new transactions to be applied to the secondary database.
Example for no_truncate:
BACKUP LOG AdventureWorks TO DISK = N'C:\test\tiesto.bak' WITH NO_TRUNCATE
This hint is used for inaccessible database. Tibor has an article about
this, you can read it for more info:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AFC8BD66-6474-4837-A80A-B9D3FF0068E3@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> Can you please explain the difference with an example.
> "Ekrem ?nsoy" wrote:
Thursday, March 22, 2012
Diff versus Log
I'm confused. Let's say I want to be able to back up to
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?
Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>
|||So either solution should work for me since I've got a one
hour max?
>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from[vbcol=seagreen]
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
you make UPDATES,[vbcol=seagreen]
and DELETEs, then[vbcol=seagreen]
>it
up.[vbcol=seagreen]
time. So you could[vbcol=seagreen]
happened. The Diff backup[vbcol=seagreen]
backup from 1 hour ago[vbcol=seagreen]
you are doing log[vbcol=seagreen]
restore it to 20 minutes[vbcol=seagreen]
you only have to[vbcol=seagreen]
your last good[vbcol=seagreen]
>database
message[vbcol=seagreen]
to[vbcol=seagreen]
Is[vbcol=seagreen]
or
>
>.
>
|||I'm not sure what you mean by 'one hour max'. How often are you making backups? If a backup spans more than one hour, but you want to restore to a certain point in time, you need log backups.
There are many questions that must be answered to come up with the best backup strategy for you; amount of acceptable work loss is only one of the questions. Start by reading everything you can in Books Online about backup and restore, and then take a look at "Microsoft SQL Server 2000 High Availability" http://www.amazon.com/exec/obidos/tg...349825-6773545
The chapter on Backup and Restore was written by Kimberly Tripp and it is the best part of the book!
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message news:02f101c48c7e$357192a0$a401280a@.phx.gbl...[vbcol=seagreen]
> So either solution should work for me since I've got a one
> hour max?
> differential backup is
> Any change to an
> changes comes from
> you make UPDATES,
> and DELETEs, then
> up.
> time. So you could
> happened. The Diff backup
> backup from 1 hour ago
> you are doing log
> restore it to 20 minutes
> you only have to
> your last good
> message
> to
> Is
> or
|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
sql
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?
Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>
|||So either solution should work for me since I've got a one
hour max?
>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from[vbcol=seagreen]
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
you make UPDATES,[vbcol=seagreen]
and DELETEs, then[vbcol=seagreen]
>it
up.[vbcol=seagreen]
time. So you could[vbcol=seagreen]
happened. The Diff backup[vbcol=seagreen]
backup from 1 hour ago[vbcol=seagreen]
you are doing log[vbcol=seagreen]
restore it to 20 minutes[vbcol=seagreen]
you only have to[vbcol=seagreen]
your last good[vbcol=seagreen]
>database
message[vbcol=seagreen]
to[vbcol=seagreen]
Is[vbcol=seagreen]
or
>
>.
>
|||I'm not sure what you mean by 'one hour max'. How often are you making backups? If a backup spans more than one hour, but you want to restore to a certain point in time, you need log backups.
There are many questions that must be answered to come up with the best backup strategy for you; amount of acceptable work loss is only one of the questions. Start by reading everything you can in Books Online about backup and restore, and then take a look at "Microsoft SQL Server 2000 High Availability" http://www.amazon.com/exec/obidos/tg...349825-6773545
The chapter on Backup and Restore was written by Kimberly Tripp and it is the best part of the book!
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message news:02f101c48c7e$357192a0$a401280a@.phx.gbl...[vbcol=seagreen]
> So either solution should work for me since I've got a one
> hour max?
> differential backup is
> Any change to an
> changes comes from
> you make UPDATES,
> and DELETEs, then
> up.
> time. So you could
> happened. The Diff backup
> backup from 1 hour ago
> you are doing log
> restore it to 20 minutes
> you only have to
> your last good
> message
> to
> Is
> or
|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
sql
Wednesday, March 21, 2012
Diff versus Log
I'm confused. Let's say I want to be able to back up to
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>|||So either solution should work for me since I've got a one
hour max?
>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
you make UPDATES,[vbcol=seagreen]
and DELETEs, then[vbcol=seagreen]
>it
up.[vbcol=seagreen]
time. So you could[vbcol=seagreen]
happened. The Diff backup[vbcol=seagreen]
backup from 1 hour ago[vbcol=seagreen]
you are doing log[vbcol=seagreen]
restore it to 20 minutes[vbcol=seagreen]
you only have to[vbcol=seagreen]
your last good[vbcol=seagreen]
>database
message[vbcol=seagreen]
to[vbcol=seagreen]
Is[vbcol=seagreen]
or[vbcol=seagreen]
>
>.
>|||I'm not sure what you mean by 'one hour max'. How often are you making backu
ps? If a backup spans more than one hour, but you want to restore to a certa
in point in time, you need log backups.
There are many questions that must be answered to come up with the best backup strategy
for you; amount of acceptable work loss is only one of the questions. Start by reading
everything you can in Books Online about backup and restore, and then take a look at "
Microsoft SQL Server 2000 High Availability" detail/-/0735619204/104-2349825-6773545" target="_blank">http://www.amazon.com/exec/obidos/t...2349825-6773545
The chapter on Backup and Restore was written by Kimberly Tripp and it is th
e best part of the book!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message news:02f101c48c7e$357192a0$a40
1280a@.phx.gbl...[vbcol=seagreen]
> So either solution should work for me since I've got a one
> hour max?
>
> differential backup is
> Any change to an
> changes comes from
> you make UPDATES,
> and DELETEs, then
> up.
> time. So you could
> happened. The Diff backup
> backup from 1 hour ago
> you are doing log
> restore it to 20 minutes
> you only have to
> your last good
> message
> to
> Is
> or|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>|||So either solution should work for me since I've got a one
hour max?
>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
you make UPDATES,[vbcol=seagreen]
and DELETEs, then[vbcol=seagreen]
>it
up.[vbcol=seagreen]
time. So you could[vbcol=seagreen]
happened. The Diff backup[vbcol=seagreen]
backup from 1 hour ago[vbcol=seagreen]
you are doing log[vbcol=seagreen]
restore it to 20 minutes[vbcol=seagreen]
you only have to[vbcol=seagreen]
your last good[vbcol=seagreen]
>database
message[vbcol=seagreen]
to[vbcol=seagreen]
Is[vbcol=seagreen]
or[vbcol=seagreen]
>
>.
>|||I'm not sure what you mean by 'one hour max'. How often are you making backu
ps? If a backup spans more than one hour, but you want to restore to a certa
in point in time, you need log backups.
There are many questions that must be answered to come up with the best backup strategy
for you; amount of acceptable work loss is only one of the questions. Start by reading
everything you can in Books Online about backup and restore, and then take a look at "
Microsoft SQL Server 2000 High Availability" detail/-/0735619204/104-2349825-6773545" target="_blank">http://www.amazon.com/exec/obidos/t...2349825-6773545
The chapter on Backup and Restore was written by Kimberly Tripp and it is th
e best part of the book!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message news:02f101c48c7e$357192a0$a40
1280a@.phx.gbl...[vbcol=seagreen]
> So either solution should work for me since I've got a one
> hour max?
>
> differential backup is
> Any change to an
> changes comes from
> you make UPDATES,
> and DELETEs, then
> up.
> time. So you could
> happened. The Diff backup
> backup from 1 hour ago
> you are doing log
> restore it to 20 minutes
> you only have to
> your last good
> message
> to
> Is
> or|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
Diff versus Log
I'm confused. Let's say I want to be able to back up to
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
> > I'm confused. Let's say I want to be able to back up to
> > one hour ago on several critical databases, i.e. I'm
> > willing to accept a maxiumum of 1 hour of data loss. Is
> > it better to use Differential backups or Log Backups or
> > does it matter? What are some of the basic pros/cons?
>|||So either solution should work for me since I've got a one
hour max?
>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
>> Differentials only look at the changes in the data. If
you make UPDATES,
>> then this may work for you. If you do a lot of INSERTS
and DELETEs, then
>it
>> may not.
>> A differential is good from the last time it was backed
up.
>> A transaciton log allows you to restore to a point in
time. So you could
>> recover up to 20 mins ago when something bad
happened. The Diff backup
>> won't allow you to do this. You can apply the diff
backup from 1 hour ago
>> and lose everything up to the last 20 mins. (Unless
you are doing log
>> backups as well. Then simply backup the log and
restore it to 20 minutes
>> ago.)
>>
>> Diff recoveries are faster than TLog recoveries in that
you only have to
>> apply the last diff backup. With logs, you restore
your last good
>database
>> backup and then run all the logs in between.
>> Sooo.. It depends on what you are after.
>> HTH
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>> "G2SL" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>> > I'm confused. Let's say I want to be able to back up
to
>> > one hour ago on several critical databases, i.e. I'm
>> > willing to accept a maxiumum of 1 hour of data loss.
Is
>> > it better to use Differential backups or Log Backups
or
>> > does it matter? What are some of the basic pros/cons?
>>
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0D00_01C48C67.9ED536F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm not sure what you mean by 'one hour max'. How often are you making =backups? If a backup spans more than one hour, but you want to restore =to a certain point in time, you need log backups.
There are many questions that must be answered to come up with the best =backup strategy for you; amount of acceptable work loss is only one of =the questions. Start by reading everything you can in Books Online about =backup and restore, and then take a look at "Microsoft SQL Server 2000 =High Availability" =http://www.amazon.com/exec/obidos/tg/detail/-/0735619204/104-2349825-6773=
545
The chapter on Backup and Restore was written by Kimberly Tripp and it =is the best part of the book!
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message =news:02f101c48c7e$357192a0$a401280a@.phx.gbl...
> So either solution should work for me since I've got a one > hour max?
> > >--Original Message--
> >Hi Rick
> >
> >I'm not sure what you're saying here. As far as a > differential backup is
> >concerned, updates are the same as inserts or deletes. > Any change to an
> >extent is backed up in the differential, whether that > changes comes from
> >updating existing rows, or from adding or removing rows.
> >
> >-- > >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> >news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> >> Differentials only look at the changes in the data. If > you make UPDATES,
> >> then this may work for you. If you do a lot of INSERTS > and DELETEs, then
> >it
> >> may not.
> >>
> >> A differential is good from the last time it was backed > up.
> >>
> >> A transaciton log allows you to restore to a point in > time. So you could
> >> recover up to 20 mins ago when something bad > happened. The Diff backup
> >> won't allow you to do this. You can apply the diff > backup from 1 hour ago
> >> and lose everything up to the last 20 mins. (Unless > you are doing log
> >> backups as well. Then simply backup the log and > restore it to 20 minutes
> >> ago.)
> >>
> >>
> >> Diff recoveries are faster than TLog recoveries in that > you only have to
> >> apply the last diff backup. With logs, you restore > your last good
> >database
> >> backup and then run all the logs in between.
> >>
> >> Sooo.. It depends on what you are after.
> >>
> >> HTH
> >>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA
> >>
> >>
> >>
> >>
> >>
> >> "G2SL" <anonymous@.discussions.microsoft.com> wrote in > message
> >> news:184301c48c72$89430e10$a601280a@.phx.gbl...
> >> > I'm confused. Let's say I want to be able to back up > to
> >> > one hour ago on several critical databases, i.e. I'm
> >> > willing to accept a maxiumum of 1 hour of data loss. > Is
> >> > it better to use Differential backups or Log Backups > or
> >> > does it matter? What are some of the basic pros/cons?
> >>
> >>
> >
> >
> >.
> >
--=_NextPart_000_0D00_01C48C67.9ED536F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I'm not sure what you mean by 'one hour =max'. How often are you making backups? If a backup spans more than one hour, but =you want to restore to a certain point in time, you need log backups.
There are many questions that must be =answered to come up with the best backup strategy for you; amount of acceptable work =loss is only one of the questions. Start by reading everything you can in Books =Online about backup and restore, and then take a look at "Microsoft SQL Server =2000 High Availability" http://www.amazon.com/exec/obidos/tg/detail/-/0735619204/104=-2349825-6773545
The chapter on Backup and Restore was =written by Kimberly Tripp and it is the best part of the book!
-- =HTH--Kalen DelaneySQL Server MVPwww.SolidQualityLearning.com
"G2SL" wrote in message news:02f101c48c7e$357192a0$a401280a@.phx.gbl...> So either =solution should work for me since I've got a one > hour max?> > >--Original Message--> >Hi Rick> >> =>I'm not sure what you're saying here. As far as a > differential =backup is> >concerned, updates are the same as inserts or deletes. => Any change to an> >extent is backed up in the differential, =whether that > changes comes from> >updating existing rows, or =from adding or removing rows.> >> >-- > =>HTH> >--> >Kalen Delaney> >SQL Server MVP> >www.SolidQualityLearning.com> >> =>> >"Rick Sawtell" wrote in message> >news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...> >> =Differentials only look at the changes in the data. If > you make UPDATES,> >> then this may work for you. If you do a =lot of INSERTS > and DELETEs, then> >it> >> may not.> >>> >> A differential is good from the =last time it was backed > up.> >>> >> A =transaciton log allows you to restore to a point in > time. So you =could> >> recover up to 20 mins ago when something bad > happened. The Diff backup> >> won't allow you =to do this. You can apply the diff > backup from 1 hour =ago> >> and lose everything up to the last 20 mins. (Unless => you are doing log> >> backups as well. Then simply backup =the log and > restore it to 20 minutes> >> ago.)> >>> >>> >> Diff recoveries are faster =than TLog recoveries in that > you only have to> >> apply the =last diff backup. With logs, you restore > your last =good> >database> >> backup and then run all the logs in between.> >>> >> Sooo.. It depends =on what you are after.> >>> >> HTH> =>>> >> Rick Sawtell> >> MCT, MCSD, MCDBA> =>>> >>> >>> >>> >>> =>> "G2SL" wrote in > message> >> news:184301c48c72$89430e10$a601280a@.phx.gbl...> >> > I'm confused. Let's say I want =to be able to back up > to> >> > one hour ago on several =critical databases, i.e. I'm> >> > willing to accept a maxiumum =of 1 hour of data loss. > Is> >> > it better to use Differential backups or Log Backups > or> >> > =does it matter? What are some of the basic pros/cons?> =>>> >>> >> >> >.> >
--=_NextPart_000_0D00_01C48C67.9ED536F0--|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
> > I'm confused. Let's say I want to be able to back up to
> > one hour ago on several critical databases, i.e. I'm
> > willing to accept a maxiumum of 1 hour of data loss. Is
> > it better to use Differential backups or Log Backups or
> > does it matter? What are some of the basic pros/cons?
>|||So either solution should work for me since I've got a one
hour max?
>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
>> Differentials only look at the changes in the data. If
you make UPDATES,
>> then this may work for you. If you do a lot of INSERTS
and DELETEs, then
>it
>> may not.
>> A differential is good from the last time it was backed
up.
>> A transaciton log allows you to restore to a point in
time. So you could
>> recover up to 20 mins ago when something bad
happened. The Diff backup
>> won't allow you to do this. You can apply the diff
backup from 1 hour ago
>> and lose everything up to the last 20 mins. (Unless
you are doing log
>> backups as well. Then simply backup the log and
restore it to 20 minutes
>> ago.)
>>
>> Diff recoveries are faster than TLog recoveries in that
you only have to
>> apply the last diff backup. With logs, you restore
your last good
>database
>> backup and then run all the logs in between.
>> Sooo.. It depends on what you are after.
>> HTH
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>> "G2SL" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>> > I'm confused. Let's say I want to be able to back up
to
>> > one hour ago on several critical databases, i.e. I'm
>> > willing to accept a maxiumum of 1 hour of data loss.
Is
>> > it better to use Differential backups or Log Backups
or
>> > does it matter? What are some of the basic pros/cons?
>>
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0D00_01C48C67.9ED536F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm not sure what you mean by 'one hour max'. How often are you making =backups? If a backup spans more than one hour, but you want to restore =to a certain point in time, you need log backups.
There are many questions that must be answered to come up with the best =backup strategy for you; amount of acceptable work loss is only one of =the questions. Start by reading everything you can in Books Online about =backup and restore, and then take a look at "Microsoft SQL Server 2000 =High Availability" =http://www.amazon.com/exec/obidos/tg/detail/-/0735619204/104-2349825-6773=
545
The chapter on Backup and Restore was written by Kimberly Tripp and it =is the best part of the book!
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message =news:02f101c48c7e$357192a0$a401280a@.phx.gbl...
> So either solution should work for me since I've got a one > hour max?
> > >--Original Message--
> >Hi Rick
> >
> >I'm not sure what you're saying here. As far as a > differential backup is
> >concerned, updates are the same as inserts or deletes. > Any change to an
> >extent is backed up in the differential, whether that > changes comes from
> >updating existing rows, or from adding or removing rows.
> >
> >-- > >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> >news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> >> Differentials only look at the changes in the data. If > you make UPDATES,
> >> then this may work for you. If you do a lot of INSERTS > and DELETEs, then
> >it
> >> may not.
> >>
> >> A differential is good from the last time it was backed > up.
> >>
> >> A transaciton log allows you to restore to a point in > time. So you could
> >> recover up to 20 mins ago when something bad > happened. The Diff backup
> >> won't allow you to do this. You can apply the diff > backup from 1 hour ago
> >> and lose everything up to the last 20 mins. (Unless > you are doing log
> >> backups as well. Then simply backup the log and > restore it to 20 minutes
> >> ago.)
> >>
> >>
> >> Diff recoveries are faster than TLog recoveries in that > you only have to
> >> apply the last diff backup. With logs, you restore > your last good
> >database
> >> backup and then run all the logs in between.
> >>
> >> Sooo.. It depends on what you are after.
> >>
> >> HTH
> >>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA
> >>
> >>
> >>
> >>
> >>
> >> "G2SL" <anonymous@.discussions.microsoft.com> wrote in > message
> >> news:184301c48c72$89430e10$a601280a@.phx.gbl...
> >> > I'm confused. Let's say I want to be able to back up > to
> >> > one hour ago on several critical databases, i.e. I'm
> >> > willing to accept a maxiumum of 1 hour of data loss. > Is
> >> > it better to use Differential backups or Log Backups > or
> >> > does it matter? What are some of the basic pros/cons?
> >>
> >>
> >
> >
> >.
> >
--=_NextPart_000_0D00_01C48C67.9ED536F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I'm not sure what you mean by 'one hour =max'. How often are you making backups? If a backup spans more than one hour, but =you want to restore to a certain point in time, you need log backups.
There are many questions that must be =answered to come up with the best backup strategy for you; amount of acceptable work =loss is only one of the questions. Start by reading everything you can in Books =Online about backup and restore, and then take a look at "Microsoft SQL Server =2000 High Availability" http://www.amazon.com/exec/obidos/tg/detail/-/0735619204/104=-2349825-6773545
The chapter on Backup and Restore was =written by Kimberly Tripp and it is the best part of the book!
-- =HTH--Kalen DelaneySQL Server MVPwww.SolidQualityLearning.com
"G2SL" wrote in message news:02f101c48c7e$357192a0$a401280a@.phx.gbl...> So either =solution should work for me since I've got a one > hour max?> > >--Original Message--> >Hi Rick> >> =>I'm not sure what you're saying here. As far as a > differential =backup is> >concerned, updates are the same as inserts or deletes. => Any change to an> >extent is backed up in the differential, =whether that > changes comes from> >updating existing rows, or =from adding or removing rows.> >> >-- > =>HTH> >--> >Kalen Delaney> >SQL Server MVP> >www.SolidQualityLearning.com> >> =>> >"Rick Sawtell"
--=_NextPart_000_0D00_01C48C67.9ED536F0--|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?
Diff between Logshipping , Transaction Log backup & Transaction Replication
Can any one explain in detail difference betwen Logshipping , Transaction Log backup & Transaction Replication and which is
better over other on what circumstances?By all means you can get more explanation with code examples from books online for these topics.sql
better over other on what circumstances?By all means you can get more explanation with code examples from books online for these topics.sql
Monday, March 19, 2012
Did not clear up transaction log after backup?
I performed a complete database backup using the backup wizard and checked
"Remove inactive entries from transaction log". After the backup was
completed, I discovered that the size of the transaction log did not go down.
How can I clear the transaction log?
Hi
BACKUP LOG file and the DBCC SHRINKFILE command ( for more details please
refer to BOL)
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:B0DF5822-6FCE-411B-8603-BD0F64E13DAD@.microsoft.com...
> I performed a complete database backup using the backup wizard and checked
> "Remove inactive entries from transaction log". After the backup was
> completed, I discovered that the size of the transaction log did not go
down.
> How can I clear the transaction log?
"Remove inactive entries from transaction log". After the backup was
completed, I discovered that the size of the transaction log did not go down.
How can I clear the transaction log?
Hi
BACKUP LOG file and the DBCC SHRINKFILE command ( for more details please
refer to BOL)
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:B0DF5822-6FCE-411B-8603-BD0F64E13DAD@.microsoft.com...
> I performed a complete database backup using the backup wizard and checked
> "Remove inactive entries from transaction log". After the backup was
> completed, I discovered that the size of the transaction log did not go
down.
> How can I clear the transaction log?
Did not clear up transaction log after backup?
I performed a complete database backup using the backup wizard and checked
"Remove inactive entries from transaction log". After the backup was
completed, I discovered that the size of the transaction log did not go down
.
How can I clear the transaction log?Hi
BACKUP LOG file and the DBCC SHRINKFILE command ( for more details please
refer to BOL)
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:B0DF5822-6FCE-411B-8603-BD0F64E13DAD@.microsoft.com...
> I performed a complete database backup using the backup wizard and checked
> "Remove inactive entries from transaction log". After the backup was
> completed, I discovered that the size of the transaction log did not go
down.
> How can I clear the transaction log?
"Remove inactive entries from transaction log". After the backup was
completed, I discovered that the size of the transaction log did not go down
.
How can I clear the transaction log?Hi
BACKUP LOG file and the DBCC SHRINKFILE command ( for more details please
refer to BOL)
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:B0DF5822-6FCE-411B-8603-BD0F64E13DAD@.microsoft.com...
> I performed a complete database backup using the backup wizard and checked
> "Remove inactive entries from transaction log". After the backup was
> completed, I discovered that the size of the transaction log did not go
down.
> How can I clear the transaction log?
Did not clear up transaction log after backup?
I performed a complete database backup using the backup wizard and checked
"Remove inactive entries from transaction log". After the backup was
completed, I discovered that the size of the transaction log did not go down.
How can I clear the transaction log?Hi
BACKUP LOG file and the DBCC SHRINKFILE command ( for more details please
refer to BOL)
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:B0DF5822-6FCE-411B-8603-BD0F64E13DAD@.microsoft.com...
> I performed a complete database backup using the backup wizard and checked
> "Remove inactive entries from transaction log". After the backup was
> completed, I discovered that the size of the transaction log did not go
down.
> How can I clear the transaction log?
"Remove inactive entries from transaction log". After the backup was
completed, I discovered that the size of the transaction log did not go down.
How can I clear the transaction log?Hi
BACKUP LOG file and the DBCC SHRINKFILE command ( for more details please
refer to BOL)
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:B0DF5822-6FCE-411B-8603-BD0F64E13DAD@.microsoft.com...
> I performed a complete database backup using the backup wizard and checked
> "Remove inactive entries from transaction log". After the backup was
> completed, I discovered that the size of the transaction log did not go
down.
> How can I clear the transaction log?
Friday, March 9, 2012
DHCP Logging
Is anyone familiar with a way to have a Windows 2003 DHCP server log to an
SQL server?
Can you elaborate on your requirements? Of course if the server is on the
network, with proper authentication this is possible.
Anith
SQL server?
Can you elaborate on your requirements? Of course if the server is on the
network, with proper authentication this is possible.
Anith
DHCP Logging
Is anyone familiar with a way to have a Windows 2003 DHCP server log to an
SQL server?Can you elaborate on your requirements? Of course if the server is on the
network, with proper authentication this is possible.
--
Anith
SQL server?Can you elaborate on your requirements? Of course if the server is on the
network, with proper authentication this is possible.
--
Anith
Wednesday, March 7, 2012
Device activation error
Hi All,
Currently I have a PC that has MSDE installed on it and is attached to database (MyData.MDF and a log file MYLog.LDF) located on its hard drive at c:\data\.When I detach from the database, place a copy of the two files noted above on my networkdrive @. u:\data and try to attach I get the following error’s:
SQL[1] exec error = -1: Changed database context to 'master'.
“Device activation error. The physical file name u:\data\MyData.MDF may be incorrect.”
I have done some testing a have found that I can attach to a copy of my database if I move it anywhere on the c: drive, and or even to a 1Gb USB key attached to the system(e:\).So far it seems to only be an issue if I move it to a mapped network drive.If anyone could please provide me with any info it would be greatly appreciated.
Thanks.
James D.
First, your data must be stored on an approved network server/storage you want to be supported. Second, you will have to use a special trace flag to force sqlserver to mount a networked db file. See the following article:
http://support.microsoft.com/kb/304261|||
It's because your U: drive isn't a local drive. By default, it's not support to place data files on mapped shares or UNC paths. You can find more information in the following article:
Description of support for network database files in SQL Server
http://support.microsoft.com/?id=304261
-Sue
|||Excellent, thank you for the response.
After I use the trace flag mentioned and get my MS SQL Server Desktop Engine to mount to the networked db files, will it be possible to have a second PC with MS SQL Server Desktop Engine also installed on it to connect to the same networked DB files at the same time as the first unit, or will it be limited to just one PC at a time due to file locks? We require that at least two units(possibly up to 3 more in the future) can all share, write, and read to same networked db files at the same time. If you could please let me know when you have a moment it would be great.
Thanks.
James D.
|||Sqlserver is a shared nothing technology. I.e. only one instance can access the data at any time. So, no, you cannot have another server hits that network database file(s). As it stands, unless your hardware is part of the qualified HCL, you're running in an unsupported platform.
Btw, sqlserver has a feature called Scalable Shared Database (SSD) which allows multiple instances to share the same database file. However, this requires a SAN.
Friday, February 24, 2012
Developer terminates MSSQLSERVER service
Hi.
One of the developers tried to compile a stored procedure
against the master database by mistake. The result was
that the .
In the event log the following entry appears:
The system has called a custom component and that
component has failed and generated an exception.
This indicates a problem with the custom component.
Notify the developer of this component that a failure has
occurred and provide them with the information below.
Component Prog ID: SC. Pool 61 1
Method Name: IDispenseDriver::CreateResource
Process Name: sqlserver.exe
The serious nature of this error has caused the process
to terminate.
Exception: C0000005
Address: 0x17CEA9E1
Call Stack: 0x17CEA9E2
The MSSQLSERVER service terminated unexpectedly.
Just prior to that an entry appears with the developers
user id:
Error: 0, Severity:19 State:0
SqlDumpExceptionHandler: Process 167 generated fatal
exception = C0000005
EXCEPTION_ACCESS_VIOLATION SQL Server is terminating this
process.
How can I prevent this from ever happening again?
How can I lock down my server properly enough to prevent
this from ever happening again?
Thanks,
MTAV's are generally bugs in SQL Server, and you cannot make sure that SQL Server is bug free. When
you hit an AV, search KB, make sure the db is OK (CHECKDB) and if needed open a case with MS.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
> Hi.
> One of the developers tried to compile a stored procedure
> against the master database by mistake. The result was
> that the .
> In the event log the following entry appears:
> The system has called a custom component and that
> component has failed and generated an exception.
> This indicates a problem with the custom component.
> Notify the developer of this component that a failure has
> occurred and provide them with the information below.
> Component Prog ID: SC. Pool 61 1
> Method Name: IDispenseDriver::CreateResource
> Process Name: sqlserver.exe
> The serious nature of this error has caused the process
> to terminate.
> Exception: C0000005
> Address: 0x17CEA9E1
> Call Stack: 0x17CEA9E2
> The MSSQLSERVER service terminated unexpectedly.
> Just prior to that an entry appears with the developers
> user id:
> Error: 0, Severity:19 State:0
> SqlDumpExceptionHandler: Process 167 generated fatal
> exception = C0000005
> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating this
> process.
> How can I prevent this from ever happening again?
> How can I lock down my server properly enough to prevent
> this from ever happening again?
> Thanks,
> MT
>|||Thanks Tibor,
Good news, I ran the CHECKDB and found 0 allocation
errors and 0 consistency errors in database 'master'.
What does AV stand for? Under what criteria would I
search the KB to determine how to prevent this situation
from happening again
Thanks,
MT
>--Original Message--
>AV's are generally bugs in SQL Server, and you cannot
make sure that SQL Server is bug free. When
>you hit an AV, search KB, make sure the db is OK
(CHECKDB) and if needed open a case with MS.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"MT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
>> Hi.
>> One of the developers tried to compile a stored
procedure
>> against the master database by mistake. The result was
>> that the .
>> In the event log the following entry appears:
>> The system has called a custom component and that
>> component has failed and generated an exception.
>> This indicates a problem with the custom component.
>> Notify the developer of this component that a failure
has
>> occurred and provide them with the information below.
>> Component Prog ID: SC. Pool 61 1
>> Method Name: IDispenseDriver::CreateResource
>> Process Name: sqlserver.exe
>> The serious nature of this error has caused the process
>> to terminate.
>> Exception: C0000005
>> Address: 0x17CEA9E1
>> Call Stack: 0x17CEA9E2
>> The MSSQLSERVER service terminated unexpectedly.
>> Just prior to that an entry appears with the developers
>> user id:
>> Error: 0, Severity:19 State:0
>> SqlDumpExceptionHandler: Process 167 generated fatal
>> exception = C0000005
>> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating
this
>> process.
>> How can I prevent this from ever happening again?
>> How can I lock down my server properly enough to
prevent
>> this from ever happening again?
>> Thanks,
>> MT
>>
>
>.
>|||AV is short for Access Violation (see the error messages that SQL Server returned). You would have
to try to find a relevant KB article by going in a good search string, error number etc. However,
there might not be a KB article for your problem (the bug in SQL Server might not be knows, yet),
hence the need to possibly open a case with MS support. If you find a KB article, it hopefully has
some workaround, point to some service pack that fixes this or possibly a hotfix that fixes this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:06b301c39ebc$a80b02c0$a601280a@.phx.gbl...
> Thanks Tibor,
> Good news, I ran the CHECKDB and found 0 allocation
> errors and 0 consistency errors in database 'master'.
> What does AV stand for? Under what criteria would I
> search the KB to determine how to prevent this situation
> from happening again
> Thanks,
> MT
> >--Original Message--
> >AV's are generally bugs in SQL Server, and you cannot
> make sure that SQL Server is bug free. When
> >you hit an AV, search KB, make sure the db is OK
> (CHECKDB) and if needed open a case with MS.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"MT" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
> >> Hi.
> >>
> >> One of the developers tried to compile a stored
> procedure
> >> against the master database by mistake. The result was
> >> that the .
> >>
> >> In the event log the following entry appears:
> >>
> >> The system has called a custom component and that
> >> component has failed and generated an exception.
> >> This indicates a problem with the custom component.
> >> Notify the developer of this component that a failure
> has
> >> occurred and provide them with the information below.
> >>
> >> Component Prog ID: SC. Pool 61 1
> >> Method Name: IDispenseDriver::CreateResource
> >> Process Name: sqlserver.exe
> >>
> >> The serious nature of this error has caused the process
> >> to terminate.
> >>
> >> Exception: C0000005
> >> Address: 0x17CEA9E1
> >> Call Stack: 0x17CEA9E2
> >>
> >> The MSSQLSERVER service terminated unexpectedly.
> >>
> >> Just prior to that an entry appears with the developers
> >> user id:
> >>
> >> Error: 0, Severity:19 State:0
> >> SqlDumpExceptionHandler: Process 167 generated fatal
> >> exception = C0000005
> >>
> >> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating
> this
> >> process.
> >>
> >> How can I prevent this from ever happening again?
> >> How can I lock down my server properly enough to
> prevent
> >> this from ever happening again?
> >>
> >> Thanks,
> >> MT
> >>
> >>
> >
> >
> >.
> >
One of the developers tried to compile a stored procedure
against the master database by mistake. The result was
that the .
In the event log the following entry appears:
The system has called a custom component and that
component has failed and generated an exception.
This indicates a problem with the custom component.
Notify the developer of this component that a failure has
occurred and provide them with the information below.
Component Prog ID: SC. Pool 61 1
Method Name: IDispenseDriver::CreateResource
Process Name: sqlserver.exe
The serious nature of this error has caused the process
to terminate.
Exception: C0000005
Address: 0x17CEA9E1
Call Stack: 0x17CEA9E2
The MSSQLSERVER service terminated unexpectedly.
Just prior to that an entry appears with the developers
user id:
Error: 0, Severity:19 State:0
SqlDumpExceptionHandler: Process 167 generated fatal
exception = C0000005
EXCEPTION_ACCESS_VIOLATION SQL Server is terminating this
process.
How can I prevent this from ever happening again?
How can I lock down my server properly enough to prevent
this from ever happening again?
Thanks,
MTAV's are generally bugs in SQL Server, and you cannot make sure that SQL Server is bug free. When
you hit an AV, search KB, make sure the db is OK (CHECKDB) and if needed open a case with MS.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
> Hi.
> One of the developers tried to compile a stored procedure
> against the master database by mistake. The result was
> that the .
> In the event log the following entry appears:
> The system has called a custom component and that
> component has failed and generated an exception.
> This indicates a problem with the custom component.
> Notify the developer of this component that a failure has
> occurred and provide them with the information below.
> Component Prog ID: SC. Pool 61 1
> Method Name: IDispenseDriver::CreateResource
> Process Name: sqlserver.exe
> The serious nature of this error has caused the process
> to terminate.
> Exception: C0000005
> Address: 0x17CEA9E1
> Call Stack: 0x17CEA9E2
> The MSSQLSERVER service terminated unexpectedly.
> Just prior to that an entry appears with the developers
> user id:
> Error: 0, Severity:19 State:0
> SqlDumpExceptionHandler: Process 167 generated fatal
> exception = C0000005
> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating this
> process.
> How can I prevent this from ever happening again?
> How can I lock down my server properly enough to prevent
> this from ever happening again?
> Thanks,
> MT
>|||Thanks Tibor,
Good news, I ran the CHECKDB and found 0 allocation
errors and 0 consistency errors in database 'master'.
What does AV stand for? Under what criteria would I
search the KB to determine how to prevent this situation
from happening again
Thanks,
MT
>--Original Message--
>AV's are generally bugs in SQL Server, and you cannot
make sure that SQL Server is bug free. When
>you hit an AV, search KB, make sure the db is OK
(CHECKDB) and if needed open a case with MS.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"MT" <anonymous@.discussions.microsoft.com> wrote in
message
>news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
>> Hi.
>> One of the developers tried to compile a stored
procedure
>> against the master database by mistake. The result was
>> that the .
>> In the event log the following entry appears:
>> The system has called a custom component and that
>> component has failed and generated an exception.
>> This indicates a problem with the custom component.
>> Notify the developer of this component that a failure
has
>> occurred and provide them with the information below.
>> Component Prog ID: SC. Pool 61 1
>> Method Name: IDispenseDriver::CreateResource
>> Process Name: sqlserver.exe
>> The serious nature of this error has caused the process
>> to terminate.
>> Exception: C0000005
>> Address: 0x17CEA9E1
>> Call Stack: 0x17CEA9E2
>> The MSSQLSERVER service terminated unexpectedly.
>> Just prior to that an entry appears with the developers
>> user id:
>> Error: 0, Severity:19 State:0
>> SqlDumpExceptionHandler: Process 167 generated fatal
>> exception = C0000005
>> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating
this
>> process.
>> How can I prevent this from ever happening again?
>> How can I lock down my server properly enough to
prevent
>> this from ever happening again?
>> Thanks,
>> MT
>>
>
>.
>|||AV is short for Access Violation (see the error messages that SQL Server returned). You would have
to try to find a relevant KB article by going in a good search string, error number etc. However,
there might not be a KB article for your problem (the bug in SQL Server might not be knows, yet),
hence the need to possibly open a case with MS support. If you find a KB article, it hopefully has
some workaround, point to some service pack that fixes this or possibly a hotfix that fixes this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:06b301c39ebc$a80b02c0$a601280a@.phx.gbl...
> Thanks Tibor,
> Good news, I ran the CHECKDB and found 0 allocation
> errors and 0 consistency errors in database 'master'.
> What does AV stand for? Under what criteria would I
> search the KB to determine how to prevent this situation
> from happening again
> Thanks,
> MT
> >--Original Message--
> >AV's are generally bugs in SQL Server, and you cannot
> make sure that SQL Server is bug free. When
> >you hit an AV, search KB, make sure the db is OK
> (CHECKDB) and if needed open a case with MS.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"MT" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:068d01c39eb8$9e372110$a601280a@.phx.gbl...
> >> Hi.
> >>
> >> One of the developers tried to compile a stored
> procedure
> >> against the master database by mistake. The result was
> >> that the .
> >>
> >> In the event log the following entry appears:
> >>
> >> The system has called a custom component and that
> >> component has failed and generated an exception.
> >> This indicates a problem with the custom component.
> >> Notify the developer of this component that a failure
> has
> >> occurred and provide them with the information below.
> >>
> >> Component Prog ID: SC. Pool 61 1
> >> Method Name: IDispenseDriver::CreateResource
> >> Process Name: sqlserver.exe
> >>
> >> The serious nature of this error has caused the process
> >> to terminate.
> >>
> >> Exception: C0000005
> >> Address: 0x17CEA9E1
> >> Call Stack: 0x17CEA9E2
> >>
> >> The MSSQLSERVER service terminated unexpectedly.
> >>
> >> Just prior to that an entry appears with the developers
> >> user id:
> >>
> >> Error: 0, Severity:19 State:0
> >> SqlDumpExceptionHandler: Process 167 generated fatal
> >> exception = C0000005
> >>
> >> EXCEPTION_ACCESS_VIOLATION SQL Server is terminating
> this
> >> process.
> >>
> >> How can I prevent this from ever happening again?
> >> How can I lock down my server properly enough to
> prevent
> >> this from ever happening again?
> >>
> >> Thanks,
> >> MT
> >>
> >>
> >
> >
> >.
> >
Subscribe to:
Posts (Atom)