Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
You must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was
>
|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>
Showing posts with label logs. Show all posts
Showing posts with label logs. Show all posts
Sunday, March 25, 2012
Database Backups and Transaction Logs
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was[vbcol=seagreen]
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set u
p
> that functionality as part of a job. I will add a nightly backup. We have
to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was[vbcol=seagreen]
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set u
p
> that functionality as part of a job. I will add a nightly backup. We have
to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>
Database Backups and Transaction Logs
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> > Problem: Transaction log grew too big.
> >
> > I took my database off line and then back online to ensure that no one
was
> > connected to the database and then backed up the database.
> >
> > I figured at this point I would be able to shrink the transaction log
> > because all of the 'pending' transactions would be clear - however I
> > cannot.
> >
> > Do I have to backup the transaction log? And in so doing will this solve
> > my
> > problem?
> >
> > Thanks
> >
> >
> >
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
>> Stephen,
>> Production database? If so, backup the transaction log then shrink the
>> transaction log file using DBCC SHRINKFILE statment.
>> HTH
>> Jerry
>> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
>> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
>> > Problem: Transaction log grew too big.
>> >
>> > I took my database off line and then back online to ensure that no one
> was
>> > connected to the database and then backed up the database.
>> >
>> > I figured at this point I would be able to shrink the transaction log
>> > because all of the 'pending' transactions would be clear - however I
>> > cannot.
>> >
>> > Do I have to backup the transaction log? And in so doing will this solve
>> > my
>> > problem?
>> >
>> > Thanks
>> >
>> >
>> >
>>
>
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> > Problem: Transaction log grew too big.
> >
> > I took my database off line and then back online to ensure that no one
was
> > connected to the database and then backed up the database.
> >
> > I figured at this point I would be able to shrink the transaction log
> > because all of the 'pending' transactions would be clear - however I
> > cannot.
> >
> > Do I have to backup the transaction log? And in so doing will this solve
> > my
> > problem?
> >
> > Thanks
> >
> >
> >
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
>> Stephen,
>> Production database? If so, backup the transaction log then shrink the
>> transaction log file using DBCC SHRINKFILE statment.
>> HTH
>> Jerry
>> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
>> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
>> > Problem: Transaction log grew too big.
>> >
>> > I took my database off line and then back online to ensure that no one
> was
>> > connected to the database and then backed up the database.
>> >
>> > I figured at this point I would be able to shrink the transaction log
>> > because all of the 'pending' transactions would be clear - however I
>> > cannot.
>> >
>> > Do I have to backup the transaction log? And in so doing will this solve
>> > my
>> > problem?
>> >
>> > 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 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.
>
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
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.
>
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.
>
Wednesday, March 21, 2012
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?
Subscribe to:
Posts (Atom)