Showing posts with label backs. Show all posts
Showing posts with label backs. Show all posts

Sunday, March 25, 2012

database backups

We have a Veritas application that backs up our databases at night. There are two databases that are backed up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines below. We the program gets to these tw
o databases the process that is running changes to spid? while the other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does the process start the database up first? Why would the database not be online
already? And what does the "IN LOAD" phrase mean?
Thanks,
backupDatabase backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backupDatabase restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
spid51Starting up database 'master4IDR'.
spid51Recovery is checkpointing database 'master4IDR' (15)
spid51Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51Starting up database 'master4IDR'.
spid51Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51Starting up database 'master4IDR'.
backupDatabase backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backupDatabase backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
backupDatabase restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
spid51Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
spid51Starting up database 'model4IDR'.
spid51Recovery is checkpointing database 'model4IDR' (15)
spid51Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51Starting up database 'model4IDR'.
spid51Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51Starting up database 'model4IDR'.
backupDatabase backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
Dan D.
Perhaps the autoclose database option is set for these databases. However, considering the names of the
database, my guess is that veritas does something strange to these databases, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
I'd be happy to hear... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
below. We the program gets to these two databases the process that is running changes to spid? while the
other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
the process start the database up first? Why would the database not be online already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> --
> Dan D.
|||Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm wondering why the file is there?
Dan D.
"Tibor Karaszi" wrote:

> Perhaps the autoclose database option is set for these databases. However, considering the names of the
> database, my guess is that veritas does something strange to these databases, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
> below. We the program gets to these two databases the process that is running changes to spid? while the
> other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be online already? And what does the "IN
> LOAD" phrase mean?
>
>
|||Again, my guess is that it is Veritas that creates these files and does some strange detach thingy. It is not
SQL Server. Profiler will tell you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm
wondering why the file is there?[vbcol=seagreen]
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
out,[vbcol=seagreen]
backed[vbcol=seagreen]
lines[vbcol=seagreen]
does[vbcol=seagreen]
"IN[vbcol=seagreen]

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

We have a Veritas application that backs up our databases at night. There ar
e two databases that are backed up - model4IDR and master4IDR - whose lines
in the log files are different. I've included some sample lines below. We t
he program gets to these tw
o databases the process that is running changes to spid' while the other da
tabases are backed up by "backup". These two databases don't show up in Ente
rprise Manager. Why does the process start the database up first? Why would
the database not be online
already? And what does the "IN LOAD" phrase mean?
Thanks,
backup Database backed up: Database: master, creation date(time): 2004/06/10
(20:01:42)
backup Database restored: Database: master4IDR, creation date(time): 2004/06
/10(20:01:
spid51 Starting up database 'master4IDR'.
spid51 Recovery is checkpointing database 'master4IDR' (15)
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN
LOAD.
spid51 Starting up database 'master4IDR'.
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN
LOAD.
spid51 Starting up database 'master4IDR'.
backup Database backed up: Database: master, creation date(time): 2004/06/10
(20:01:42)
backup Database backed up: Database: model, creation date(time): 2000/08/06(
01:40:52),
backup Database restored: Database: model4IDR, creation date(time): 2000/08/
06(01:40:5
spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored p
rocedure '
spid51 Starting up database 'model4IDR'.
spid51 Recovery is checkpointing database 'model4IDR' (15)
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN L
OAD.
spid51 Starting up database 'model4IDR'.
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN L
OAD.
spid51 Starting up database 'model4IDR'.
backup Database backed up: Database: model, creation date(time): 2000/08/06(
01:40:52),
--
Dan D.Perhaps the autoclose database option is set for these databases. However, c
onsidering the names of the
database, my guess is that veritas does something strange to these databases
, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas ac
tually does. If you do find out,
I'd be happy to hear... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two
databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different.
I've included some sample lines
below. We the program gets to these two databases the process that is runnin
g changes to spid' while the
other databases are backed up by "backup". These two databases don't show up
in Enterprise Manager. Why does
the process start the database up first? Why would the database not be onlin
e already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/
10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/
06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked I
N LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked I
N LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/
10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/0
6(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/0
8/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored
procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN
LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN
LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/0
6(01:40:52),
> --
> Dan D.|||Do you have any idea what the files are for? Since a database by that name d
oesn't show up in EE, I'm wondering why the file is there?
--
Dan D.
"Tibor Karaszi" wrote:

> Perhaps the autoclose database option is set for these databases. However,
considering the names of the
> database, my guess is that veritas does something strange to these databas
es, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas
actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> up - model4IDR and master4IDR - whose lines in the log files are different
. I've included some sample lines
> below. We the program gets to these two databases the process that is runn
ing changes to spid' while the
> other databases are backed up by "backup". These two databases don't show
up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be onl
ine already? And what does the "IN
> LOAD" phrase mean?
>
>|||Again, my guess is that it is Veritas that creates these files and does some
strange detach thingy. It is not
SQL Server. Profiler will tell you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't
show up in EE, I'm
wondering why the file is there?[vbcol=seagreen]
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
>
out,[vbcol=seagreen]
backed[vbcol=seagreen]
lines[vbcol=seagreen]
does[vbcol=seagreen]
"IN[vbcol=seagreen]

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

We have a Veritas application that backs up our databases at night. There are two databases that are backed up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines below. We the program gets to these two databases the process that is running changes to spid' while the other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does the process start the database up first? Why would the database not be online already? And what does the "IN LOAD" phrase mean?
Thanks,
backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
spid51 Starting up database 'master4IDR'.
spid51 Recovery is checkpointing database 'master4IDR' (15)
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51 Starting up database 'master4IDR'.
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51 Starting up database 'master4IDR'.
backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
spid51 Starting up database 'model4IDR'.
spid51 Recovery is checkpointing database 'model4IDR' (15)
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51 Starting up database 'model4IDR'.
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51 Starting up database 'model4IDR'.
backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
--
Dan D.Perhaps the autoclose database option is set for these databases. However, considering the names of the
database, my guess is that veritas does something strange to these databases, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
I'd be happy to hear... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
below. We the program gets to these two databases the process that is running changes to spid' while the
other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
the process start the database up first? Why would the database not be online already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> --
> Dan D.|||Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm wondering why the file is there?
--
Dan D.
"Tibor Karaszi" wrote:
> Perhaps the autoclose database option is set for these databases. However, considering the names of the
> database, my guess is that veritas does something strange to these databases, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> > We have a Veritas application that backs up our databases at night. There are two databases that are backed
> up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
> below. We the program gets to these two databases the process that is running changes to spid' while the
> other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be online already? And what does the "IN
> LOAD" phrase mean?
> >
> > Thanks,
> >
> > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> > spid51 Starting up database 'master4IDR'.
> > spid51 Recovery is checkpointing database 'master4IDR' (15)
> > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'master4IDR'.
> > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'master4IDR'.
> > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> > spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> > spid51 Starting up database 'model4IDR'.
> > spid51 Recovery is checkpointing database 'model4IDR' (15)
> > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'model4IDR'.
> > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'model4IDR'.
> > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > --
> > Dan D.
>
>|||Again, my guess is that it is Veritas that creates these files and does some strange detach thingy. It is not
SQL Server. Profiler will tell you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm
wondering why the file is there?
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
> > Perhaps the autoclose database option is set for these databases. However, considering the names of the
> > database, my guess is that veritas does something strange to these databases, like taking a copy of some
> > database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find
out,
> > I'd be happy to hear... :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> > > We have a Veritas application that backs up our databases at night. There are two databases that are
backed
> > up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample
lines
> > below. We the program gets to these two databases the process that is running changes to spid' while the
> > other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why
does
> > the process start the database up first? Why would the database not be online already? And what does the
"IN
> > LOAD" phrase mean?
> > >
> > > Thanks,
> > >
> > > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > > backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> > > spid51 Starting up database 'master4IDR'.
> > > spid51 Recovery is checkpointing database 'master4IDR' (15)
> > > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'master4IDR'.
> > > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'master4IDR'.
> > > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > > backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> > > spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> > > spid51 Starting up database 'model4IDR'.
> > > spid51 Recovery is checkpointing database 'model4IDR' (15)
> > > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'model4IDR'.
> > > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'model4IDR'.
> > > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > > --
> > > Dan D.
> >
> >
> >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.
>