Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.
Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>
|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/inf..._truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restor
e
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model i
s
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db a
nd
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say a
t
> noon. this will save time restoring the db because you do not have to rest
ore
> all transaction log backups from the last full one, instead you restore th
e
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full
,
> or from diff to diff if there is not a full between them, or between full
to
> full if not diff), as many as you need to avoid it from growing and be sav
e
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
>|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNC
ATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox shou
ld be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the
exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done
of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and als
o
http://www.karaszi.com/SQLServer/in...o_truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If y
ou don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every t
en minutes).
Or if you don't want to do log backups, put the db in simple recovery mode a
nd only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gb
l...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>sql

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
> Tom,
> > How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
> > I have some confusion about backing up databases and transaction logs.
> > When I do a complete database backup it backs up the db and the log, but
> > when I have it set to clear out inactive entries in the log it does not.
> > When I do a log backup and set it to clear out inactive entries it does do
> > that. My question is then if I do complete database backups all the time and
> > the log is not cleared for a long time and I do a restore and then play back
> > the log from a long time ago will I corrupt the database? How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> >
> >
> >|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>

Database Backup has stopped working. Cannot tell why

Hi,

I had a Database maintenance plan setup to do complete backup of my SQL Server 2000 database. Same thing was done for the transaction logs as well. And they had resulted in successful backups sometime ago.

But I have noticed that Backups are no longer happening. I cannot find the backup files where they are supposed to land. Some how, I cannot find any error messages relating why the backups are not getting created.

I do not know where I can look up the logs/reports of what possibly is going wrong. I have looked at the usual places and they are not there, for the times I have deliberately tried to submit the jobs.

Any help will be appreciated.

Thanks

Sam

Are the maint. plan jobs erroring? A db maint plan is fired off by a SQL Server Job. You should be able to look at the history of the job for a description of the failure. If there is no failure, check to make sure the job is enabled. If it is enabled, try running it by hand to see if the files are created.
Tim|||Open up your SQL Server error log. Are you actually getting messages in the log that databases are either backing up or are failing?

Database Backup & Restore

Most of the rows in one of the tables in our database were deleted by a DTS
package. (The DTS package does a complete replacement of the table.
Unfortunately the source is a "work" file which had been cleared.) I ended
up restoring the last backup of the database with a different name and then
copied the data from the alternate DB. But I was wondering, is it possible
to restore a single table in a database from a full database backup or can
you only restore the entire database?
Not with the native Sql Server backup and restore commands. There are some
third party products who claim to have object level restore capabilities but
I have not tried any of them. It can be a very dangerous thing and must be
done considering all the elements so you maintain your data integrity.
Andrew J. Kelly SQL MVP
"Evan Nelson" <EvanNelson@.discussions.microsoft.com> wrote in message
news:BBEB73EC-AB2B-48F5-92D8-AAAF056EE80C@.microsoft.com...
> Most of the rows in one of the tables in our database were deleted by a
> DTS
> package. (The DTS package does a complete replacement of the table.
> Unfortunately the source is a "work" file which had been cleared.) I
> ended
> up restoring the last backup of the database with a different name and
> then
> copied the data from the alternate DB. But I was wondering, is it
> possible
> to restore a single table in a database from a full database backup or can
> you only restore the entire database?

Database backup

I tried to backup a database but failed:
In EM, right click Northwind, select All Tasks, Backup a database...
Enter Description, select complete backup, Add a destination to a location,
set the shedule.
But there is not backup after the schedule time.
Anything I missed ?
Check inside Management, SQL Agent, Jobs and see if there is a n error
associated with the job
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uTPM1$rpEHA.3300@.TK2MSFTNGP12.phx.gbl...
>I tried to backup a database but failed:
> In EM, right click Northwind, select All Tasks, Backup a database...
> Enter Description, select complete backup, Add a destination to a
> location,
> set the shedule.
> But there is not backup after the schedule time.
> Anything I missed ?
>
>
|||Ah, may be the SQL Server Agent has not started.
"Mary Bray" <no@.spam.com> wrote in message
news:%231pAMcspEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Check inside Management, SQL Agent, Jobs and see if there is a n error
> associated with the job
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:uTPM1$rpEHA.3300@.TK2MSFTNGP12.phx.gbl...
>

Database backup

I tried to backup a database but failed:
In EM, right click Northwind, select All Tasks, Backup a database...
Enter Description, select complete backup, Add a destination to a location,
set the shedule.
But there is not backup after the schedule time.
Anything I missed ?Check inside Management, SQL Agent, Jobs and see if there is a n error
associated with the job
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uTPM1$rpEHA.3300@.TK2MSFTNGP12.phx.gbl...
>I tried to backup a database but failed:
> In EM, right click Northwind, select All Tasks, Backup a database...
> Enter Description, select complete backup, Add a destination to a
> location,
> set the shedule.
> But there is not backup after the schedule time.
> Anything I missed ?
>
>|||Ah, may be the SQL Server Agent has not started.
"Mary Bray" <no@.spam.com> wrote in message
news:%231pAMcspEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Check inside Management, SQL Agent, Jobs and see if there is a n error
> associated with the job
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:uTPM1$rpEHA.3300@.TK2MSFTNGP12.phx.gbl...
> >I tried to backup a database but failed:
> >
> > In EM, right click Northwind, select All Tasks, Backup a database...
> > Enter Description, select complete backup, Add a destination to a
> > location,
> > set the shedule.
> >
> > But there is not backup after the schedule time.
> >
> > Anything I missed ?
> >
> >
> >
>