SQL compact edition: log

Showing posts with label log. Show all posts

Monday, March 26, 2012

Pointy Headed Boss

I have this dilbert kinda boss. He wants to know if there is a tool for
taking part of a transaction log and load testing the server from a remote
location. We are trying to get around some licensing issues by the use of
only one centralized database. I do not have access to the source for the
application so I can not optimize it for use over a wan. It makes no use of
the server everything is clientside. So to make a long story short the
pointy head boss wants to know if he can run tests here, take the trans log
to a remote destination and be able to run those transactions from the
remote site.
whew,
jc
Not the transaction log, but you can run a trace using profiler and =
then replay the trace at a remote site.
Mike John
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message =
news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> I have this dilbert kinda boss. He wants to know if there is a tool =
for=20
> taking part of a transaction log and load testing the server from a =
remote=20
> location. We are trying to get around some licensing issues by the use =
of=20
> only one centralized database. I do not have access to the source for =
the=20
> application so I can not optimize it for use over a wan. It makes no =
use of=20
> the server everything is clientside. So to make a long story short the =

> pointy head boss wants to know if he can run tests here, take the =
trans log=20
> to a remote destination and be able to run those transactions from the =

> remote site.
>=20
> whew,
> jc=20
>=20
>
|||Just adding to Mike's suggestion a little..
It's actually better to use profiler than any log analysis tool as the log
doesn't give you the selects - it only gives you the updates to the database
which is of course, only part of the load testing you're trying to perform.
Profiler gives you the selects as well, so it's necessarily a better
approach than analysing the log..
Regards,
Greg Linwood
SQL Server MVP
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:OTYeqYxJEHA.1096@.TK2MSFTNGP10.phx.gbl...
Not the transaction log, but you can run a trace using profiler and then
replay the trace at a remote site.
Mike John
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message
news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> I have this dilbert kinda boss. He wants to know if there is a tool for
> taking part of a transaction log and load testing the server from a remote
> location. We are trying to get around some licensing issues by the use of
> only one centralized database. I do not have access to the source for the
> application so I can not optimize it for use over a wan. It makes no use
of
> the server everything is clientside. So to make a long story short the
> pointy head boss wants to know if he can run tests here, take the trans
log
> to a remote destination and be able to run those transactions from the
> remote site.
> whew,
> jc
>
|||And just to add a tiny bit to that:
The log only contains the effect of the modification statements. Not the statements themselves. You can have a
DELETE which modifies 1000 rows. This will be logged as 1000 delete log records in the transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message news:uS6GxizJEHA.3276@.TK2MSFTNGP12.phx.gbl...
> Just adding to Mike's suggestion a little..
> It's actually better to use profiler than any log analysis tool as the log
> doesn't give you the selects - it only gives you the updates to the database
> which is of course, only part of the load testing you're trying to perform.
> Profiler gives you the selects as well, so it's necessarily a better
> approach than analysing the log..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
> news:OTYeqYxJEHA.1096@.TK2MSFTNGP10.phx.gbl...
> Not the transaction log, but you can run a trace using profiler and then
> replay the trace at a remote site.
> Mike John
> "John Cantley" <kayjohn59@.sbcglobal.net> wrote in message
> news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> of
> log
>

Pointy Headed Boss

I have this dilbert kinda boss. He wants to know if there is a tool for
taking part of a transaction log and load testing the server from a remote
location. We are trying to get around some licensing issues by the use of
only one centralized database. I do not have access to the source for the
application so I can not optimize it for use over a wan. It makes no use of
the server everything is clientside. So to make a long story short the
pointy head boss wants to know if he can run tests here, take the trans log
to a remote destination and be able to run those transactions from the
remote site.
whew,
jcNot the transaction log, but you can run a trace using profiler and =then replay the trace at a remote site.
Mike John
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message =news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> I have this dilbert kinda boss. He wants to know if there is a tool =for > taking part of a transaction log and load testing the server from a =remote > location. We are trying to get around some licensing issues by the use =of > only one centralized database. I do not have access to the source for =the > application so I can not optimize it for use over a wan. It makes no =use of > the server everything is clientside. So to make a long story short the =
> pointy head boss wants to know if he can run tests here, take the =trans log > to a remote destination and be able to run those transactions from the =
> remote site.
> > whew,
> jc > >|||Just adding to Mike's suggestion a little..
It's actually better to use profiler than any log analysis tool as the log
doesn't give you the selects - it only gives you the updates to the database
which is of course, only part of the load testing you're trying to perform.
Profiler gives you the selects as well, so it's necessarily a better
approach than analysing the log..
Regards,
Greg Linwood
SQL Server MVP
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:OTYeqYxJEHA.1096@.TK2MSFTNGP10.phx.gbl...
Not the transaction log, but you can run a trace using profiler and then
replay the trace at a remote site.
Mike John
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message
news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> I have this dilbert kinda boss. He wants to know if there is a tool for
> taking part of a transaction log and load testing the server from a remote
> location. We are trying to get around some licensing issues by the use of
> only one centralized database. I do not have access to the source for the
> application so I can not optimize it for use over a wan. It makes no use
of
> the server everything is clientside. So to make a long story short the
> pointy head boss wants to know if he can run tests here, take the trans
log
> to a remote destination and be able to run those transactions from the
> remote site.
> whew,
> jc
>|||And just to add a tiny bit to that:
The log only contains the effect of the modification statements. Not the statements themselves. You can have a
DELETE which modifies 1000 rows. This will be logged as 1000 delete log records in the transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message news:uS6GxizJEHA.3276@.TK2MSFTNGP12.phx.gbl...
> Just adding to Mike's suggestion a little..
> It's actually better to use profiler than any log analysis tool as the log
> doesn't give you the selects - it only gives you the updates to the database
> which is of course, only part of the load testing you're trying to perform.
> Profiler gives you the selects as well, so it's necessarily a better
> approach than analysing the log..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
> news:OTYeqYxJEHA.1096@.TK2MSFTNGP10.phx.gbl...
> Not the transaction log, but you can run a trace using profiler and then
> replay the trace at a remote site.
> Mike John
> "John Cantley" <kayjohn59@.sbcglobal.net> wrote in message
> news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> > I have this dilbert kinda boss. He wants to know if there is a tool for
> > taking part of a transaction log and load testing the server from a remote
> > location. We are trying to get around some licensing issues by the use of
> > only one centralized database. I do not have access to the source for the
> > application so I can not optimize it for use over a wan. It makes no use
> of
> > the server everything is clientside. So to make a long story short the
> > pointy head boss wants to know if he can run tests here, take the trans
> log
> > to a remote destination and be able to run those transactions from the
> > remote site.
> >
> > whew,
> > jc
> >
> >
>

Pointy Headed Boss

I have this dilbert kinda boss. He wants to know if there is a tool for
taking part of a transaction log and load testing the server from a remote
location. We are trying to get around some licensing issues by the use of
only one centralized database. I do not have access to the source for the
application so I can not optimize it for use over a wan. It makes no use of
the server everything is clientside. So to make a long story short the
pointy head boss wants to know if he can run tests here, take the trans log
to a remote destination and be able to run those transactions from the
remote site.
whew,
jcNot the transaction log, but you can run a trace using profiler and =
then replay the trace at a remote site.
Mike John
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message =
news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> I have this dilbert kinda boss. He wants to know if there is a tool =
for=20
> taking part of a transaction log and load testing the server from a =
remote=20
> location. We are trying to get around some licensing issues by the use =
of=20
> only one centralized database. I do not have access to the source for =
the=20
> application so I can not optimize it for use over a wan. It makes no =
use of=20
> the server everything is clientside. So to make a long story short the =

> pointy head boss wants to know if he can run tests here, take the =
trans log=20
> to a remote destination and be able to run those transactions from the =

> remote site.
>=20
> whew,
> jc=20
>=20
>|||Just adding to Mike's suggestion a little..
It's actually better to use profiler than any log analysis tool as the log
doesn't give you the selects - it only gives you the updates to the database
which is of course, only part of the load testing you're trying to perform.
Profiler gives you the selects as well, so it's necessarily a better
approach than analysing the log..
Regards,
Greg Linwood
SQL Server MVP
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:OTYeqYxJEHA.1096@.TK2MSFTNGP10.phx.gbl...
Not the transaction log, but you can run a trace using profiler and then
replay the trace at a remote site.
Mike John
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message
news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> I have this dilbert kinda boss. He wants to know if there is a tool for
> taking part of a transaction log and load testing the server from a remote
> location. We are trying to get around some licensing issues by the use of
> only one centralized database. I do not have access to the source for the
> application so I can not optimize it for use over a wan. It makes no use
of
> the server everything is clientside. So to make a long story short the
> pointy head boss wants to know if he can run tests here, take the trans
log
> to a remote destination and be able to run those transactions from the
> remote site.
> whew,
> jc
>|||And just to add a tiny bit to that:
The log only contains the effect of the modification statements. Not the sta
tements themselves. You can have a
DELETE which modifies 1000 rows. This will be logged as 1000 delete log reco
rds in the transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message news:uS6GxizJEHA.3276@.TK2MSFTNGP12.p
hx.gbl...
> Just adding to Mike's suggestion a little..
> It's actually better to use profiler than any log analysis tool as the log
> doesn't give you the selects - it only gives you the updates to the databa
se
> which is of course, only part of the load testing you're trying to perform
.
> Profiler gives you the selects as well, so it's necessarily a better
> approach than analysing the log..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
> news:OTYeqYxJEHA.1096@.TK2MSFTNGP10.phx.gbl...
> Not the transaction log, but you can run a trace using profiler and then
> replay the trace at a remote site.
> Mike John
> "John Cantley" <kayjohn59@.sbcglobal.net> wrote in message
> news:u1any%23wJEHA.1192@.TK2MSFTNGP11.phx.gbl...
> of
> log
>

Friday, March 23, 2012

Point-In-Time Restoration Issue

Hello,

I'm testing "Point In Time" restoration for my system using both Database & Log backup files. (Database backup once a day; Log backup every 4 hours)

When I use T-SQL to perfrom the restoration, I can specify one .BAK file with numerous .TRN files and restore to any 'point of time' with no issue.

However, if I use EM to perform the same restoration, I can only specify one .BAK file with a maximum of two .TRN files (although I can see all the .TRN files) in order to restore the database properly. If I specify more .TRN files, after restoration, my DB will be in 'LOADING' status and can't be used.

Does anyone encounter the same problem before and know what is going on?

Thank You.When restoring a database with Enterprise Manager, make sure the 'Leave Database Operational' is checked.|||you can also always switch the database from "Loading" to "operational" modes with simple tsql command

restore database <Dbname> with recovery

simas|||hi guys,

thanks for the replies. I'll look into it.

btw, I did found out some documentation that closely describe my problem, it's "Microsoft Knowledge Base Article - 319697, FIX: SQL Enterprise Manager Restore to Point in Time Does Not Stop at Requested Time and the Database is Left in a Loading State"

Seems like it's not something new...

Thank You.

Point-in-time recovery using Log file backups newer than full back

Hopefully I can ask this question w/o too much confusion. The example I am
about to give may not be practical, but the answer should help me understand
db and log backups a little better.
Scenario:
Full db backup performed 3 times in a day - morning, afternoon, and evening
(T1, T2, T3 respectively)
Log backup performed in morning (T1) and evening (T3) immediately after the
morning full db backups (no afternoon (T2) log backup)
Then server db drive fails after the evening db and log backups
The evening full backup is unrestorable (bad tape)
The evening log backup is good (stored on another device).
Could I restore the db to the point of failure using the afternoon full db
backup (T2) and the evening log backup (T3)?
db: T1--T2--T3
log: T1--T3
In other words, when restoring the evening log backup, against the afternoon
full db backup, would the restore process read through the evening log backup
to find the transactions begining at T2, or does the log being restored need
to be from a backup that occurs after the full backup was created?
My "guess" is that the restore would read through the T3 log and apply the
transactions that began after the T2 full db backup.If you ask whether you can "skip" a db backup, then the answer it yes. You cannot "skip" a log
backup, though. Perhaps easier with an examples. Say the time goes from top to bottom:
A Db
B Log
C Log
D Db
E Log
F Log
G Db
H Log
I Log
Here are a couple of examples of what you *can* do:
a, b, c, d, e, h, i
d, e, f, h, i
g, h, i
Here's a couple of examples of what you *cannot* do:
a, c, e, f, h, i
a, e, f, h, i
Short answer is that you need an unbroken chain of log backup. Db backups in between do not matter.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"GRakaska" <GRakaska@.discussions.microsoft.com> wrote in message
news:E20FF009-56C4-4B91-A31A-9720FB1404CD@.microsoft.com...
> Hopefully I can ask this question w/o too much confusion. The example I am
> about to give may not be practical, but the answer should help me understand
> db and log backups a little better.
> Scenario:
> Full db backup performed 3 times in a day - morning, afternoon, and evening
> (T1, T2, T3 respectively)
> Log backup performed in morning (T1) and evening (T3) immediately after the
> morning full db backups (no afternoon (T2) log backup)
> Then server db drive fails after the evening db and log backups
> The evening full backup is unrestorable (bad tape)
> The evening log backup is good (stored on another device).
> Could I restore the db to the point of failure using the afternoon full db
> backup (T2) and the evening log backup (T3)?
> db: T1--T2--T3
> log: T1--T3
> In other words, when restoring the evening log backup, against the afternoon
> full db backup, would the restore process read through the evening log backup
> to find the transactions begining at T2, or does the log being restored need
> to be from a backup that occurs after the full backup was created?
> My "guess" is that the restore would read through the T3 log and apply the
> transactions that began after the T2 full db backup.
>|||> A Db
> B Log
> C Log
> D Db
> E Log
> F Log
> G Db
> H Log
> I Log
> Here are a couple of examples of what you *can* do:
> a, b, c, d, e, h, i
> d, e, f, h, i
> g, h, i
I think an error has found it's way in the first row. You can't skip the F,
since that will break the log chain - and you also say that the chain can
not be broken :)
/Sjang|||> I think an error has found it's way in the first row.
Yes, thanks for catching that. It should have been:
a, b, c, e, f, h, i
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Henrik Davidsen" <none@.none.dk> wrote in message news:lBBDj.7440$9m.7234@.fe25.usenetserver.com...
>> A Db
>> B Log
>> C Log
>> D Db
>> E Log
>> F Log
>> G Db
>> H Log
>> I Log
>> Here are a couple of examples of what you *can* do:
>> a, b, c, d, e, h, i
>> d, e, f, h, i
>> g, h, i
>
> I think an error has found it's way in the first row. You can't skip the F,
> since that will break the log chain - and you also say that the chain can
> not be broken :)
> /Sjang
>
>

POINT IN TIME RESTORE

When I try to restore a database with query analyser and point in time resto
re, I always obtain this error message : The log in this backup set terminat
es at LSN 69508000000440000001, which is too early to apply to the database.
A more recent log backup t
hat includes LSN 69508000000440300001 can be restored.
My request is : RESTORE DATABASE TEST1 FROM BBA with norecovery
RESTORE LOG TEST1 FROM BBA
WITH recovery, STOPAT = '2004-03-07 8:30:00'
I have the same problem with all my databases and if I look in my device BBA
, I can see that I start with a full backup on 2004-03-07 and I continue wit
h logs backup until 2004-03-13.
What's wrong ?I forgot to mention that my full backup were at 5:00 AM on 2004-03-07|||Is there only 1 tlog created upto "8:30" and after?
If there are multiple tlogs you'll have to issue something like...
restore database dbname.bak from bba with norecovery
restore log logname1.tlog from bba with norecovery
restore log logname2.tlog from bba with recovery, stopat = '2004-03-07 8:30:
00'
Regards,
Craig.
Cris wrote:

> When I try to restore a database with query analyser and point in time restore, I
always obtain this error message : The log in this backup set terminates at LSN 6950
8000000440000001, which is too early to apply to the database. A more recent log bac
kup
that includes LSN 69508000000440300001 can be restored.
> My request is : RESTORE DATABASE TEST1 FROM BBA with norecovery
> RESTORE LOG TEST1 FROM BBA
> WITH recovery, STOPAT = '2004-03-07 8:30:00'
> I have the same problem with all my databases and if I look in my device B
BA, I can see that I start with a full backup on 2004-03-07 and I continue w
ith logs backup until 2004-03-13.
> What's wrong ?|||Hi,
To add on to craigs posting,
The POINT-IN-TIME recovery will work only if the database's Recovery moel is
set to "FULL" and the model is not
changed after theFULL database backup.
Steps:
1. Perform a transaction log backup of the original database (If you have
not performed one)
2. RESTORE DATABASE TEST1 FROM BBA (Give the correct backup file / device
name) WITH NORECOVERY
3. Restore the subsequent transaction log files in order of backup WITH
NORECOVERY option till the final transaction log file
4. In the the final transaction log restore mention WITH RECOVERY and
STOPAT='date and time'
Thanks
Hari
MCDBA
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:#T$bgUICEHA.2556@.TK2MSFTNGP12.phx.gbl...
> Is there only 1 tlog created upto "8:30" and after?
> If there are multiple tlogs you'll have to issue something like...
> restore database dbname.bak from bba with norecovery
> restore log logname1.tlog from bba with norecovery
> restore log logname2.tlog from bba with recovery, stopat = '2004-03-07
8:30:00'
> Regards,
> Craig.
> Cris wrote:
>
restore, I always obtain this error message : The log in this backup set
terminates at LSN 69508000000440000001, which is too early to apply to the
database. A more recent log backup that includes LSN 69508000000440300001
can be restored.
BBA, I can see that I start with a full backup on 2004-03-07 and I continue
with logs backup until 2004-03-13.|||Sorry to jump in, I have read the thread and it is close to what I
would like to accomplish but not quite.
I have a perfefctly good database with a .mdf and a .ldf file. The
recovery model is FULL. It was a new database a few weeks ago for
developing a new product.
We entered all kinds of info into it before we realized we were not
backing it up. Today we erased all the data...oops..
So I have a good .mdf and a full .ldf, I do a backup of both (wrong
thing)? And I try to restore using the cdoe I have seen here before..
RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
NORECOVERY;
RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
STOPAT = 'Mar 18, 2004 10:00 AM';
This runs without error but restores the database to its CURRENT state
which is empty of data!
If I run the same from Enterprise Manager and I check the "Point in
time" it will not allow me to select anything but today.
It seems that I should be able to do this since I have a full .ldf
file but the method is beyond my technical skills.
Any ideas?
Thanks
Tim|||You haven't provided us with exact information of what you have, and at
which point in time those backups (loosely speaking) were taken.

> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
OK, so what we need to know is, compared to this point in time (when you
erased all data), what file backups (ldf, mdf) and database backups (BACKUP
DATABASE) and transaction log backups (BACKUP LOG) you have. Again, put it
on a time line compared to the erase of the data.

> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)?
You generally don't do backup of the underlying database files in SQL
Server. Use BACKUP DATABASE and BACKUP LOG instead.
Then you continue to say that you use the RESTORE command, but you haven't
given us the information about when you did the BACKUP DATABASE and BACKUP
LOG.
You are probably toast, though. My guess is that you have some old file
level backup. And after the disaster, you did BACKUP DATABASE and BACKUP
LOG. You then put back those old database files, but want to come closer to
the point of disaster. So you do the RESTORE DATABASE. And we don't need to
go no further, because that put the database in the same state as it was
when the backup command was taken (which was after the point of disaster).
You really need to read the sections about backup and restore in Books
Online to understand how these things work in SQL Server. If you want more
help with this particular situation, please provide more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tim" <tsullivan@.websul.net> wrote in message
news:a1920b75.0403221607.6e054dc9@.posting.google.com...
> Sorry to jump in, I have read the thread and it is close to what I
> would like to accomplish but not quite.
> I have a perfefctly good database with a .mdf and a .ldf file. The
> recovery model is FULL. It was a new database a few weeks ago for
> developing a new product.
> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)? And I try to restore using the cdoe I have seen here before..
> RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
> NORECOVERY;
> RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
> STOPAT = 'Mar 18, 2004 10:00 AM';
> This runs without error but restores the database to its CURRENT state
> which is empty of data!
> If I run the same from Enterprise Manager and I check the "Point in
> time" it will not allow me to select anything but today.
> It seems that I should be able to do this since I have a full .ldf
> file but the method is beyond my technical skills.
>
> Any ideas?
> Thanks
> Tim|||Tibor,
Thanks for trying to help a dummy
You are right on many fronts
a) I need more knowledge in BOL
b) I didn't decribe clearly, due to "a"
Here is the timeline
1- create database
2- enter data
3- erase data
4 - enterprise manager backup database, backup transaction log
5- restore database with norecovery
6- restore log with recovery, stopat (date time before data erased)
I use osql to run the job, the job runs but all the data is still gone
If I run the same restore from enterprise manager I am unable to
select a date prior to the time of the 1st transaction log backup.
I guess that is the point, you have to have a database backup from
BEFORE the point in time you want to restore to...
I am toast eh?
Tim
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<#
6Mb5LLEEHA.3412@.TK2MSFTNGP10.phx.gbl>...
> You haven't provided us with exact information of what you have, and at
> which point in time those backups (loosely speaking) were taken.
>
> OK, so what we need to know is, compared to this point in time (when you
> erased all data), what file backups (ldf, mdf) and database backups (BACKU
P
> DATABASE) and transaction log backups (BACKUP LOG) you have. Again, put it
> on a time line compared to the erase of the data.
>
> You generally don't do backup of the underlying database files in SQL
> Server. Use BACKUP DATABASE and BACKUP LOG instead.
> Then you continue to say that you use the RESTORE command, but you haven't
> given us the information about when you did the BACKUP DATABASE and BACKUP
> LOG.
> You are probably toast, though. My guess is that you have some old file
> level backup. And after the disaster, you did BACKUP DATABASE and BACKUP
> LOG. You then put back those old database files, but want to come closer t
o
> the point of disaster. So you do the RESTORE DATABASE. And we don't need t
o
> go no further, because that put the database in the same state as it was
> when the backup command was taken (which was after the point of disaster).
> You really need to read the sections about backup and restore in Books
> Online to understand how these things work in SQL Server. If you want more
> help with this particular situation, please provide more information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tim" <tsullivan@.websul.net> wrote in message
> news:a1920b75.0403221607.6e054dc9@.posting.google.com...|||This is what you should have done in order to do the restore as you wish:

> 1- create database
> 2- enter data
BACKUP DATABASE
> 3- erase data
BACKUP LOG
> 5- restore database with norecovery
> 6- restore log with recovery, stopat (date time before data erased)
The BACKUP DATABAE can of course be at an earlier point in time, but not
later, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tim" <tsullivan@.websul.net> wrote in message
news:a1920b75.0403230651.23d72ffe@.posting.google.com...
> Tibor,
> Thanks for trying to help a dummy
> You are right on many fronts
> a) I need more knowledge in BOL
> b) I didn't decribe clearly, due to "a"
> Here is the timeline
> 1- create database
> 2- enter data
> 3- erase data
> 4 - enterprise manager backup database, backup transaction log
> 5- restore database with norecovery
> 6- restore log with recovery, stopat (date time before data erased)
> I use osql to run the job, the job runs but all the data is still gone
> If I run the same restore from enterprise manager I am unable to
> select a date prior to the time of the 1st transaction log backup.
> I guess that is the point, you have to have a database backup from
> BEFORE the point in time you want to restore to...
> I am toast eh?
> Tim
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message news:<#6Mb5LLEEHA.3412@.TK2MSFTNGP10.phx.gbl>...
(BACKUP
it
haven't
BACKUP
to
to
disaster).
more|||Try like this
[url]http://www.sql-server-performance.com/lockwoodtech_log_navigator_spotlight.asp[/ur
l]

> Sorry to jump in, I have read the thread and it is close to what I
> would like to accomplish but not quite.
> I have a perfefctly good database with a .mdf and a .ldf file. The
> recovery model is FULL. It was a new database a few weeks ago for
> developing a new product.
> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)? And I try to restore using the cdoe I have seen here before..
> RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
> NORECOVERY;
> RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
> STOPAT = 'Mar 18, 2004 10:00 AM';
> This runs without error but restores the database to its CURRENT state
> which is empty of data!
> If I run the same from Enterprise Manager and I check the "Point in
> time" it will not allow me to select anything but today.
> It seems that I should be able to do this since I have a full .ldf
> file but the method is beyond my technical skills.
>
> Any ideas?
> Thanks
> Timsql

Wednesday, March 21, 2012

Point in time recovery

Hi
My Backup policy is to take transaction log backups every 30 minutes...
what if my server crashes at 10 minutes before a Transaction log backup
takes place...
e.g. Full Backup 1:00 pm
Transaction Log Backup - 1:30 pm
Transaction Log Backup - 2:00 pm
Transaction Log Backup - 2:30 pm
Server Crashes at 2:50 pm ... can we recover the work done form 2:30
pm to 2:50 pm ...
Please help
ThanksNo, you would need an additional tlog backup done after 2:50 to do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> Hi
> My Backup policy is to take transaction log backups every 30 minutes...
> what if my server crashes at 10 minutes before a Transaction log backup
> takes place...
> e.g. Full Backup 1:00 pm
> Transaction Log Backup - 1:30 pm
> Transaction Log Backup - 2:00 pm
> Transaction Log Backup - 2:30 pm
> Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> pm to 2:50 pm ...
> Please help
> Thanks
>|||Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what the
purpose of this option is. Post back if you have further questions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> Hi
> My Backup policy is to take transaction log backups every 30 minutes...
> what if my server crashes at 10 minutes before a Transaction log backup
> takes place...
> e.g. Full Backup 1:00 pm
> Transaction Log Backup - 1:30 pm
> Transaction Log Backup - 2:00 pm
> Transaction Log Backup - 2:30 pm
> Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> pm to 2:50 pm ...
> Please help
> Thanks
>|||It backups the log file without truncating it ...
But when the server crashes... how can i use that log file... i mean I
wont be able to take a backup of that log file cause i would not have
access to the server...
Please can someone explain this
Thanks
Tibor Karaszi wrote:
> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what the
> purpose of this option is. Post back if you have further questions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> > Hi
> >
> > My Backup policy is to take transaction log backups every 30 minutes...
> > what if my server crashes at 10 minutes before a Transaction log backup
> > takes place...
> >
> > e.g. Full Backup 1:00 pm
> > Transaction Log Backup - 1:30 pm
> > Transaction Log Backup - 2:00 pm
> > Transaction Log Backup - 2:30 pm
> >
> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> > pm to 2:50 pm ...
> >
> > Please help
> >
> > Thanks
> >|||> But when the server crashes... how can i use that log file... i mean I
> wont be able to take a backup of that log file cause i would not have
> access to the server...
You need to consider a number of scenarios in developing your recovery plan.
Also consider your tolerance for data loss (SLAs) and the cost of preventing
such.
The worst case is that you lose your data center and can recover using only
off-site storage. You will likely lose some data in that scenario unless
you engineer a (very expensive) system to mirror all data real time to an
offsite location. Similarly, you will loose data if you lose your log file
for any reason; you can recover only to your last transaction log backup.
If you lose data file(s) but your log is intact, you'll need to first
recover to the point where SQL Server is running but the database is suspect
due to the data file problem. This may involve correcting hardware
problems, rebuilding the server OS and restoring SQL Server system
databases, depending on the particulars of the recovery scenario. You can
then use the BACKUP LOG...WITH NO_TRUNCATE to extract the last log you'll
need for the restore process.
In any case, you should schedule transaction log backups (stored on a
different server) that are frequent enough to meet your data loss SLAs.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
> It backups the log file without truncating it ...
> But when the server crashes... how can i use that log file... i mean I
> wont be able to take a backup of that log file cause i would not have
> access to the server...
> Please can someone explain this
> Thanks
>
>
> Tibor Karaszi wrote:
>> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command.
>> This is exactly what the
>> purpose of this option is. Post back if you have further questions.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Double_B" <bharatbutani@.gmail.com> wrote in message
>> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
>> > Hi
>> >
>> > My Backup policy is to take transaction log backups every 30 minutes...
>> > what if my server crashes at 10 minutes before a Transaction log backup
>> > takes place...
>> >
>> > e.g. Full Backup 1:00 pm
>> > Transaction Log Backup - 1:30 pm
>> > Transaction Log Backup - 2:00 pm
>> > Transaction Log Backup - 2:30 pm
>> >
>> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
>> > pm to 2:50 pm ...
>> >
>> > Please help
>> >
>> > Thanks
>> >
>|||In addition to Dan's comments:
If the server is really toast, but the log file is intact:
Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove the
database files. "Slide" in your log file from the crashed database. Start that SQL Server, and the
database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
backup the log of a crashed database even if the whole machine goes south, assuming you get to the
ldf file(s).
Another comment:
>> It backups the log file without truncating it ...
Don't let the name of the option fool you. The purpose of this option is exactly what we have
described here. The options is badly named, quite simply. This is why I suggested you to study the
literature about the meaning of this option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
>> But when the server crashes... how can i use that log file... i mean I
>> wont be able to take a backup of that log file cause i would not have
>> access to the server...
> You need to consider a number of scenarios in developing your recovery plan. Also consider your
> tolerance for data loss (SLAs) and the cost of preventing such.
> The worst case is that you lose your data center and can recover using only off-site storage. You
> will likely lose some data in that scenario unless you engineer a (very expensive) system to
> mirror all data real time to an offsite location. Similarly, you will loose data if you lose your
> log file for any reason; you can recover only to your last transaction log backup.
> If you lose data file(s) but your log is intact, you'll need to first recover to the point where
> SQL Server is running but the database is suspect due to the data file problem. This may involve
> correcting hardware problems, rebuilding the server OS and restoring SQL Server system databases,
> depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
> NO_TRUNCATE to extract the last log you'll need for the restore process.
> In any case, you should schedule transaction log backups (stored on a different server) that are
> frequent enough to meet your data loss SLAs.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
>> It backups the log file without truncating it ...
>> But when the server crashes... how can i use that log file... i mean I
>> wont be able to take a backup of that log file cause i would not have
>> access to the server...
>> Please can someone explain this
>> Thanks
>>
>>
>> Tibor Karaszi wrote:
>> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
>> the
>> purpose of this option is. Post back if you have further questions.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Double_B" <bharatbutani@.gmail.com> wrote in message
>> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
>> > Hi
>> >
>> > My Backup policy is to take transaction log backups every 30 minutes...
>> > what if my server crashes at 10 minutes before a Transaction log backup
>> > takes place...
>> >
>> > e.g. Full Backup 1:00 pm
>> > Transaction Log Backup - 1:30 pm
>> > Transaction Log Backup - 2:00 pm
>> > Transaction Log Backup - 2:30 pm
>> >
>> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
>> > pm to 2:50 pm ...
>> >
>> > Please help
>> >
>> > Thanks
>> >
>|||Thanks a ton for the help guys...
I just wanted the procedure , of how to move ahead if i had the
transaction log .. I mean as Tibor said ... I would create a new DB ...
then de-tach it & attach the old transcation log file & then take a
backup & then restore that after the full backup of the database ...
Is that that correct way .. or are there some more steps...
Tibor Karaszi wrote:
> In addition to Dan's comments:
> If the server is really toast, but the log file is intact:
> Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove the
> database files. "Slide" in your log file from the crashed database. Start that SQL Server, and the
> database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
> backup the log of a crashed database even if the whole machine goes south, assuming you get to the
> ldf file(s).
>
> Another comment:
> >> It backups the log file without truncating it ...
> Don't let the name of the option fool you. The purpose of this option is exactly what we have
> described here. The options is badly named, quite simply. This is why I suggested you to study the
> literature about the meaning of this option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
> >> But when the server crashes... how can i use that log file... i mean I
> >> wont be able to take a backup of that log file cause i would not have
> >> access to the server...
> >
> > You need to consider a number of scenarios in developing your recovery plan. Also consider your
> > tolerance for data loss (SLAs) and the cost of preventing such.
> >
> > The worst case is that you lose your data center and can recover using only off-site storage. You
> > will likely lose some data in that scenario unless you engineer a (very expensive) system to
> > mirror all data real time to an offsite location. Similarly, you will loose data if you lose your
> > log file for any reason; you can recover only to your last transaction log backup.
> >
> > If you lose data file(s) but your log is intact, you'll need to first recover to the point where
> > SQL Server is running but the database is suspect due to the data file problem. This may involve
> > correcting hardware problems, rebuilding the server OS and restoring SQL Server system databases,
> > depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
> > NO_TRUNCATE to extract the last log you'll need for the restore process.
> >
> > In any case, you should schedule transaction log backups (stored on a different server) that are
> > frequent enough to meet your data loss SLAs.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Double_B" <bharatbutani@.gmail.com> wrote in message
> > news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
> >> It backups the log file without truncating it ...
> >>
> >> But when the server crashes... how can i use that log file... i mean I
> >> wont be able to take a backup of that log file cause i would not have
> >> access to the server...
> >>
> >> Please can someone explain this
> >>
> >> Thanks
> >>
> >>
> >>
> >>
> >> Tibor Karaszi wrote:
> >> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
> >> the
> >> purpose of this option is. Post back if you have further questions.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> >> > Hi
> >> >
> >> > My Backup policy is to take transaction log backups every 30 minutes...
> >> > what if my server crashes at 10 minutes before a Transaction log backup
> >> > takes place...
> >> >
> >> > e.g. Full Backup 1:00 pm
> >> > Transaction Log Backup - 1:30 pm
> >> > Transaction Log Backup - 2:00 pm
> >> > Transaction Log Backup - 2:30 pm
> >> >
> >> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> >> > pm to 2:50 pm ...
> >> >
> >> > Please help
> >> >
> >> > Thanks
> >> >
> >>
> >
> >|||Those are the steps basically, but not detach/attach. Rather stop SQL Server, delete the files, copy
over the tlog file in the place of the old tlog file. I know there has been a KB article about this
particular subject, and I'd be surprised if it has been removed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1157040981.529433.265080@.i3g2000cwc.googlegroups.com...
> Thanks a ton for the help guys...
> I just wanted the procedure , of how to move ahead if i had the
> transaction log .. I mean as Tibor said ... I would create a new DB ...
> then de-tach it & attach the old transcation log file & then take a
> backup & then restore that after the full backup of the database ...
> Is that that correct way .. or are there some more steps...
>
>
> Tibor Karaszi wrote:
>> In addition to Dan's comments:
>> If the server is really toast, but the log file is intact:
>> Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove
>> the
>> database files. "Slide" in your log file from the crashed database. Start that SQL Server, and
>> the
>> database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
>> backup the log of a crashed database even if the whole machine goes south, assuming you get to
>> the
>> ldf file(s).
>>
>> Another comment:
>> >> It backups the log file without truncating it ...
>> Don't let the name of the option fool you. The purpose of this option is exactly what we have
>> described here. The options is badly named, quite simply. This is why I suggested you to study
>> the
>> literature about the meaning of this option.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
>> >> But when the server crashes... how can i use that log file... i mean I
>> >> wont be able to take a backup of that log file cause i would not have
>> >> access to the server...
>> >
>> > You need to consider a number of scenarios in developing your recovery plan. Also consider your
>> > tolerance for data loss (SLAs) and the cost of preventing such.
>> >
>> > The worst case is that you lose your data center and can recover using only off-site storage.
>> > You
>> > will likely lose some data in that scenario unless you engineer a (very expensive) system to
>> > mirror all data real time to an offsite location. Similarly, you will loose data if you lose
>> > your
>> > log file for any reason; you can recover only to your last transaction log backup.
>> >
>> > If you lose data file(s) but your log is intact, you'll need to first recover to the point
>> > where
>> > SQL Server is running but the database is suspect due to the data file problem. This may
>> > involve
>> > correcting hardware problems, rebuilding the server OS and restoring SQL Server system
>> > databases,
>> > depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
>> > NO_TRUNCATE to extract the last log you'll need for the restore process.
>> >
>> > In any case, you should schedule transaction log backups (stored on a different server) that
>> > are
>> > frequent enough to meet your data loss SLAs.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "Double_B" <bharatbutani@.gmail.com> wrote in message
>> > news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
>> >> It backups the log file without truncating it ...
>> >>
>> >> But when the server crashes... how can i use that log file... i mean I
>> >> wont be able to take a backup of that log file cause i would not have
>> >> access to the server...
>> >>
>> >> Please can someone explain this
>> >>
>> >> Thanks
>> >>
>> >>
>> >>
>> >>
>> >> Tibor Karaszi wrote:
>> >> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
>> >> the
>> >> purpose of this option is. Post back if you have further questions.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Double_B" <bharatbutani@.gmail.com> wrote in message
>> >> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
>> >> > Hi
>> >> >
>> >> > My Backup policy is to take transaction log backups every 30 minutes...
>> >> > what if my server crashes at 10 minutes before a Transaction log backup
>> >> > takes place...
>> >> >
>> >> > e.g. Full Backup 1:00 pm
>> >> > Transaction Log Backup - 1:30 pm
>> >> > Transaction Log Backup - 2:00 pm
>> >> > Transaction Log Backup - 2:30 pm
>> >> >
>> >> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
>> >> > pm to 2:50 pm ...
>> >> >
>> >> > Please help
>> >> >
>> >> > Thanks
>> >> >
>> >>
>> >
>> >
>|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:
> Those are the steps basically, but not detach/attach. Rather stop SQL Server, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has been a KB article about this
> particular subject, and I'd be surprised if it has been removed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1157040981.529433.265080@.i3g2000cwc.googlegroups.com...
> > Thanks a ton for the help guys...
> >
> > I just wanted the procedure , of how to move ahead if i had the
> > transaction log .. I mean as Tibor said ... I would create a new DB ...
> > then de-tach it & attach the old transcation log file & then take a
> > backup & then restore that after the full backup of the database ...
> >
> > Is that that correct way .. or are there some more steps...
> >
> >
> >
> >
> > Tibor Karaszi wrote:
> >> In addition to Dan's comments:
> >>
> >> If the server is really toast, but the log file is intact:
> >> Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove
> >> the
> >> database files. "Slide" in your log file from the crashed database. Start that SQL Server, and
> >> the
> >> database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
> >> backup the log of a crashed database even if the whole machine goes south, assuming you get to
> >> the
> >> ldf file(s).
> >>
> >>
> >> Another comment:
> >>
> >> >> It backups the log file without truncating it ...
> >>
> >> Don't let the name of the option fool you. The purpose of this option is exactly what we have
> >> described here. The options is badly named, quite simply. This is why I suggested you to study
> >> the
> >> literature about the meaning of this option.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> >> news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
> >> >> But when the server crashes... how can i use that log file... i mean I
> >> >> wont be able to take a backup of that log file cause i would not have
> >> >> access to the server...
> >> >
> >> > You need to consider a number of scenarios in developing your recovery plan. Also consider your
> >> > tolerance for data loss (SLAs) and the cost of preventing such.
> >> >
> >> > The worst case is that you lose your data center and can recover using only off-site storage.
> >> > You
> >> > will likely lose some data in that scenario unless you engineer a (very expensive) system to
> >> > mirror all data real time to an offsite location. Similarly, you will loose data if you lose
> >> > your
> >> > log file for any reason; you can recover only to your last transaction log backup.
> >> >
> >> > If you lose data file(s) but your log is intact, you'll need to first recover to the point
> >> > where
> >> > SQL Server is running but the database is suspect due to the data file problem. This may
> >> > involve
> >> > correcting hardware problems, rebuilding the server OS and restoring SQL Server system
> >> > databases,
> >> > depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
> >> > NO_TRUNCATE to extract the last log you'll need for the restore process.
> >> >
> >> > In any case, you should schedule transaction log backups (stored on a different server) that
> >> > are
> >> > frequent enough to meet your data loss SLAs.
> >> >
> >> > --
> >> > Hope this helps.
> >> >
> >> > Dan Guzman
> >> > SQL Server MVP
> >> >
> >> > "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> > news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
> >> >> It backups the log file without truncating it ...
> >> >>
> >> >> But when the server crashes... how can i use that log file... i mean I
> >> >> wont be able to take a backup of that log file cause i would not have
> >> >> access to the server...
> >> >>
> >> >> Please can someone explain this
> >> >>
> >> >> Thanks
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> Tibor Karaszi wrote:
> >> >> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
> >> >> the
> >> >> purpose of this option is. Post back if you have further questions.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> >> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> >> >> > Hi
> >> >> >
> >> >> > My Backup policy is to take transaction log backups every 30 minutes...
> >> >> > what if my server crashes at 10 minutes before a Transaction log backup
> >> >> > takes place...
> >> >> >
> >> >> > e.g. Full Backup 1:00 pm
> >> >> > Transaction Log Backup - 1:30 pm
> >> >> > Transaction Log Backup - 2:00 pm
> >> >> > Transaction Log Backup - 2:30 pm
> >> >> >
> >> >> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> >> >> > pm to 2:50 pm ...
> >> >> >
> >> >> > Please help
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >>
> >> >
> >> >
> >|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:
> Those are the steps basically, but not detach/attach. Rather stop SQL Server, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has been a KB article about this
> particular subject, and I'd be surprised if it has been removed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1157040981.529433.265080@.i3g2000cwc.googlegroups.com...
> > Thanks a ton for the help guys...
> >
> > I just wanted the procedure , of how to move ahead if i had the
> > transaction log .. I mean as Tibor said ... I would create a new DB ...
> > then de-tach it & attach the old transcation log file & then take a
> > backup & then restore that after the full backup of the database ...
> >
> > Is that that correct way .. or are there some more steps...
> >
> >
> >
> >
> > Tibor Karaszi wrote:
> >> In addition to Dan's comments:
> >>
> >> If the server is really toast, but the log file is intact:
> >> Create a database on some other machine with SQL Server installed. Stop that SQL Server. Remove
> >> the
> >> database files. "Slide" in your log file from the crashed database. Start that SQL Server, and
> >> the
> >> database is now, of course, suspect. Now do the log backup using NO_TRUNCATE. This way you can
> >> backup the log of a crashed database even if the whole machine goes south, assuming you get to
> >> the
> >> ldf file(s).
> >>
> >>
> >> Another comment:
> >>
> >> >> It backups the log file without truncating it ...
> >>
> >> Don't let the name of the option fool you. The purpose of this option is exactly what we have
> >> described here. The options is badly named, quite simply. This is why I suggested you to study
> >> the
> >> literature about the meaning of this option.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> >> news:e5OqgLPzGHA.4232@.TK2MSFTNGP04.phx.gbl...
> >> >> But when the server crashes... how can i use that log file... i mean I
> >> >> wont be able to take a backup of that log file cause i would not have
> >> >> access to the server...
> >> >
> >> > You need to consider a number of scenarios in developing your recovery plan. Also consider your
> >> > tolerance for data loss (SLAs) and the cost of preventing such.
> >> >
> >> > The worst case is that you lose your data center and can recover using only off-site storage.
> >> > You
> >> > will likely lose some data in that scenario unless you engineer a (very expensive) system to
> >> > mirror all data real time to an offsite location. Similarly, you will loose data if you lose
> >> > your
> >> > log file for any reason; you can recover only to your last transaction log backup.
> >> >
> >> > If you lose data file(s) but your log is intact, you'll need to first recover to the point
> >> > where
> >> > SQL Server is running but the database is suspect due to the data file problem. This may
> >> > involve
> >> > correcting hardware problems, rebuilding the server OS and restoring SQL Server system
> >> > databases,
> >> > depending on the particulars of the recovery scenario. You can then use the BACKUP LOG...WITH
> >> > NO_TRUNCATE to extract the last log you'll need for the restore process.
> >> >
> >> > In any case, you should schedule transaction log backups (stored on a different server) that
> >> > are
> >> > frequent enough to meet your data loss SLAs.
> >> >
> >> > --
> >> > Hope this helps.
> >> >
> >> > Dan Guzman
> >> > SQL Server MVP
> >> >
> >> > "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> > news:1157021648.378785.37920@.b28g2000cwb.googlegroups.com...
> >> >> It backups the log file without truncating it ...
> >> >>
> >> >> But when the server crashes... how can i use that log file... i mean I
> >> >> wont be able to take a backup of that log file cause i would not have
> >> >> access to the server...
> >> >>
> >> >> Please can someone explain this
> >> >>
> >> >> Thanks
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> Tibor Karaszi wrote:
> >> >> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. This is exactly what
> >> >> the
> >> >> purpose of this option is. Post back if you have further questions.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Double_B" <bharatbutani@.gmail.com> wrote in message
> >> >> news:1157015968.208121.268170@.m79g2000cwm.googlegroups.com...
> >> >> > Hi
> >> >> >
> >> >> > My Backup policy is to take transaction log backups every 30 minutes...
> >> >> > what if my server crashes at 10 minutes before a Transaction log backup
> >> >> > takes place...
> >> >> >
> >> >> > e.g. Full Backup 1:00 pm
> >> >> > Transaction Log Backup - 1:30 pm
> >> >> > Transaction Log Backup - 2:00 pm
> >> >> > Transaction Log Backup - 2:30 pm
> >> >> >
> >> >> > Server Crashes at 2:50 pm ... can we recover the work done form 2:30
> >> >> > pm to 2:50 pm ...
> >> >> >
> >> >> > Please help
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >>
> >> >
> >> >
> >

Point in time recovery

Point in Time Backup (impossible for some points?)

Hello,

I am using SQL Server 2000 with SP4. I have a database with two full
backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
PM. Is there a possible way to do a point in time restore to 4:30 PM,
that is between two full backups?

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM. Is the transaction log
truncated at each full backup? If so, even if you take transactional
log backup every ten minutes, and full backups every once in a while,
there will be some point in time which cannot be recovered to, namely
the time between a transactional log backup and a full backup. Take a
log backup at 4:50, and full backup at 5:00 and you can never recover
to 4:55, can you?

Any insight on the topic will be appreciated,

Regards,

M. Baris Caglarmcaglar@.cs.ucf.edu (mcaglar@.cs.ucf.edu) writes:

Quote:

Originally Posted by

Yes, restore the backup from 16:00 with NORECOVERY and then the
transaction log with the STOPAT option. Check the exact syntax in
Books Online.

This presumes that the log chain was never broken. That is the most
previous T-log backup of any kind must have been taken before 16:00.
SQL Server will tell you if this is the ase.

Quote:

Originally Posted by

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM.

Don't really know what you mean, but if you are using some GUI, I
don't really know what happens. I prefer to use T-SQL commands.

Quote:

Originally Posted by

Is the transaction log truncated at each full backup?

No. BACKUP DATABASE backs up the database, and all it does with the
log is to write a log record.

But if the database was taken as part of a job, that job may include a
backup of the transaction log as well. At worst, it includs a backup
with any of the options TRUNCATE_ONLY of NO_LOG which just throws
the logs away, without saving them anywhere.

There are tables in msdb where you can see at which points various sorts
of backups were taken. I don't use these tables very often myself, so
I can't give you an exact query to run.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I had find the exact same solution at a different thread in this group
and it worked, but thank you for your response. Interestingly,
Enterprise manager does not allow to perform such action. I wonder if
this was a bug or a design issue. Does anyone know if this peoblem is
fixed on SQL Server 2005?

Baris

Erland Sommarskog wrote:

Quote:

Originally Posted by

mcaglar@.cs.ucf.edu (mcaglar@.cs.ucf.edu) writes:

Quote:

Originally Posted by

>
Yes, restore the backup from 16:00 with NORECOVERY and then the
transaction log with the STOPAT option. Check the exact syntax in
Books Online.
>
This presumes that the log chain was never broken. That is the most
previous T-log backup of any kind must have been taken before 16:00.
SQL Server will tell you if this is the ase.
>

Quote:

Originally Posted by

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM.

>
Don't really know what you mean, but if you are using some GUI, I
don't really know what happens. I prefer to use T-SQL commands.
>

Quote:

Originally Posted by

Is the transaction log truncated at each full backup?

>
No. BACKUP DATABASE backs up the database, and all it does with the
log is to write a log record.
>
But if the database was taken as part of a job, that job may include a
backup of the transaction log as well. At worst, it includs a backup
with any of the options TRUNCATE_ONLY of NO_LOG which just throws
the logs away, without saving them anywhere.
>
There are tables in msdb where you can see at which points various sorts
of backups were taken. I don't use these tables very often myself, so
I can't give you an exact query to run.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||mcaglar@.cs.ucf.edu (mcaglar@.cs.ucf.edu) writes:

Quote:

Originally Posted by

I had find the exact same solution at a different thread in this group
and it worked, but thank you for your response. Interestingly,
Enterprise manager does not allow to perform such action. I wonder if
this was a bug or a design issue. Does anyone know if this peoblem is
fixed on SQL Server 2005?

Enterprise Manager is not included in SQL 2005, neither is Query Analyzer.
Both tools have been superceded by SQL Server Management Studio. Whether
the GUI dialogs in Mgmt Studio would make this operation available to you
I don't know. In any case, the GUI are just wrappers on the T-SQL commands,
and you can always use T-SQL when the GUI does not expose a certain piece
of functionality.

Tuesday, March 20, 2012

Pls recommend Raid 10 stripe size for SQL Server

Is there a rough rule of thumb for the stripe size to use for SQL Server
2000?
I am going to reorganise our disks. Assume 4 disk raid 10 for log and two 6
disk raid 10 sets for data.
Environment fairly mixed. Probably more reads than writes but still plenty
of updates etc.
Controllers have 128Mb battery backed up cache.
Thanks
Paul
Unless the controller manufacturer recommends otherwise specifically for SQL
server, go with their default stripe size. That is typically what the
hardware and firmware has been tuned to run. Any minor gains at the SQL
layer would likely be offset by a less than optimal setting at the
controller layer.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Is there a rough rule of thumb for the stripe size to use for SQL Server
> 2000?
> I am going to reorganise our disks. Assume 4 disk raid 10 for log and two
> 6 disk raid 10 sets for data.
> Environment fairly mixed. Probably more reads than writes but still plenty
> of updates etc.
> Controllers have 128Mb battery backed up cache.
> Thanks
> Paul
>
|||Thanks Geoff.
I guess I was just wondering give the more random nature of database calls
and sequential nature of log I/O.
Does Sql read/write in a particular batch/block size eg 8K.
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Unless the controller manufacturer recommends otherwise specifically for
> SQL server, go with their default stripe size. That is typically what the
> hardware and firmware has been tuned to run. Any minor gains at the SQL
> layer would likely be offset by a less than optimal setting at the
> controller layer.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
|||Data pages are 8K. Extents (allocation units and read-ahead increments) are
64K. SQL servers tend to do a log more reading than writing. Sequential
data reads will probably be in 64K increments while random reads will be in
8K blocks. Log writes tend to be small and sequential, hence the
recommendation to separate logs and data onto separate spindle sets as you
have done. As I recommended before, let the controller work at its best
which is almost always the default settings.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
> Thanks Geoff.
> I guess I was just wondering give the more random nature of database calls
> and sequential nature of log I/O.
> Does Sql read/write in a particular batch/block size eg 8K.
> Paul
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
|||It's a very rare opportunity to work on the system (24/7). Just wanted to do
all I could.
Thanks again
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:umcRsSbfFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Data pages are 8K. Extents (allocation units and read-ahead increments)
> are 64K. SQL servers tend to do a log more reading than writing.
> Sequential data reads will probably be in 64K increments while random
> reads will be in 8K blocks. Log writes tend to be small and sequential,
> hence the recommendation to separate logs and data onto separate spindle
> sets as you have done. As I recommended before, let the controller work
> at its best which is almost always the default settings.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
>

Pls recommend Raid 10 stripe size for SQL Server

Is there a rough rule of thumb for the stripe size to use for SQL Server
2000?
I am going to reorganise our disks. Assume 4 disk raid 10 for log and two 6
disk raid 10 sets for data.
Environment fairly mixed. Probably more reads than writes but still plenty
of updates etc.
Controllers have 128Mb battery backed up cache.
Thanks
PaulUnless the controller manufacturer recommends otherwise specifically for SQL
server, go with their default stripe size. That is typically what the
hardware and firmware has been tuned to run. Any minor gains at the SQL
layer would likely be offset by a less than optimal setting at the
controller layer.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Is there a rough rule of thumb for the stripe size to use for SQL Server
> 2000?
> I am going to reorganise our disks. Assume 4 disk raid 10 for log and two
> 6 disk raid 10 sets for data.
> Environment fairly mixed. Probably more reads than writes but still plenty
> of updates etc.
> Controllers have 128Mb battery backed up cache.
> Thanks
> Paul
>|||Thanks Geoff.
I guess I was just wondering give the more random nature of database calls
and sequential nature of log I/O.
Does Sql read/write in a particular batch/block size eg 8K.
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Unless the controller manufacturer recommends otherwise specifically for
> SQL server, go with their default stripe size. That is typically what the
> hardware and firmware has been tuned to run. Any minor gains at the SQL
> layer would likely be offset by a less than optimal setting at the
> controller layer.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>|||Data pages are 8K. Extents (allocation units and read-ahead increments) are
64K. SQL servers tend to do a log more reading than writing. Sequential
data reads will probably be in 64K increments while random reads will be in
8K blocks. Log writes tend to be small and sequential, hence the
recommendation to separate logs and data onto separate spindle sets as you
have done. As I recommended before, let the controller work at its best
which is almost always the default settings.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
> Thanks Geoff.
> I guess I was just wondering give the more random nature of database calls
> and sequential nature of log I/O.
> Does Sql read/write in a particular batch/block size eg 8K.
> Paul
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>|||It's a very rare opportunity to work on the system (24/7). Just wanted to do
all I could.
Thanks again
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:umcRsSbfFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Data pages are 8K. Extents (allocation units and read-ahead increments)
> are 64K. SQL servers tend to do a log more reading than writing.
> Sequential data reads will probably be in 64K increments while random
> reads will be in 8K blocks. Log writes tend to be small and sequential,
> hence the recommendation to separate logs and data onto separate spindle
> sets as you have done. As I recommended before, let the controller work
> at its best which is almost always the default settings.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
>

Monday, March 26, 2012

Pointy Headed Boss

Pointy Headed Boss

Pointy Headed Boss

Friday, March 23, 2012

Point-In-Time Restoration Issue

Point-in-time recovery using Log file backups newer than full back

POINT IN TIME RESTORE

Wednesday, March 21, 2012

Point in time recovery

Point in time recovery

Point in Time Backup (impossible for some points?)

Tuesday, March 20, 2012

Pls recommend Raid 10 stripe size for SQL Server

Pls recommend Raid 10 stripe size for SQL Server

SQL compact edition

Blog Archive

About Me