Full Restore Question

Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?
EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegr oups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>
|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?
|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegr oups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>
|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages
|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegrou ps.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>
|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!

Full Restore Question

Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and
sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and
use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm
:ss'.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time res
tore, you don't specify
the date and time, and you will not see that problem. But why not just execu
te the RESTORE command
from Query Analyzer instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also,
as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writi
ng and executing TSQL
makes us handle those situations quickly and solve the problem with minimal
impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.co
m.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!

Full Restore Question

Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!

full drives full trans log version 7

What does one do?
I tried "backup log mb with no_log" to truncate the file. I've now
started a shrinkdatabase. I can't backup since the drive is full. I
can't disconnect because it's 7.
How does one actually reduce the log file size?
thx
md
PS isn't simple logging supposed to avoid this problem?
PSS is it possible to set a DTS to not log?
*** Sent via Developersdex http://www.codecomments.com ***See if this helps:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/EN-US/
AMB
"M D" wrote:

> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Hi,
Since you dont require the transaction log backup you could truncate the log
first
BACKUP LOG DBNAME with TRUNCATE_ONLY
After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
If the usage is very low than SHRINK the LDF file using DBCC
SHRINKFILE('logical ldf name',size)
Please refer the link Alejandro pointed out.
Thanks
Hari
SQL Server MVP
"M D" <mardukes@.aol.com> wrote in message
news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.codecomments.com ***|||I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:

> Hi,
> Since you dont require the transaction log backup you could truncate the l
og
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
>|||Also run a DBCC OPENTRAN agains the database in question to make sure there
are no pending transactions to be completed.
The trasaction log architecture is different in SS7.0 versus SS2K and can be
quite difficult to shrink, even when using BACKUP LOG ... WITH NO_LOG or
WITH TRUNCATE_ONLY, which are synonomous by the way.
Usually, you will have to kill all processes but your own within the
database you are concerned with.
Execute sp_detach_db.
Delete the Transaction log file(s) for the database in question.
Execute sp_attach_single_file_db.
The new transaction log will be the size of the transaction log for the
model database.
Also know that regardless of Recovery Model, which didn't exist in
SS7.0-there were database options: 'trunc. log on chkpt.' and/or 'select
into/bulk copy'-EVERY TRANSACTION IS WRITTEN TO THE TRANSACTION LOG. There
is no way to stop this. The recovery settings just tell SQL Server what to
do with "COMPLETED" transactions within the transaction log file if they
have not been backed up yet.
If you are using 'trunc. log on chkpt.', then completed transactions are
automatically purged on the next CHECKPOINT process. This allows you to
write over that freed up space with the next transaction. However, your
transaction log will grow to support the largest single active transaction
or concurrent set of transactions.
If transactions are open, not completed or rolled back, then new
transactions can not overwrite those regardless of the recovery settings, or
db options, as the case may be.
Sincerely,
Anthony Thomas
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:8E5D6182-C9B9-4999-BBD7-5074AA5BF78A@.microsoft.com...
I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:

> Hi,
> Since you dont require the transaction log backup you could truncate the
log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
>

full drives full trans log version 7

What does one do?
I tried "backup log mb with no_log" to truncate the file. I've now
started a shrinkdatabase. I can't backup since the drive is full. I
can't disconnect because it's 7.
How does one actually reduce the log file size?
thx
md
PS isn't simple logging supposed to avoid this problem?
PSS is it possible to set a DTS to not log?
*** Sent via Developersdex http://www.developersdex.com ***See if this helps:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/EN-US/
AMB
"M D" wrote:
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Hi,
Since you dont require the transaction log backup you could truncate the log
first
BACKUP LOG DBNAME with TRUNCATE_ONLY
After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
If the usage is very low than SHRINK the LDF file using DBCC
SHRINKFILE('logical ldf name',size)
Please refer the link Alejandro pointed out.
Thanks
Hari
SQL Server MVP
"M D" <mardukes@.aol.com> wrote in message
news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.developersdex.com ***|||I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:
> Hi,
> Since you dont require the transaction log backup you could truncate the log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> > What does one do?
> >
> > I tried "backup log mb with no_log" to truncate the file. I've now
> > started a shrinkdatabase. I can't backup since the drive is full. I
> > can't disconnect because it's 7.
> >
> > How does one actually reduce the log file size?
> >
> > thx
> > md
> >
> > PS isn't simple logging supposed to avoid this problem?
> > PSS is it possible to set a DTS to not log?
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>|||Also run a DBCC OPENTRAN agains the database in question to make sure there
are no pending transactions to be completed.
The trasaction log architecture is different in SS7.0 versus SS2K and can be
quite difficult to shrink, even when using BACKUP LOG ... WITH NO_LOG or
WITH TRUNCATE_ONLY, which are synonomous by the way.
Usually, you will have to kill all processes but your own within the
database you are concerned with.
Execute sp_detach_db.
Delete the Transaction log file(s) for the database in question.
Execute sp_attach_single_file_db.
The new transaction log will be the size of the transaction log for the
model database.
Also know that regardless of Recovery Model, which didn't exist in
SS7.0-there were database options: 'trunc. log on chkpt.' and/or 'select
into/bulk copy'-EVERY TRANSACTION IS WRITTEN TO THE TRANSACTION LOG. There
is no way to stop this. The recovery settings just tell SQL Server what to
do with "COMPLETED" transactions within the transaction log file if they
have not been backed up yet.
If you are using 'trunc. log on chkpt.', then completed transactions are
automatically purged on the next CHECKPOINT process. This allows you to
write over that freed up space with the next transaction. However, your
transaction log will grow to support the largest single active transaction
or concurrent set of transactions.
If transactions are open, not completed or rolled back, then new
transactions can not overwrite those regardless of the recovery settings, or
db options, as the case may be.
Sincerely,
Anthony Thomas
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:8E5D6182-C9B9-4999-BBD7-5074AA5BF78A@.microsoft.com...
I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:
> Hi,
> Since you dont require the transaction log backup you could truncate the
log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> > What does one do?
> >
> > I tried "backup log mb with no_log" to truncate the file. I've now
> > started a shrinkdatabase. I can't backup since the drive is full. I
> > can't disconnect because it's 7.
> >
> > How does one actually reduce the log file size?
> >
> > thx
> > md
> >
> > PS isn't simple logging supposed to avoid this problem?
> > PSS is it possible to set a DTS to not log?
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>