Connect to CE Remotely

If I have SQL 2005 Standard installed on a server and I log on locally to that server. Can I connect to other machines through Management Studio-->Object Explorer that have SQLCE installed and manage the CE DB?

I think you can only connect to either local .sdf files or ones located on a device attached by USB to the local machine, but I have had some difficulty finding a definitive statement on that.

Thanks

SQL CE is in process database (a bunch of DLLs loaded by your application) , it has no remote connectivity. There’s a very special remote provider which allows you to access files on device from desktop.

You also might be able to “connect” (quoted because you’re not really connecting to anything – SQL CE is within your process) to the database located on a file shares though it might have some issues like sharing.

Confused...SA Account

Hi
I have a two node cluster with SQL2000 SP3. When the node1 fails over to
node2, the SQL Server Agent fails. The log file says "Login Failed for user
'sa'" I don't understand how the SA account can fail on node2 and not on
node1. Can anyone shed some light on this and possibly how to resolve the
issue.
Thank You
Marc
What security account context is the SQL Agent running under? Is it a domain account and does it have adequate permissions/rights on both nodes?
|||Hi Stephen
SQL security is set for "SQL Server and Windows" and the service account is
in the Domain Admins group. Same service account is used for both nodes.
Regards
Marc
"Stephen Strong" <anonymous@.discussions.microsoft.com> wrote in message
news:39A963C4-A818-487F-8B10-C38CC58715FA@.microsoft.com...
> What security account context is the SQL Agent running under? Is it a
domain account and does it have adequate permissions/rights on both nodes?
|||Is the SQL Server Agent startup account a member of the local admin group
on both nodes. If you are using SQL Server authentication for SQL Server
Agent to connect to SQL Server then the SQL Server Agent startup account
has to be a local Admin.
You can check the SQL Agent connection properties by right clicking on SQL
Server Agent - Properties - Connection and see how the connection to SQL
Server is set up.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Thanks Guys
As it turns out, one of our programmers changed the authentication method to
Windows rather than SQL. I changed it back and all is well.
Regards
Marc
"Marc" <Marc@.nospam.com> wrote in message
news:%23u4AZPJJEHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a two node cluster with SQL2000 SP3. When the node1 fails over to
> node2, the SQL Server Agent fails. The log file says "Login Failed for
user
> 'sa'" I don't understand how the SA account can fail on node2 and not on
> node1. Can anyone shed some light on this and possibly how to resolve the
> issue.
> Thank You
> Marc
>

Confused about relation between full backup and transaction log backup

Hi everyone,
Would someone be able to help with a question I have about the
strangeness of my backup process/results. My schema is:
Full Backup: 2:00am each day
Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
min intervals
Full database reindex: 11:00pm on Sundays
What I found was that from Tuesday to Sunday my transactional log
backups are of minimal size. However the Monday 7:00am transactional
log backup is around the size of the database itself i.e. 30 Gigs. And
this happens each Monday 7:00am. I can only nail this down to the fact
that a full database reindex has taken place between Sunday 10:30pm and
Monday 7:00am - which is infact the case. However, my full backup takes
place on Monday 2:00am. So why should the transactional be so large? I
thought the transactional backup is a backup of:
1) transactions since the last full backup (which in my case is 2:00am
i.e. AFTER the reindex had occured - hence the 7:00am log backup should
only show transactions since the last full backup NOT the last
transactional backup) OR,
2) the last transactional backup if there has been a full backup prior
to the last log backup.
It seems as though my transactional log backup process does not depend
on my full backup process.
Does this mean that if I wanted to restore (for example) to Friday
5:00pm I could either do:
a) take a Tuesday 2:00am full backup and apply transactional backups
from Tuesday 7am to Friday 5p, OR
b) take Friday 2am full backup and apply transactional backups from
Friday 2am to Friday 5pm?
I think I am confused and hard fixed on the idea that if you implement
both full and transactional backups, the transactonal backup that takes
place immediately after a full backup contains transactions relative to
the last full backup, NOT the last transactional backup. Can anyone
confirm this?
When the answer is "relative to the last transactional backup", I guess
when you restore from the last full backup (created at 2am) and apply a
transactional backup against it (created at 7am), the system is clever
enough to disregard all transactions within the transaction log that
occured between 10:30pm and 2am - thuis only applying those
transactions between 2am and 7am. Is this correct?
Any help most appreciated in sorting out my confusion!
Many thanks,
PeterBottom line is that the two backups really have nothing to do with each
other except that you need a valid FULL backup to start the log chain. From
there on out you can back up the db with a FULL backup as many or as few
times as you want and the log backups always go from the last log backup.
It is a continuous chain. So yes if you had failure and the most recent FULL
backup was corrupted you can go back to the one before that and restore all
the logs up to the last one, right past where the last FULL backup was
taken. Same goes for a Differential backup. It does not affect the Log
backups at all. But unlike the Logs a Differential is always from the last
FULL backup.
Andrew J. Kelly SQL MVP
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>|||A full backup is a complete, consistant copy of the database at a single
point in time. If you are in FULL or BULK-LOGGED recovery mode, you can
apply one or more transaction logs in sequential order during the restore
process to move the database forward through time from the initial backup
time point. The large transaction log backup size is likely a side effect
of a reindex maintenance function.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>|||Thanks for the response.
so in effect, if i have:
backup1: 2am transactional backup
backup2: 5am full backup
backup3: 7am transactional backup
I would like to reconfirm... am i correct in saying that:
1) backup3 contains all transactions that occured between the end of backup1
and the start of backup3?
2) I can apply backup3 to backup2. In this case not all those transactions
contained in backup3 will be applied against backup2 i.e. only those
occuring between the end of backup2 and the start of backup 3 will be
applied.
Thanks again!
peter
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>|||Yes and yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"peter" <peter@.nospam.com> wrote in message news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...[
vbcol=seagreen]
> Thanks for the response.
> so in effect, if i have:
> backup1: 2am transactional backup
> backup2: 5am full backup
> backup3: 7am transactional backup
> I would like to reconfirm... am i correct in saying that:
> 1) backup3 contains all transactions that occured between the end of backu
p1
> and the start of backup3?
> 2) I can apply backup3 to backup2. In this case not all those transactions
> contained in backup3 will be applied against backup2 i.e. only those
> occuring between the end of backup2 and the start of backup 3 will be
> applied.
> Thanks again!
> peter
> <mag1kus@.yahoo.com> wrote in message
> news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
>[/vbcol]|||1) backup3 contains all transactions that occured between the end of backup1
and the start of backup3?
If the transaction log backups are created WITH INIT they will only contain
transactions since the previous log backup.
Keith Kratochvil
"peter" <peter@.nospam.com> wrote in message
news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Thanks for the response.
> so in effect, if i have:
> backup1: 2am transactional backup
> backup2: 5am full backup
> backup3: 7am transactional backup
> I would like to reconfirm... am i correct in saying that:
> 1) backup3 contains all transactions that occured between the end of
> backup1 and the start of backup3?
> 2) I can apply backup3 to backup2. In this case not all those transactions
> contained in backup3 will be applied against backup2 i.e. only those
> occuring between the end of backup2 and the start of backup 3 will be
> applied.
> Thanks again!
> peter
> <mag1kus@.yahoo.com> wrote in message
> news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
>|||thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OED7V6dQGHA.4312@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Yes and yes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "peter" <peter@.nospam.com> wrote in message
> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...|||but doesnt WITH INIT just overwrite all previous log backup sets (if used
against log backups)?
So irrespective of whether WITH INIT is used or not, any transaction log
backup will contain transactions since the previous log backup?
peter
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uaIzoNgQGHA.1096@.TK2MSFTNGP11.phx.gbl...
> 1) backup3 contains all transactions that occured between the end of
> backup1
> and the start of backup3?
> If the transaction log backups are created WITH INIT they will only
> contain transactions since the previous log backup.
> --
> Keith Kratochvil
>
> "peter" <peter@.nospam.com> wrote in message
> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>|||> So irrespective of whether WITH INIT is used or not, any transaction log
> backup will contain transactions since the previous log backup?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"peter" <peter@.nospam.com> wrote in message news:uTek9jsQGHA.3872@.TK2MSFTNGP15.phx.gbl...[vb
col=seagreen]
> but doesnt WITH INIT just overwrite all previous log backup sets (if used
> against log backups)?
> So irrespective of whether WITH INIT is used or not, any transaction log
> backup will contain transactions since the previous log backup?
> peter
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uaIzoNgQGHA.1096@.TK2MSFTNGP11.phx.gbl...
>[/vbcol]

Confused about relation between full backup and transaction log backup

Hi everyone,
Would someone be able to help with a question I have about the
strangeness of my backup process/results. My schema is:
Full Backup: 2:00am each day
Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
min intervals
Full database reindex: 11:00pm on Sundays
What I found was that from Tuesday to Sunday my transactional log
backups are of minimal size. However the Monday 7:00am transactional
log backup is around the size of the database itself i.e. 30 Gigs. And
this happens each Monday 7:00am. I can only nail this down to the fact
that a full database reindex has taken place between Sunday 10:30pm and
Monday 7:00am - which is infact the case. However, my full backup takes
place on Monday 2:00am. So why should the transactional be so large? I
thought the transactional backup is a backup of:
1) transactions since the last full backup (which in my case is 2:00am
i.e. AFTER the reindex had occured - hence the 7:00am log backup should
only show transactions since the last full backup NOT the last
transactional backup) OR,
2) the last transactional backup if there has been a full backup prior
to the last log backup.
It seems as though my transactional log backup process does not depend
on my full backup process.
Does this mean that if I wanted to restore (for example) to Friday
5:00pm I could either do:
a) take a Tuesday 2:00am full backup and apply transactional backups
from Tuesday 7am to Friday 5p, OR
b) take Friday 2am full backup and apply transactional backups from
Friday 2am to Friday 5pm?
I think I am confused and hard fixed on the idea that if you implement
both full and transactional backups, the transactonal backup that takes
place immediately after a full backup contains transactions relative to
the last full backup, NOT the last transactional backup. Can anyone
confirm this?
When the answer is "relative to the last transactional backup", I guess
when you restore from the last full backup (created at 2am) and apply a
transactional backup against it (created at 7am), the system is clever
enough to disregard all transactions within the transaction log that
occured between 10:30pm and 2am - thuis only applying those
transactions between 2am and 7am. Is this correct?
Any help most appreciated in sorting out my confusion!
Many thanks,
Peter
Bottom line is that the two backups really have nothing to do with each
other except that you need a valid FULL backup to start the log chain. From
there on out you can back up the db with a FULL backup as many or as few
times as you want and the log backups always go from the last log backup.
It is a continuous chain. So yes if you had failure and the most recent FULL
backup was corrupted you can go back to the one before that and restore all
the logs up to the last one, right past where the last FULL backup was
taken. Same goes for a Differential backup. It does not affect the Log
backups at all. But unlike the Logs a Differential is always from the last
FULL backup.
Andrew J. Kelly SQL MVP
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>
|||A full backup is a complete, consistant copy of the database at a single
point in time. If you are in FULL or BULK-LOGGED recovery mode, you can
apply one or more transaction logs in sequential order during the restore
process to move the database forward through time from the initial backup
time point. The large transaction log backup size is likely a side effect
of a reindex maintenance function.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>
|||Thanks for the response.
so in effect, if i have:
backup1: 2am transactional backup
backup2: 5am full backup
backup3: 7am transactional backup
I would like to reconfirm... am i correct in saying that:
1) backup3 contains all transactions that occured between the end of backup1
and the start of backup3?
2) I can apply backup3 to backup2. In this case not all those transactions
contained in backup3 will be applied against backup2 i.e. only those
occuring between the end of backup2 and the start of backup 3 will be
applied.
Thanks again!
peter
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>
|||Yes and yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"peter" <peter@.nospam.com> wrote in message news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Thanks for the response.
> so in effect, if i have:
> backup1: 2am transactional backup
> backup2: 5am full backup
> backup3: 7am transactional backup
> I would like to reconfirm... am i correct in saying that:
> 1) backup3 contains all transactions that occured between the end of backup1
> and the start of backup3?
> 2) I can apply backup3 to backup2. In this case not all those transactions
> contained in backup3 will be applied against backup2 i.e. only those
> occuring between the end of backup2 and the start of backup 3 will be
> applied.
> Thanks again!
> peter
> <mag1kus@.yahoo.com> wrote in message
> news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
>
|||1) backup3 contains all transactions that occured between the end of backup1
and the start of backup3?
If the transaction log backups are created WITH INIT they will only contain
transactions since the previous log backup.
Keith Kratochvil
"peter" <peter@.nospam.com> wrote in message
news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Thanks for the response.
> so in effect, if i have:
> backup1: 2am transactional backup
> backup2: 5am full backup
> backup3: 7am transactional backup
> I would like to reconfirm... am i correct in saying that:
> 1) backup3 contains all transactions that occured between the end of
> backup1 and the start of backup3?
> 2) I can apply backup3 to backup2. In this case not all those transactions
> contained in backup3 will be applied against backup2 i.e. only those
> occuring between the end of backup2 and the start of backup 3 will be
> applied.
> Thanks again!
> peter
> <mag1kus@.yahoo.com> wrote in message
> news:1141687158.413556.213540@.j33g2000cwa.googlegr oups.com...
>
|||thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OED7V6dQGHA.4312@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Yes and yes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "peter" <peter@.nospam.com> wrote in message
> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
|||but doesnt WITH INIT just overwrite all previous log backup sets (if used
against log backups)?
So irrespective of whether WITH INIT is used or not, any transaction log
backup will contain transactions since the previous log backup?
peter
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uaIzoNgQGHA.1096@.TK2MSFTNGP11.phx.gbl...
> 1) backup3 contains all transactions that occured between the end of
> backup1
> and the start of backup3?
> If the transaction log backups are created WITH INIT they will only
> contain transactions since the previous log backup.
> --
> Keith Kratochvil
>
> "peter" <peter@.nospam.com> wrote in message
> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>
|||> So irrespective of whether WITH INIT is used or not, any transaction log
> backup will contain transactions since the previous log backup?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"peter" <peter@.nospam.com> wrote in message news:uTek9jsQGHA.3872@.TK2MSFTNGP15.phx.gbl...
> but doesnt WITH INIT just overwrite all previous log backup sets (if used
> against log backups)?
> So irrespective of whether WITH INIT is used or not, any transaction log
> backup will contain transactions since the previous log backup?
> peter
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uaIzoNgQGHA.1096@.TK2MSFTNGP11.phx.gbl...
>

Confused about relation between full backup and transaction log backup

Hi everyone,
Would someone be able to help with a question I have about the
strangeness of my backup process/results. My schema is:
Full Backup: 2:00am each day
Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
min intervals
Full database reindex: 11:00pm on Sundays
What I found was that from Tuesday to Sunday my transactional log
backups are of minimal size. However the Monday 7:00am transactional
log backup is around the size of the database itself i.e. 30 Gigs. And
this happens each Monday 7:00am. I can only nail this down to the fact
that a full database reindex has taken place between Sunday 10:30pm and
Monday 7:00am - which is infact the case. However, my full backup takes
place on Monday 2:00am. So why should the transactional be so large? I
thought the transactional backup is a backup of:
1) transactions since the last full backup (which in my case is 2:00am
i.e. AFTER the reindex had occured - hence the 7:00am log backup should
only show transactions since the last full backup NOT the last
transactional backup) OR,
2) the last transactional backup if there has been a full backup prior
to the last log backup.
It seems as though my transactional log backup process does not depend
on my full backup process.
Does this mean that if I wanted to restore (for example) to Friday
5:00pm I could either do:
a) take a Tuesday 2:00am full backup and apply transactional backups
from Tuesday 7am to Friday 5p, OR
b) take Friday 2am full backup and apply transactional backups from
Friday 2am to Friday 5pm?
I think I am confused and hard fixed on the idea that if you implement
both full and transactional backups, the transactonal backup that takes
place immediately after a full backup contains transactions relative to
the last full backup, NOT the last transactional backup. Can anyone
confirm this?
When the answer is "relative to the last transactional backup", I guess
when you restore from the last full backup (created at 2am) and apply a
transactional backup against it (created at 7am), the system is clever
enough to disregard all transactions within the transaction log that
occured between 10:30pm and 2am - thuis only applying those
transactions between 2am and 7am. Is this correct?
Any help most appreciated in sorting out my confusion!
Many thanks,
PeterBottom line is that the two backups really have nothing to do with each
other except that you need a valid FULL backup to start the log chain. From
there on out you can back up the db with a FULL backup as many or as few
times as you want and the log backups always go from the last log backup.
It is a continuous chain. So yes if you had failure and the most recent FULL
backup was corrupted you can go back to the one before that and restore all
the logs up to the last one, right past where the last FULL backup was
taken. Same goes for a Differential backup. It does not affect the Log
backups at all. But unlike the Logs a Differential is always from the last
FULL backup.
--
Andrew J. Kelly SQL MVP
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>|||A full backup is a complete, consistant copy of the database at a single
point in time. If you are in FULL or BULK-LOGGED recovery mode, you can
apply one or more transaction logs in sequential order during the restore
process to move the database forward through time from the initial backup
time point. The large transaction log backup size is likely a side effect
of a reindex maintenance function.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>|||Thanks for the response.
so in effect, if i have:
backup1: 2am transactional backup
backup2: 5am full backup
backup3: 7am transactional backup
I would like to reconfirm... am i correct in saying that:
1) backup3 contains all transactions that occured between the end of backup1
and the start of backup3?
2) I can apply backup3 to backup2. In this case not all those transactions
contained in backup3 will be applied against backup2 i.e. only those
occuring between the end of backup2 and the start of backup 3 will be
applied.
Thanks again!
peter
<mag1kus@.yahoo.com> wrote in message
news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
> Hi everyone,
> Would someone be able to help with a question I have about the
> strangeness of my backup process/results. My schema is:
> Full Backup: 2:00am each day
> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
> min intervals
> Full database reindex: 11:00pm on Sundays
> What I found was that from Tuesday to Sunday my transactional log
> backups are of minimal size. However the Monday 7:00am transactional
> log backup is around the size of the database itself i.e. 30 Gigs. And
> this happens each Monday 7:00am. I can only nail this down to the fact
> that a full database reindex has taken place between Sunday 10:30pm and
> Monday 7:00am - which is infact the case. However, my full backup takes
> place on Monday 2:00am. So why should the transactional be so large? I
> thought the transactional backup is a backup of:
> 1) transactions since the last full backup (which in my case is 2:00am
> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
> only show transactions since the last full backup NOT the last
> transactional backup) OR,
> 2) the last transactional backup if there has been a full backup prior
> to the last log backup.
> It seems as though my transactional log backup process does not depend
> on my full backup process.
> Does this mean that if I wanted to restore (for example) to Friday
> 5:00pm I could either do:
> a) take a Tuesday 2:00am full backup and apply transactional backups
> from Tuesday 7am to Friday 5p, OR
> b) take Friday 2am full backup and apply transactional backups from
> Friday 2am to Friday 5pm?
> I think I am confused and hard fixed on the idea that if you implement
> both full and transactional backups, the transactonal backup that takes
> place immediately after a full backup contains transactions relative to
> the last full backup, NOT the last transactional backup. Can anyone
> confirm this?
> When the answer is "relative to the last transactional backup", I guess
> when you restore from the last full backup (created at 2am) and apply a
> transactional backup against it (created at 7am), the system is clever
> enough to disregard all transactions within the transaction log that
> occured between 10:30pm and 2am - thuis only applying those
> transactions between 2am and 7am. Is this correct?
> Any help most appreciated in sorting out my confusion!
> Many thanks,
> Peter
>|||Yes and yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"peter" <peter@.nospam.com> wrote in message news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Thanks for the response.
> so in effect, if i have:
> backup1: 2am transactional backup
> backup2: 5am full backup
> backup3: 7am transactional backup
> I would like to reconfirm... am i correct in saying that:
> 1) backup3 contains all transactions that occured between the end of backup1
> and the start of backup3?
> 2) I can apply backup3 to backup2. In this case not all those transactions
> contained in backup3 will be applied against backup2 i.e. only those
> occuring between the end of backup2 and the start of backup 3 will be
> applied.
> Thanks again!
> peter
> <mag1kus@.yahoo.com> wrote in message
> news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
>> Hi everyone,
>> Would someone be able to help with a question I have about the
>> strangeness of my backup process/results. My schema is:
>> Full Backup: 2:00am each day
>> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
>> min intervals
>> Full database reindex: 11:00pm on Sundays
>> What I found was that from Tuesday to Sunday my transactional log
>> backups are of minimal size. However the Monday 7:00am transactional
>> log backup is around the size of the database itself i.e. 30 Gigs. And
>> this happens each Monday 7:00am. I can only nail this down to the fact
>> that a full database reindex has taken place between Sunday 10:30pm and
>> Monday 7:00am - which is infact the case. However, my full backup takes
>> place on Monday 2:00am. So why should the transactional be so large? I
>> thought the transactional backup is a backup of:
>> 1) transactions since the last full backup (which in my case is 2:00am
>> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
>> only show transactions since the last full backup NOT the last
>> transactional backup) OR,
>> 2) the last transactional backup if there has been a full backup prior
>> to the last log backup.
>> It seems as though my transactional log backup process does not depend
>> on my full backup process.
>> Does this mean that if I wanted to restore (for example) to Friday
>> 5:00pm I could either do:
>> a) take a Tuesday 2:00am full backup and apply transactional backups
>> from Tuesday 7am to Friday 5p, OR
>> b) take Friday 2am full backup and apply transactional backups from
>> Friday 2am to Friday 5pm?
>> I think I am confused and hard fixed on the idea that if you implement
>> both full and transactional backups, the transactonal backup that takes
>> place immediately after a full backup contains transactions relative to
>> the last full backup, NOT the last transactional backup. Can anyone
>> confirm this?
>> When the answer is "relative to the last transactional backup", I guess
>> when you restore from the last full backup (created at 2am) and apply a
>> transactional backup against it (created at 7am), the system is clever
>> enough to disregard all transactions within the transaction log that
>> occured between 10:30pm and 2am - thuis only applying those
>> transactions between 2am and 7am. Is this correct?
>> Any help most appreciated in sorting out my confusion!
>> Many thanks,
>> Peter
>|||1) backup3 contains all transactions that occured between the end of backup1
and the start of backup3?
If the transaction log backups are created WITH INIT they will only contain
transactions since the previous log backup.
--
Keith Kratochvil
"peter" <peter@.nospam.com> wrote in message
news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Thanks for the response.
> so in effect, if i have:
> backup1: 2am transactional backup
> backup2: 5am full backup
> backup3: 7am transactional backup
> I would like to reconfirm... am i correct in saying that:
> 1) backup3 contains all transactions that occured between the end of
> backup1 and the start of backup3?
> 2) I can apply backup3 to backup2. In this case not all those transactions
> contained in backup3 will be applied against backup2 i.e. only those
> occuring between the end of backup2 and the start of backup 3 will be
> applied.
> Thanks again!
> peter
> <mag1kus@.yahoo.com> wrote in message
> news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
>> Hi everyone,
>> Would someone be able to help with a question I have about the
>> strangeness of my backup process/results. My schema is:
>> Full Backup: 2:00am each day
>> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
>> min intervals
>> Full database reindex: 11:00pm on Sundays
>> What I found was that from Tuesday to Sunday my transactional log
>> backups are of minimal size. However the Monday 7:00am transactional
>> log backup is around the size of the database itself i.e. 30 Gigs. And
>> this happens each Monday 7:00am. I can only nail this down to the fact
>> that a full database reindex has taken place between Sunday 10:30pm and
>> Monday 7:00am - which is infact the case. However, my full backup takes
>> place on Monday 2:00am. So why should the transactional be so large? I
>> thought the transactional backup is a backup of:
>> 1) transactions since the last full backup (which in my case is 2:00am
>> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
>> only show transactions since the last full backup NOT the last
>> transactional backup) OR,
>> 2) the last transactional backup if there has been a full backup prior
>> to the last log backup.
>> It seems as though my transactional log backup process does not depend
>> on my full backup process.
>> Does this mean that if I wanted to restore (for example) to Friday
>> 5:00pm I could either do:
>> a) take a Tuesday 2:00am full backup and apply transactional backups
>> from Tuesday 7am to Friday 5p, OR
>> b) take Friday 2am full backup and apply transactional backups from
>> Friday 2am to Friday 5pm?
>> I think I am confused and hard fixed on the idea that if you implement
>> both full and transactional backups, the transactonal backup that takes
>> place immediately after a full backup contains transactions relative to
>> the last full backup, NOT the last transactional backup. Can anyone
>> confirm this?
>> When the answer is "relative to the last transactional backup", I guess
>> when you restore from the last full backup (created at 2am) and apply a
>> transactional backup against it (created at 7am), the system is clever
>> enough to disregard all transactions within the transaction log that
>> occured between 10:30pm and 2am - thuis only applying those
>> transactions between 2am and 7am. Is this correct?
>> Any help most appreciated in sorting out my confusion!
>> Many thanks,
>> Peter
>|||thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OED7V6dQGHA.4312@.TK2MSFTNGP12.phx.gbl...
> Yes and yes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "peter" <peter@.nospam.com> wrote in message
> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>> Thanks for the response.
>> so in effect, if i have:
>> backup1: 2am transactional backup
>> backup2: 5am full backup
>> backup3: 7am transactional backup
>> I would like to reconfirm... am i correct in saying that:
>> 1) backup3 contains all transactions that occured between the end of
>> backup1 and the start of backup3?
>> 2) I can apply backup3 to backup2. In this case not all those
>> transactions contained in backup3 will be applied against backup2 i.e.
>> only those occuring between the end of backup2 and the start of backup 3
>> will be applied.
>> Thanks again!
>> peter
>> <mag1kus@.yahoo.com> wrote in message
>> news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
>> Hi everyone,
>> Would someone be able to help with a question I have about the
>> strangeness of my backup process/results. My schema is:
>> Full Backup: 2:00am each day
>> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
>> min intervals
>> Full database reindex: 11:00pm on Sundays
>> What I found was that from Tuesday to Sunday my transactional log
>> backups are of minimal size. However the Monday 7:00am transactional
>> log backup is around the size of the database itself i.e. 30 Gigs. And
>> this happens each Monday 7:00am. I can only nail this down to the fact
>> that a full database reindex has taken place between Sunday 10:30pm and
>> Monday 7:00am - which is infact the case. However, my full backup takes
>> place on Monday 2:00am. So why should the transactional be so large? I
>> thought the transactional backup is a backup of:
>> 1) transactions since the last full backup (which in my case is 2:00am
>> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
>> only show transactions since the last full backup NOT the last
>> transactional backup) OR,
>> 2) the last transactional backup if there has been a full backup prior
>> to the last log backup.
>> It seems as though my transactional log backup process does not depend
>> on my full backup process.
>> Does this mean that if I wanted to restore (for example) to Friday
>> 5:00pm I could either do:
>> a) take a Tuesday 2:00am full backup and apply transactional backups
>> from Tuesday 7am to Friday 5p, OR
>> b) take Friday 2am full backup and apply transactional backups from
>> Friday 2am to Friday 5pm?
>> I think I am confused and hard fixed on the idea that if you implement
>> both full and transactional backups, the transactonal backup that takes
>> place immediately after a full backup contains transactions relative to
>> the last full backup, NOT the last transactional backup. Can anyone
>> confirm this?
>> When the answer is "relative to the last transactional backup", I guess
>> when you restore from the last full backup (created at 2am) and apply a
>> transactional backup against it (created at 7am), the system is clever
>> enough to disregard all transactions within the transaction log that
>> occured between 10:30pm and 2am - thuis only applying those
>> transactions between 2am and 7am. Is this correct?
>> Any help most appreciated in sorting out my confusion!
>> Many thanks,
>> Peter
>>|||but doesnt WITH INIT just overwrite all previous log backup sets (if used
against log backups)?
So irrespective of whether WITH INIT is used or not, any transaction log
backup will contain transactions since the previous log backup?
peter
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uaIzoNgQGHA.1096@.TK2MSFTNGP11.phx.gbl...
> 1) backup3 contains all transactions that occured between the end of
> backup1
> and the start of backup3?
> If the transaction log backups are created WITH INIT they will only
> contain transactions since the previous log backup.
> --
> Keith Kratochvil
>
> "peter" <peter@.nospam.com> wrote in message
> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>> Thanks for the response.
>> so in effect, if i have:
>> backup1: 2am transactional backup
>> backup2: 5am full backup
>> backup3: 7am transactional backup
>> I would like to reconfirm... am i correct in saying that:
>> 1) backup3 contains all transactions that occured between the end of
>> backup1 and the start of backup3?
>> 2) I can apply backup3 to backup2. In this case not all those
>> transactions contained in backup3 will be applied against backup2 i.e.
>> only those occuring between the end of backup2 and the start of backup 3
>> will be applied.
>> Thanks again!
>> peter
>> <mag1kus@.yahoo.com> wrote in message
>> news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
>> Hi everyone,
>> Would someone be able to help with a question I have about the
>> strangeness of my backup process/results. My schema is:
>> Full Backup: 2:00am each day
>> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
>> min intervals
>> Full database reindex: 11:00pm on Sundays
>> What I found was that from Tuesday to Sunday my transactional log
>> backups are of minimal size. However the Monday 7:00am transactional
>> log backup is around the size of the database itself i.e. 30 Gigs. And
>> this happens each Monday 7:00am. I can only nail this down to the fact
>> that a full database reindex has taken place between Sunday 10:30pm and
>> Monday 7:00am - which is infact the case. However, my full backup takes
>> place on Monday 2:00am. So why should the transactional be so large? I
>> thought the transactional backup is a backup of:
>> 1) transactions since the last full backup (which in my case is 2:00am
>> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
>> only show transactions since the last full backup NOT the last
>> transactional backup) OR,
>> 2) the last transactional backup if there has been a full backup prior
>> to the last log backup.
>> It seems as though my transactional log backup process does not depend
>> on my full backup process.
>> Does this mean that if I wanted to restore (for example) to Friday
>> 5:00pm I could either do:
>> a) take a Tuesday 2:00am full backup and apply transactional backups
>> from Tuesday 7am to Friday 5p, OR
>> b) take Friday 2am full backup and apply transactional backups from
>> Friday 2am to Friday 5pm?
>> I think I am confused and hard fixed on the idea that if you implement
>> both full and transactional backups, the transactonal backup that takes
>> place immediately after a full backup contains transactions relative to
>> the last full backup, NOT the last transactional backup. Can anyone
>> confirm this?
>> When the answer is "relative to the last transactional backup", I guess
>> when you restore from the last full backup (created at 2am) and apply a
>> transactional backup against it (created at 7am), the system is clever
>> enough to disregard all transactions within the transaction log that
>> occured between 10:30pm and 2am - thuis only applying those
>> transactions between 2am and 7am. Is this correct?
>> Any help most appreciated in sorting out my confusion!
>> Many thanks,
>> Peter
>>
>|||> So irrespective of whether WITH INIT is used or not, any transaction log
> backup will contain transactions since the previous log backup?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"peter" <peter@.nospam.com> wrote in message news:uTek9jsQGHA.3872@.TK2MSFTNGP15.phx.gbl...
> but doesnt WITH INIT just overwrite all previous log backup sets (if used
> against log backups)?
> So irrespective of whether WITH INIT is used or not, any transaction log
> backup will contain transactions since the previous log backup?
> peter
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uaIzoNgQGHA.1096@.TK2MSFTNGP11.phx.gbl...
>> 1) backup3 contains all transactions that occured between the end of
>> backup1
>> and the start of backup3?
>> If the transaction log backups are created WITH INIT they will only
>> contain transactions since the previous log backup.
>> --
>> Keith Kratochvil
>>
>> "peter" <peter@.nospam.com> wrote in message
>> news:%23h5W9WcQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>> Thanks for the response.
>> so in effect, if i have:
>> backup1: 2am transactional backup
>> backup2: 5am full backup
>> backup3: 7am transactional backup
>> I would like to reconfirm... am i correct in saying that:
>> 1) backup3 contains all transactions that occured between the end of
>> backup1 and the start of backup3?
>> 2) I can apply backup3 to backup2. In this case not all those
>> transactions contained in backup3 will be applied against backup2 i.e.
>> only those occuring between the end of backup2 and the start of backup 3
>> will be applied.
>> Thanks again!
>> peter
>> <mag1kus@.yahoo.com> wrote in message
>> news:1141687158.413556.213540@.j33g2000cwa.googlegroups.com...
>> Hi everyone,
>> Would someone be able to help with a question I have about the
>> strangeness of my backup process/results. My schema is:
>> Full Backup: 2:00am each day
>> Transactional log backup: 7:00am - 10:30pm (inclusive) each day at 30
>> min intervals
>> Full database reindex: 11:00pm on Sundays
>> What I found was that from Tuesday to Sunday my transactional log
>> backups are of minimal size. However the Monday 7:00am transactional
>> log backup is around the size of the database itself i.e. 30 Gigs. And
>> this happens each Monday 7:00am. I can only nail this down to the fact
>> that a full database reindex has taken place between Sunday 10:30pm and
>> Monday 7:00am - which is infact the case. However, my full backup takes
>> place on Monday 2:00am. So why should the transactional be so large? I
>> thought the transactional backup is a backup of:
>> 1) transactions since the last full backup (which in my case is 2:00am
>> i.e. AFTER the reindex had occured - hence the 7:00am log backup should
>> only show transactions since the last full backup NOT the last
>> transactional backup) OR,
>> 2) the last transactional backup if there has been a full backup prior
>> to the last log backup.
>> It seems as though my transactional log backup process does not depend
>> on my full backup process.
>> Does this mean that if I wanted to restore (for example) to Friday
>> 5:00pm I could either do:
>> a) take a Tuesday 2:00am full backup and apply transactional backups
>> from Tuesday 7am to Friday 5p, OR
>> b) take Friday 2am full backup and apply transactional backups from
>> Friday 2am to Friday 5pm?
>> I think I am confused and hard fixed on the idea that if you implement
>> both full and transactional backups, the transactonal backup that takes
>> place immediately after a full backup contains transactions relative to
>> the last full backup, NOT the last transactional backup. Can anyone
>> confirm this?
>> When the answer is "relative to the last transactional backup", I guess
>> when you restore from the last full backup (created at 2am) and apply a
>> transactional backup against it (created at 7am), the system is clever
>> enough to disregard all transactions within the transaction log that
>> occured between 10:30pm and 2am - thuis only applying those
>> transactions between 2am and 7am. Is this correct?
>> Any help most appreciated in sorting out my confusion!
>> Many thanks,
>> Peter
>>
>>
>

Conflict Viewer usage for past conflicts

Hello,
When conflicts arise, we can use the conflict viewer. We have checked the
box that says to log them for future use. Help says they go to the system
tables.
I am trying to find conflict information from several replications ago, but
can't find them. Is there a certain system table they go to?
Thanks,
Steve
Steve,
the tables that maintain conflict information are:
conflict_<PublicationName>_<ArticleName>_usertable name and
MSmerge_delete_conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Conflict Resolution: Is defining local subscription enough: How to get log of lost record.

Hello All,
I am very much thankful to you all for providing much needed help, and
once again I am at the group with my query. In my replication
implementation in which I have around 100 subscriber I need to decide
what conflict resolution policy should be employed.
We, in our implementation just want that publisher always wins in case
if there is any conflict. Is it enough to create a local subscription
to acheieve this.
Which will ensure that in case of any conflict with the publisher
publisher always wins the conflict.
Also we want that losing party should get log of inforamtion about the
lost record, is there any way to acheive this?
Can you define what you mean by a local subscription?
The best way to alert the user of conflicts is to use the com object
C:\Program Files\Common Files\Microsoft Shared\Database
Replication\WZCNF.DLL
This will display a list of the conflicts. You will then have to poll for
them and send out alerts.
Subscriptions managed by Windows Synchronization Manager using the
interactive resolved will allow the user to manage the conflict when WSM
runs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ruchir" <ruchirdhar@.gmail.com> wrote in message
news:88216eb7.0501172243.6e17e067@.posting.google.c om...
> Hello All,
> I am very much thankful to you all for providing much needed help, and
> once again I am at the group with my query. In my replication
> implementation in which I have around 100 subscriber I need to decide
> what conflict resolution policy should be employed.
> We, in our implementation just want that publisher always wins in case
> if there is any conflict. Is it enough to create a local subscription
> to acheieve this.
> Which will ensure that in case of any conflict with the publisher
> publisher always wins the conflict.
> Also we want that losing party should get log of inforamtion about the
> lost record, is there any way to acheive this?

Conflict Resolution: Is defining local subscription enough: How to get log of lost record.

Publisher always wins is the default, so you don't need
to do anything to achieve this.
To get notification of a conflict, you could create a
polling routine (there's no alert that I know of).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
hi Paul,
Thanks for you reply, I have few more queries
1. Where is this conflict information specially the losing row information is stored, at the publisher side or at the subscriber side? if losing row information is maintained at the publisher side is there any way so that we can store this information at the publisher side.
2. As you talked about creating a polling routine to get notification of conflict, in my implementation I just want a log file which contain information about the losing row nothing else, will the polling routine you are referring to can do the same and alsoI have not much information about how to write polling routine can you throw some light on it?
regards.
Ruchir.

Quote:

Originally posted by Paul Ibison
Publisher always wins is the default, so you don't need
to do anything to achieve this.
To get notification of a conflict, you could create a
polling routine (there's no alert that I know of).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

|||Have a look at the tables
conflict_<PublicationName>_<ArticleName>_usertable name
and
MSmerge_delete_conflicts
at the publisher.
The polling routine would be not much more than a job
that runs regularly and does a select from this table,
then sends the results in an email as an attachment. The
origin_datasource column could be used to determine the
correct recipient.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Configuring Text Log File Path in dtxConfig

Hi,

I am having trouble configuring the log file path in dtsconfig. I don't want to keep changing the log file path every time I change environments.

Here are the parameters.

<Configuration ConfiguredType="Property" Path="\Package.LogProviders[SSIS log provider for Text files].Properties[ConfigString]" ValueType="String">
<ConfiguredValue>SalesForceConnectLog</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.LogProviders[SSIS log provider for Text files].Properties[Name]" ValueType="String">
<ConfiguredValue>SSIS log provider for Text files</ConfiguredValue>
</Configuration>

I tried to enter the path on [ConfigString] but it didn't work.

Any Help is appreciated

Gulden

You don't update the log provider, you update the connection manager for that log provider.|||

You are absolutely right. Your email also reminded me why I had ruled that out. It was a problem with logging to the same file from two different packages. I went back and corrected that.

Thanks for your answer..

Configuring SQL to use AWE

I have configured a SQL200 instance to use 5gb of my server's memory.
At startup the log indicates that AWE is being used, and I see
that the memory is being grabbed when the instance starts.
But in the log indicates that the Working Set Size is set to 1 GB.
Does this mean anything when using AWE..
Am I getting full use of AWE on my instance. How can I tell....
Thanks
Paul
Check the Buffer Manager counter in Perfmon. There are a set of
events/classes related to AWE which you can check.
Anith

Configuring SQL to use AWE

I have configured a SQL200 instance to use 5gb of my server's memory.
At startup the log indicates that AWE is being used, and I see
that the memory is being grabbed when the instance starts.
But in the log indicates that the Working Set Size is set to 1 GB.
Does this mean anything when using AWE..
Am I getting full use of AWE on my instance. How can I tell....
Thanks
PaulCheck the Buffer Manager counter in Perfmon. There are a set of
events/classes related to AWE which you can check.
--
Anith

Configuring SQL to use AWE

I have configured a SQL200 instance to use 5gb of my server's memory.
At startup the log indicates that AWE is being used, and I see
that the memory is being grabbed when the instance starts.
But in the log indicates that the Working Set Size is set to 1 GB.
Does this mean anything when using AWE..
Am I getting full use of AWE on my instance. How can I tell....
Thanks
PaulCheck the Buffer Manager counter in Perfmon. There are a set of
events/classes related to AWE which you can check.
Anith

Configure SQL Server 2005 to allow remote connections!

Hello there,

I just deployed a new ASP.NET 2.0 website on a remote serverhttp://www.alrazem.com/. and when i want to log on, or when any connection to the database happens!! the following error ocurs.

Server Error in '/' Application.

---

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Anyways, I searched the new. and i found this link:http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277... I followed the instructions and changed theSurface Area Configuration for Services and Connectionsto Local and remote connections.and Applied both protocols. Then redeployed the application. but the error still ocurs.

I didn't restart my machine after changing the SQL to remote. but i did the website from scratch again. and created a new connectionstring and database!!!

Any help? Thanks!

Hi singlenipple,

It seems that the after enabling the remote connection, you still cannot connect to it.

In this case I suggest you try the following steps.

1. Restart the computer and make sure that your SQL Server service is started.
2. Check if there is any firewall app running on this machine that prevents your service from being accessed.
3. Create a .txt file on a remote client machine, rename it to .udl, and double click on it. You will see a connection property dialog then. You can use this wizard to test the connection.

Configure RS in sql express

Reporting Services Log

The Reporting Services installation options you specified in Setup determine whether further configuration is required before you can access the report server. If you installed the default configuration, the report server can be used immediately. If you installed just the program files, you must run the Reporting Services Configuration tool to deploy the report server.
I just installed sql express without sql developer or enterprise edition, I dont see Reporting Services on virtual directorys on IIS. I dont know how to acess the report server and configure it.

Tks

i have the same problem
i serch information about instalation RS on express edition and i find nothink, only info about futures
probably there are no RS in SQL Express setup, maby later...|||But Microsoft has a page where they say sql express has reporting services. Where is it?|||Yes. SQL Server Express has RS. However, not now. it will be available in the first half of the next year.

Alejandro Leguizamo
SQL Server MVP
Colombia|||Thank you Alejandro for answering to my email.

So the solution for this I think will be easy.

I will create my database on sql server express, I also will install sql server 2005 enterprise edition and make the reports in there, when the sql express with reporting services has been released the only thing I need to change will be the deployment options and thats it.

Correct me if I am wrong.|||

We have a client who requires RS 2005 (cause RS2004 has bugs), however is not ready to update to SQL 2005 (yet). The plan was to install Express with RS 2005, however it has been pointed out that RS is not packaged yet.

Is it possible to install Reporting Services off the developer edition cd onto a SQL Express instance? (Without using the developer licence in doing so).

Thanks

David

|||

No, this is not possible. A specific "package" which includes SQL Express and SSRS (Called SQL Server Express Edition with Advanced Services) will be available next year. This "Express SSRS" is the only version that will function against an Express database. I've had a customer or two try what you've suggested already, and it doesn't work.

I'm also not sure this idea would be "legal" (as you'd be putting SQL Developer into production which you may not do until the more recent MSDN SKUS), but that's above and beyond the technical part of the answer, anyway.

Configure RS in sql express

Reporting Services Log

The Reporting Services installation options you specified in Setup determine whether further configuration is required before you can access the report server. If you installed the default configuration, the report server can be used immediately. If you installed just the program files, you must run the Reporting Services Configuration tool to deploy the report server.
I just installed sql express without sql developer or enterprise edition, I dont see Reporting Services on virtual directorys on IIS. I dont know how to acess the report server and configure it.

Tks

i have the same problem
i serch information about instalation RS on express edition and i find nothink, only info about futures
probably there are no RS in SQL Express setup, maby later...
|||But Microsoft has a page where they say sql express has reporting services. Where is it?|||Yes. SQL Server Express has RS. However, not now. it will be available in the first half of the next year.

Alejandro Leguizamo
SQL Server MVP
Colombia|||Thank you Alejandro for answering to my email.

So the solution for this I think will be easy.

I will create my database on sql server express, I also will install sql server 2005 enterprise edition and make the reports in there, when the sql express with reporting services has been released the only thing I need to change will be the deployment options and thats it.

Correct me if I am wrong.|||

We have a client who requires RS 2005 (cause RS2004 has bugs), however is not ready to update to SQL 2005 (yet). The plan was to install Express with RS 2005, however it has been pointed out that RS is not packaged yet.

Is it possible to install Reporting Services off the developer edition cd onto a SQL Express instance? (Without using the developer licence in doing so).

Thanks

David

|||

No, this is not possible. A specific "package" which includes SQL Express and SSRS (Called SQL Server Express Edition with Advanced Services) will be available next year. This "Express SSRS" is the only version that will function against an Express database. I've had a customer or two try what you've suggested already, and it doesn't work.

I'm also not sure this idea would be "legal" (as you'd be putting SQL Developer into production which you may not do until the more recent MSDN SKUS), but that's above and beyond the technical part of the answer, anyway.