Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.com
Have you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> nice
>
sql

Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.comHave you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> nice
>

Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.comHave you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
>> Our environment is development and data warehousing with many non logged
>> trasactions. Our recover model is simple and our backups are always
>> full
>> backups.
>> My question is:
>> Is there anything in sql that is comparable to the archive flag of a
>> file?
>> We do have some databases that might not change in a week. It would be
> nice
>> not to have to back them up
>> Robert Alexander
>> Robert.Alexander@.cca-audit.com
>>
>

backup sql server database in hosting environment?

I am new to database.

I have a website in a hosting environment and it uses sql server 2005 databases. I want to have a complete backup of my database in addition to the hosting company's routine backup. I want to write a program to automatically backup everything every night. And of couse have the ability to restored the whole database if necessary.

How should I do it? Are there any tools exist that I can use? What is the best way to deal with backup/restore in hosting environment?

Thanks!

Why not just schedule the backup task to be execueted every night? In Enterprise Manager, right click a database->choose Backup->check the Schedule checkbox in the General tab->Customize the schedule as you like.sql

backup question

Dear all,
i want to verify whether the following backup planning can restore to the
original environment or not
1.) backup master, msdb, model with full backup to bak
2.) backup all user database with full backup and tran log backup to bak
question
how about the DTS package, user login, replication, job schelding, is it can
restore when i backup to the master and model?Users, packages and jobs are all in the databases you are
backup up (DTS Packages - as long as they are saved to SQL
Server as the location) so backing up all of your databases
and restoring all databases, including system databases will
get things back.
Replication restore strategies depend on how you have
implemented replication. You'd want to backup the publisher,
distributor, subscriber. There are different strategies for
merge, transactional, snapshot replication. You can find
them in books online. Look up the index topic:
replication, backup and restore operations
-Sue
On Mon, 3 Oct 2005 04:17:13 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:

>Dear all,
>i want to verify whether the following backup planning can restore to the
>original environment or not
>1.) backup master, msdb, model with full backup to bak
>2.) backup all user database with full backup and tran log backup to bak
>question
>how about the DTS package, user login, replication, job schelding, is it ca
n
>restore when i backup to the master and model?

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.

I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
log file:
Message
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
recovery model should be used to automatically truncate the transaction log.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?If the full only takes "a second" to run, just run it hourly in Simple
Recovery if you want near-time recovery and no worries about the t-log. Set
your retention appropriately
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>I have choosen the Full model over the simple model in a development
> environment because if the database was to go suspect using the simple
> model,
> I would have to choose how much work I was willing to lo lose. For example
> if
> the database was backed up at 10:00am and the next backup was due at
> 4:00pm
> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
> While using the Full model I should be able to recover up to 3:00pm. Now
> because this is a very small database, only takes a second to do a Full
> backup, it really doesn't pay to keep backups of the log, but in order to
> keep the log file from growing and growing, I clean it out so that it will
> shrink by backing up with troncate_only. I get the following message in
> the
> log file:
> Message
> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
> recovery model should be used to automatically truncate the transaction
> log.
> Now I have to say in the situtation that I have described, isn't using a
> Full model better than a simple?
>|||What you describe is not a very common scenario (i.e. running in full but not do log backups, and
reason for running in full is so you can backup log when db goes suspect). However, you can
accomplish the same thing as BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple recovery
model and then back to full recovery model again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>I have choosen the Full model over the simple model in a development
> environment because if the database was to go suspect using the simple model,
> I would have to choose how much work I was willing to lo lose. For example if
> the database was backed up at 10:00am and the next backup was due at 4:00pm
> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
> While using the Full model I should be able to recover up to 3:00pm. Now
> because this is a very small database, only takes a second to do a Full
> backup, it really doesn't pay to keep backups of the log, but in order to
> keep the log file from growing and growing, I clean it out so that it will
> shrink by backing up with troncate_only. I get the following message in the
> log file:
> Message
> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
> recovery model should be used to automatically truncate the transaction log.
> Now I have to say in the situtation that I have described, isn't using a
> Full model better than a simple?
>|||> What you describe is not a very common scenario (i.e. running in full but
> not do log backups
Actually I have to take exception to that one Tibor. This forum and others
have numerous examples of users who complain "my data file is NNN MB and my
tlog is MM GB, what is going on!?!?". Default settings lead many users who
aren't DBAs (99.438% of them) to have FULL recovery mode doing FULL (or
often even no) backups without doing tlog backups. I guess you probably
consult at larger companies that have more significant problems than things
like this. :-))
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
> What you describe is not a very common scenario (i.e. running in full but
> not do log backups, and reason for running in full is so you can backup
> log when db goes suspect). However, you can accomplish the same thing as
> BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple recovery
> model and then back to full recovery model again.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>>I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple
>> model,
>> I would have to choose how much work I was willing to lo lose. For
>> example if
>> the database was backed up at 10:00am and the next backup was due at
>> 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it
>> will
>> shrink by backing up with troncate_only. I get the following message in
>> the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction
>> log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?
>|||> Actually I have to take exception to that one Tibor. This forum and others have numerous examples
> of users who complain "my data file is NNN MB and my tlog is MM GB, what is going on!?!?".
Oh, I now see that I should have qualified my statement. It isn't a very common scenario *when the
dba understand recovery models and transaction logging*. I.e., to do this deliberately.
I definitely agree that it is unfortunately common to have this setup undeliberately. I've always
thought that full recovery model as default is a good thing. But lately, I've been having some
doubts. Perhaps SQL Server should default recovery model for the model database to simple. Would
reduce a lot of the cases we see in this newsgroup, for instance.
> I guess you probably consult at larger companies that have more significant problems than things
> like this. :-))
Nah, I do both. And I see this in the most surprising situations... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13ofcn7dvasj99c@.corp.supernews.com...
>> What you describe is not a very common scenario (i.e. running in full but not do log backups
> Actually I have to take exception to that one Tibor. This forum and others have numerous examples
> of users who complain "my data file is NNN MB and my tlog is MM GB, what is going on!?!?".
> Default settings lead many users who aren't DBAs (99.438% of them) to have FULL recovery mode
> doing FULL (or often even no) backups without doing tlog backups. I guess you probably consult at
> larger companies that have more significant problems than things like this. :-))
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
>> What you describe is not a very common scenario (i.e. running in full but not do log backups, and
>> reason for running in full is so you can backup log when db goes suspect). However, you can
>> accomplish the same thing as BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple
>> recovery model and then back to full recovery model again.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
>> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>>I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple model,
>> I would have to choose how much work I was willing to lo lose. For example if
>> the database was backed up at 10:00am and the next backup was due at 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it will
>> shrink by backing up with troncate_only. I get the following message in the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?
>>
>|||One reason you see the message that you are is that it is being removed in
SQL2008. See http://www.mssqltips.com/tip.asp?tip=1352 and
http://www.mssqltips.com/tip.asp?tip=1370
Chris
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>I have choosen the Full model over the simple model in a development
> environment because if the database was to go suspect using the simple
> model,
> I would have to choose how much work I was willing to lo lose. For example
> if
> the database was backed up at 10:00am and the next backup was due at
> 4:00pm
> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
> While using the Full model I should be able to recover up to 3:00pm. Now
> because this is a very small database, only takes a second to do a Full
> backup, it really doesn't pay to keep backups of the log, but in order to
> keep the log file from growing and growing, I clean it out so that it will
> shrink by backing up with troncate_only. I get the following message in
> the
> log file:
> Message
> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
> recovery model should be used to automatically truncate the transaction
> log.
> Now I have to say in the situtation that I have described, isn't using a
> Full model better than a simple?
>|||I think from the replies, my point has been lost, which is that doing a full
back up followed immediately by a log backup using truncate only, is better
than using a simple model because it protects against data loss between
backup time periods. If this feature is going to be removed in 2008 than a
dba with a situtation like mine will have to either choose between allowing a
certain amount of data loss (simple) or using (full) and creating tran log
backups that he really doesn't want and has to clean up.
"Doctor Who" wrote:
> I have choosen the Full model over the simple model in a development
> environment because if the database was to go suspect using the simple model,
> I would have to choose how much work I was willing to lo lose. For example if
> the database was backed up at 10:00am and the next backup was due at 4:00pm
> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
> While using the Full model I should be able to recover up to 3:00pm. Now
> because this is a very small database, only takes a second to do a Full
> backup, it really doesn't pay to keep backups of the log, but in order to
> keep the log file from growing and growing, I clean it out so that it will
> shrink by backing up with troncate_only. I get the following message in the
> log file:
> Message
> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
> recovery model should be used to automatically truncate the transaction log.
> Now I have to say in the situtation that I have described, isn't using a
> Full model better than a simple?
>|||"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>I think from the replies, my point has been lost, which is that doing a
>full
> back up followed immediately by a log backup using truncate only, is
> better
> than using a simple model because it protects against data loss between
> backup time periods.
How do you figure? If you truncate the log you can't later recover it.
Do you mean the other way around?
> If this feature is going to be removed in 2008 than a
> dba with a situtation like mine will have to either choose between
> allowing a
> certain amount of data loss (simple) or using (full) and creating tran log
> backups that he really doesn't want and has to clean up.
> "Doctor Who" wrote:
>> I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple
>> model,
>> I would have to choose how much work I was willing to lo lose. For
>> example if
>> the database was backed up at 10:00am and the next backup was due at
>> 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it
>> will
>> shrink by backing up with troncate_only. I get the following message in
>> the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction
>> log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>I think from the replies, my point has been lost, which is that doing a full
> back up followed immediately by a log backup using truncate only
That doesn't give you anything compared to simple recovery model. What you later have in the log
isn't usable for recovery purposes since you truncated the log directly after your database backup.
It would be better to have simple recovery model.
If you do it the other way around (BACKUP LOG WITH TRUNCATE ONLY immediately before the database
backup), then you have a possible advantage compared to simple recovery model. If the database
becomes suspect, you can do a log backup. But this is a pretty extreme case, and I would suggest
that you do regular log backups instead.
> If this feature is going to be removed in 2008 than a
> dba with a situtation like mine will have to either choose between allowing a
> certain amount of data loss (simple) or using (full) and creating tran log
> backups that he really doesn't want and has to clean up.
As I replied earlier, it is only the command which will be removed. The *functionality* is still
there. Put the db in simple recovery then immediately to full again. This gives you the same effect
as BACKUP LOG WITH TRUNCATE ONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>I think from the replies, my point has been lost, which is that doing a full
> back up followed immediately by a log backup using truncate only, is better
> than using a simple model because it protects against data loss between
> backup time periods. If this feature is going to be removed in 2008 than a
> dba with a situtation like mine will have to either choose between allowing a
> certain amount of data loss (simple) or using (full) and creating tran log
> backups that he really doesn't want and has to clean up.
> "Doctor Who" wrote:
>> I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple model,
>> I would have to choose how much work I was willing to lo lose. For example if
>> the database was backed up at 10:00am and the next backup was due at 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it will
>> shrink by backing up with troncate_only. I get the following message in the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?|||You can always make the log backup be deleted after one hour, the shortest
time currently allowed.
Chris
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>I think from the replies, my point has been lost, which is that doing a
>full
> back up followed immediately by a log backup using truncate only, is
> better
> than using a simple model because it protects against data loss between
> backup time periods. If this feature is going to be removed in 2008 than
> a
> dba with a situtation like mine will have to either choose between
> allowing a
> certain amount of data loss (simple) or using (full) and creating tran log
> backups that he really doesn't want and has to clean up.
> "Doctor Who" wrote:
>> I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple
>> model,
>> I would have to choose how much work I was willing to lo lose. For
>> example if
>> the database was backed up at 10:00am and the next backup was due at
>> 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it
>> will
>> shrink by backing up with troncate_only. I get the following message in
>> the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction
>> log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?

backup issue

To all,
As always, I appreciate your help.
In an active/active cluster environment, I have a 68GB
database. Creates a 63GB .BAK file to a separate drive at
night that is on one of the physical nodes of the cluster,
but the physical drive is not assigned to the cluster.
The .BAK is only thing on the drive.
After performing chkdsk maintenance, I got deleting
indexing entry...MFT file. (I think this may be related to
Microsoft's KB327009)
So I've rebooted a couple times performing chkdsk /f and
the disk comes back clean. I backup the db again manually,
doing a restore verify of course, says it's good. At night
when the maintenance job runs, in the morning it says the
file is not a valid backup set. I run chkdsk and get
errors. At night we have a 3rd party backup program that
kicks off and around the same time I have this error in my
system logs: Event ID 12503 - The Distributed Link
Tracking log was corrupt on volume Z: and has been re-
created. This log is used to automatically repair file
links, such as Shell Shortcuts and OLE links, when for
some reason those links become broken.
I have deleted and re-created the job, and this past week.
I've been running the job manually in the evening, the job
will run once successfully at night, but if I allow it to
backup through the jobs the following night. I get that
it's not a valid backup set again. Confusing huh?
Has anyone experienced such a thing? And if so, please
share how you fixed it? If not, any suggestions are
greatly appreciated, because I'm fresh out of ideas.
Thanks,
JosieJosie,
Hello!
Looking at your problem, it appears to me that you could be looking at some
disk corruption. The best place to start would be the Event logs and see
if:-
1. You see any corruption or hardware errors
2. Did the backups actually complete successfully?
3. Is the database corrupt? (Run checkdb)
With SQL Server backups, we usually have commands such as RESTORE
VERIFYONLY and RESTORE HEADERONLY to verify the state of a backup device. A
backup can be deemed as successful only after it's been fully restored, and
a checkdb is run on it.
I'd suggest you look at the event logs, and maybe get your hardware vendors
engaged as well. Have you tried backing up the db to another drive?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

backup issue

To all,
As always, I appreciate your help.
In an active/active cluster environment, I have a 68GB
database. Creates a 63GB .BAK file to a separate drive at
night that is on one of the physical nodes of the cluster,
but the physical drive is not assigned to the cluster.
The .BAK is only thing on the drive.
After performing chkdsk maintenance, I got deleting
indexing entry...MFT file. (I think this may be related to
Microsoft's KB327009)
So I've rebooted a couple times performing chkdsk /f and
the disk comes back clean. I backup the db again manually,
doing a restore verify of course, says it's good. At night
when the maintenance job runs, in the morning it says the
file is not a valid backup set. I run chkdsk and get
errors. At night we have a 3rd party backup program that
kicks off and around the same time I have this error in my
system logs: Event ID 12503 - The Distributed Link
Tracking log was corrupt on volume Z: and has been re-
created. This log is used to automatically repair file
links, such as Shell Shortcuts and OLE links, when for
some reason those links become broken.
I have deleted and re-created the job, and this past week.
I've been running the job manually in the evening, the job
will run once successfully at night, but if I allow it to
backup through the jobs the following night. I get that
it's not a valid backup set again. Confusing huh?
Has anyone experienced such a thing? And if so, please
share how you fixed it? If not, any suggestions are
greatly appreciated, because I'm fresh out of ideas.
Thanks,
Josie
Josie,
Hello!
Looking at your problem, it appears to me that you could be looking at some
disk corruption. The best place to start would be the Event logs and see
if:-
1. You see any corruption or hardware errors
2. Did the backups actually complete successfully?
3. Is the database corrupt? (Run checkdb)
With SQL Server backups, we usually have commands such as RESTORE
VERIFYONLY and RESTORE HEADERONLY to verify the state of a backup device. A
backup can be deemed as successful only after it's been fully restored, and
a checkdb is run on it.
I'd suggest you look at the event logs, and maybe get your hardware vendors
engaged as well. Have you tried backing up the db to another drive?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

backup issue

To all,
As always, I appreciate your help.
In an active/active cluster environment, I have a 68GB
database. Creates a 63GB .BAK file to a separate drive at
night that is on one of the physical nodes of the cluster,
but the physical drive is not assigned to the cluster.
The .BAK is only thing on the drive.
After performing chkdsk maintenance, I got deleting
indexing entry...MFT file. (I think this may be related to
Microsoft's KB327009)
So I've rebooted a couple times performing chkdsk /f and
the disk comes back clean. I backup the db again manually,
doing a restore verify of course, says it's good. At night
when the maintenance job runs, in the morning it says the
file is not a valid backup set. I run chkdsk and get
errors. At night we have a 3rd party backup program that
kicks off and around the same time I have this error in my
system logs: Event ID 12503 - The Distributed Link
Tracking log was corrupt on volume Z: and has been re-
created. This log is used to automatically repair file
links, such as Shell Shortcuts and OLE links, when for
some reason those links become broken.
I have deleted and re-created the job, and this past week.
I've been running the job manually in the evening, the job
will run once successfully at night, but if I allow it to
backup through the jobs the following night. I get that
it's not a valid backup set again. Confusing huh?
Has anyone experienced such a thing? And if so, please
share how you fixed it? If not, any suggestions are
greatly appreciated, because I'm fresh out of ideas.
Thanks,
JosieJosie,
Hello!
Looking at your problem, it appears to me that you could be looking at some
disk corruption. The best place to start would be the Event logs and see
if:-
1. You see any corruption or hardware errors
2. Did the backups actually complete successfully?
3. Is the database corrupt? (Run checkdb)
With SQL Server backups, we usually have commands such as RESTORE
VERIFYONLY and RESTORE HEADERONLY to verify the state of a backup device. A
backup can be deemed as successful only after it's been fully restored, and
a checkdb is run on it.
I'd suggest you look at the event logs, and maybe get your hardware vendors
engaged as well. Have you tried backing up the db to another drive?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.