Back Up question

I currently have my database backed up to the same physical drive where the data resides. In the event of drive failure, I would lose everything. Due to space considerations on a tape, I was wondering if I can copy only the database backup file (.BAK) and not the transaction log backup (.TRN). Can I go ahead with this approach if I do not want point-in-time recovery and I am satisfied with only what resides on .BAK file? Will I able to restore the database from the tape if the tape contains only the (.BAK) file ?

Thanks.Sure...

How often do you dump the transaction log?

But I don't think it's advisable...

How big a database are we talking about...

Ever consider compression?|||I often dump my backups across the network to a share on another server. It won't work with very large databases very well, but I've had pretty good luck so far. I find that writing backups to tape can be extremely slow.

As for not keeping the transaction logs, I have to agree with Brett; I would prefer to keep mine.

Regards,

hmscott|||Do you dump across a network, or locally the copy?

I've always thought a dump over a network could corrupt the dump

Never seen it...just always believed it could happen...|||I had all kinds of problems with it in 6.5 and 7.0. I've never seen any problems with it in 2000 though. We do it all the time. All of our servers are dumped to a central repository. We even do restores from there, including log shipping restores. I've never had any corruption doing it this way. Of course, we do have a dual 16gb core with fiber to all servers. We're upgrading to dual 64gb. A lot has changed over the years I guess.

I would definitely consider compression if I were that strapped on space and bandwidth. Many times SQL Backup files will shrink to 70-80 percent of the original size.

back up failure error

the problem is
source:kernel
backup medium ::reportloerror:write failure on backup device\\.\tape0.operating system error 23(data error(cyclic reduncancy check));Delete your backup device and start from scratch.

Avoid Package Failure

Hello everyone

i'm using a Foreach Container to run certain tasks, these ones are inside a Sequence Container. I'd like to know if there is a way to have a task failure without a foreach failure, i don't need the hole process to fail, just the current iteration so i can log it.

Thanks a lot!

You may want to check FailParentOnFailure and FailPackageOnFailure properties of your inner tasks. I have not played with those but it seems like prove what you need.

Rafael Salas

|||Use an event handler.

Set the [System::Propagate] to false in an OnError handler in the inner tasks. That way, errors won't bubble up to the Foreach loop and cause it to fail.|||Thanks a lot

I used set maximum error count to a number fairly reachable, but i think it's not a good practice. I'll try your suggestions right now.

regards.|||Hello again,

I'm having the same problem, I did what you told me, on the OnError event handler I use a script where i change the Propagate variable to False. But there's a problem with the MaximumErrorCount property. Watching on the Execution Results tab, i figured out that the package fails because of the MaximumErrorCount property overpassed the foreach's number.

Well i have to say that inside the foreach Container there is a Sequence Container, and inside this one, there are 12 tasks, and i don't know if it could be stopping the package.

any suggestion?

regards.|||

I was referring to setting the OnError event handler on the failing inner tasks, however, I believe it would work just as well as on the sequence container, as long as its configured as depected.

└───ForEach
├───Sequence (<= OnError event handler here )
├───Task 1
├───Task N

On which tasks or containers is the OnError Event Handler defined?

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.Be
It really depends on the perfomance of your hardware. There is no set rule that I know of for time. It also depends on what needs to be recovered and what was in the log at the time of the crash. The more of the work in the log the longer the time. It could take anywhere from 1 minute to 45 minutes depending on how much work it has to do
Sorry it is so vagu
Jef
MCDBA, MCSE+I|||The recovery interval setting can impact the speed at which the =databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =if it could happen or not.
In my experience the databases are available shortly after the machine =comes online. I have experienced a few hardware related machine crashes =that have caused a server to black screen (become totally unresponsive). = We had to press the power button to turn the machine off and back on =again. Upon restarting the database was available almost right away. =This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =that the machine became unplugged from the UPS. Upon power-up SQL =Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =backup and restore databases. Books Online has lots of good information =in this area.
-- Keith
"ben brugman" <ben@.niethier.nl> wrote in message =news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> > If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> > What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> > How long will it take SQL-server to 'recover', from
> a crash ?
> > (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> > On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> > thanks for your attention,
> ben brugman.
> >|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.
Ben
It really depends on the perfomance of your hardware. There is no set rule that I know of for time. It also depends on what needs to be recovered and what was in the log at the time of the crash. The more of the work in the log the longer the time. It
could take anywhere from 1 minute to 45 minutes depending on how much work it has to do.
Sorry it is so vague
Jeff
MCDBA, MCSE+I
|||The recovery interval setting can impact the speed at which the =
databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =
Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =
if it could happen or not. =20
In my experience the databases are available shortly after the machine =
comes online. I have experienced a few hardware related machine crashes =
that have caused a server to black screen (become totally unresponsive). =
We had to press the power button to turn the machine off and back on =
again. Upon restarting the database was available almost right away. =
This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =
that the machine became unplugged from the UPS. Upon power-up SQL =
Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =
backup and restore databases. Books Online has lots of good information =
in this area.
--=20
Keith
"ben brugman" <ben@.niethier.nl> wrote in message =
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
>=20
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
>=20
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
>=20
> How long will it take SQL-server to 'recover', from
> a crash ?
>=20
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
>=20
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
>=20
> thanks for your attention,
> ben brugman.
>=20
>
|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.Ben
It really depends on the perfomance of your hardware. There is no set rule
that I know of for time. It also depends on what needs to be recovered and
what was in the log at the time of the crash. The more of the work in the l
og the longer the time. It
could take anywhere from 1 minute to 45 minutes depending on how much work i
t has to do.
Sorry it is so vague
Jeff
MCDBA, MCSE+I|||The recovery interval setting can impact the speed at which the =
databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =
Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =
if it could happen or not. =20
In my experience the databases are available shortly after the machine =
comes online. I have experienced a few hardware related machine crashes =
that have caused a server to black screen (become totally unresponsive). =
We had to press the power button to turn the machine off and back on =
again. Upon restarting the database was available almost right away. =
This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =
that the machine became unplugged from the UPS. Upon power-up SQL =
Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =
backup and restore databases. Books Online has lots of good information =
in this area.
--=20
Keith
"ben brugman" <ben@.niethier.nl> wrote in message =
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
>=20
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
>=20
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
>=20
> How long will it take SQL-server to 'recover', from
> a crash ?
>=20
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
>=20
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
>=20
> thanks for your attention,
> ben brugman.
>=20
>|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>