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
>
Showing posts with label transaction. Show all posts
Showing posts with label transaction. 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,
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
> >
> >
>
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
>
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
>
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
> >> >> >
> >> >>
> >> >
> >> >
> >
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
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. Thi
s 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:[vbcol=seagreen]
> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. T
his 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...|||> 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:
>|||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 th
at 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, a
ssuming you get to the
ldf file(s).
Another comment:
Don't let the name of the option fool you. The purpose of this option is exa
ctly what we have
described here. The options is badly named, quite simply. This is why I sugg
ested 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...[vbcol=seagreen]
> You need to consider a number of scenarios in developing your recovery pla
n. 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 onl
y off-site storage. You
> will likely lose some data in that scenario unless you engineer a (very ex
pensive) system to
> mirror all data real time to an offsite location. Similarly, you will loo
se 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 reco
ver to the point where
> SQL Server is running but the database is suspect due to the data file pro
blem. This may involve
> correcting hardware problems, rebuilding the server OS and restoring SQL S
erver system databases,
> depending on the particulars of the recovery scenario. You can then use t
he 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 diff
erent 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...
>|||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:[vbcol=seagreen]
> 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 th
at 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_TRUNCA
TE. 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:
>
> Don't let the name of the option fool you. The purpose of this option is e
xactly what we have
> described here. The options is badly named, quite simply. This is why I su
ggested 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...|||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:
>|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:[vbcol=seagreen]
> Those are the steps basically, but not detach/attach. Rather stop SQL Serv
er, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has bee
n 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...|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:[vbcol=seagreen]
> Those are the steps basically, but not detach/attach. Rather stop SQL Serv
er, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has bee
n 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...
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. Thi
s 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:[vbcol=seagreen]
> Start by reading about the NO_TRUNCATE option of the BACKUP LOG command. T
his 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...|||> 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:
>|||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 th
at 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, a
ssuming you get to the
ldf file(s).
Another comment:
Don't let the name of the option fool you. The purpose of this option is exa
ctly what we have
described here. The options is badly named, quite simply. This is why I sugg
ested 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...[vbcol=seagreen]
> You need to consider a number of scenarios in developing your recovery pla
n. 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 onl
y off-site storage. You
> will likely lose some data in that scenario unless you engineer a (very ex
pensive) system to
> mirror all data real time to an offsite location. Similarly, you will loo
se 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 reco
ver to the point where
> SQL Server is running but the database is suspect due to the data file pro
blem. This may involve
> correcting hardware problems, rebuilding the server OS and restoring SQL S
erver system databases,
> depending on the particulars of the recovery scenario. You can then use t
he 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 diff
erent 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...
>|||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:[vbcol=seagreen]
> 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 th
at 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_TRUNCA
TE. 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:
>
> Don't let the name of the option fool you. The purpose of this option is e
xactly what we have
> described here. The options is badly named, quite simply. This is why I su
ggested 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...|||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:
>|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:[vbcol=seagreen]
> Those are the steps basically, but not detach/attach. Rather stop SQL Serv
er, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has bee
n 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...|||It would be really helpful if i could get the link to the article
Thanks a ton
Regards
Tibor Karaszi wrote:[vbcol=seagreen]
> Those are the steps basically, but not detach/attach. Rather stop SQL Serv
er, delete the files, copy
> over the tlog file in the place of the old tlog file. I know there has bee
n 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...
Monday, March 12, 2012
Pls help on data design
I am tring to build a large relational database as source for OLAP and it is running ok.
It is a financial transaction database accumulated with data of (1) Actuals in Jan 2005, Feb 05... to Mar 06
(2) Budgeted numbers in Jan 2005, Feb 05... to Mar 06
An example is like this:-
Table A - actuals
Date/Product/Sales value
Table B - Budget
Date/Product/Sales value
However, one last trouble is regarding the time dimension,.
What is the best way to assign the time value to each transaction so that I can:-
(1) Compare aggregated Jan 06 with Jan 05 easily ?
(2) Compare aggregated Feb 06 with Jan 06 easily ?
(3) Compare actual vs budget easily ?
For example, if date is 23 Jan 2006, should I make 2 more columns, month & year, ie assign values 1 and 2006 ?
Also, should I create 2 columns of value fields, "Actual" and "budget", or should I create one single data value, but added one more domension, "status" for example and asiign "Actual" or "budget" to each transaction ?
Help...
Assuming that you're using AS 2005, you can get some ideas by looking at the Adventure Works cube - Financial Reporting measure group (just 1 measure: Amount) and Scenario dimension (3 members: Actual, Budget and Forecast). Defining Time Intelligence permits various time-based comparisons, such as the ones you mentioned:
http://msdn2.microsoft.com/en-us/library/ms175440(SQL.90).aspx
>>
Defining Time Intelligence (SSAS)
The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:
Period to date.Period over period growth.
Moving averages.
Parallel period comparisons.
Subscribe to:
Posts (Atom)