interface supported: log

Showing posts with label log. Show all posts

Friday, March 30, 2012

NOSKIP & SKIP make no difference to the amount of time backup take

Hi
I run a full backup once a month and a transaction log backup every 10 mins
through working hours for the remainder of the month until the backup is
re-initialised at th start of the following month.
I backup to virtual sql disk object devices.
As the month progresses the backup takes longer and longer whilst the amount
of data being backed up every 10 minutes cycle is always roughly the same.
its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
month.
I noticed that NOSKIP was being used in the command - i changed this to
SKIP, but it made no difference in the amount of time the backup takes. Isnt
this suppoed to stop some sort of integrity scan on all other backup sets in
the archive?
full syntax of the backup command that runs now is:
BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
but as I say, the SKIP command instead of NOSKIP makes no difference.
whats going on here?
any help appreciated.
Thanks
Alastair.On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
wrote:
> Hi
> I run a full backup once a month and a transaction log backup every 10 mins
> through working hours for the remainder of the month until the backup is
> re-initialised at th start of the following month.
> I backup to virtual sql disk object devices.
> As the month progresses the backup takes longer and longer whilst the amount
> of data being backed up every 10 minutes cycle is always roughly the same.
> its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> month.
> I noticed that NOSKIP was being used in the command - i changed this to
> SKIP, but it made no difference in the amount of time the backup takes. Isnt
> this suppoed to stop some sort of integrity scan on all other backup sets in
> the archive?
> full syntax of the backup command that runs now is:
> BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> but as I say, the SKIP command instead of NOSKIP makes no difference.
> whats going on here?
> any help appreciated.
> Thanks
> Alastair.
Noskip/skip does not make any difference on Backup time. It just
checks expiration date of backup sets. I just guess that the reason
may be that your disk gets fragmented after number of backups and that
may cause delay.|||yes but if theres a LOT of backup sets surely this will add a delay?
"amish" wrote:
> On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
> wrote:
> > Hi
> >
> > I run a full backup once a month and a transaction log backup every 10 mins
> > through working hours for the remainder of the month until the backup is
> > re-initialised at th start of the following month.
> >
> > I backup to virtual sql disk object devices.
> >
> > As the month progresses the backup takes longer and longer whilst the amount
> > of data being backed up every 10 minutes cycle is always roughly the same.
> > its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > month.
> >
> > I noticed that NOSKIP was being used in the command - i changed this to
> > SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > this suppoed to stop some sort of integrity scan on all other backup sets in
> > the archive?
> >
> > full syntax of the backup command that runs now is:
> >
> > BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> > but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> > whats going on here?
> >
> > any help appreciated.
> >
> > Thanks
> >
> > Alastair.
> Noskip/skip does not make any difference on Backup time. It just
> checks expiration date of backup sets. I just guess that the reason
> may be that your disk gets fragmented after number of backups and that
> may cause delay.
>|||Looking at the BACKUP commands you posted, which includes REWIND and
NOFORMAT, it looks like the backup goes to tape. When you referred to
"virtual sql disk object devices" I was not expecting that. Tapes are
not virtual disks, tapes are tapes.
I have seen that behavior when backing up directly to tape. The
problem in that case was reading the tape from the start to the point
where the prior backup ended. Lets consider that by the end of the
month there have been around 1000 log backups assuming a 5 day work
week and an 8 hour work day.
I suggest changing the REWIND option to NOREWIND. From the
documentation: "NOREWIND -- Specifies that SQL Server will keep the
tape open after the backup operation. You can use this option to help
improve performance when performing multiple backup operations to a
tape."
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>Hi
>I run a full backup once a month and a transaction log backup every 10 mins
>through working hours for the remainder of the month until the backup is
>re-initialised at th start of the following month.
>I backup to virtual sql disk object devices.
>As the month progresses the backup takes longer and longer whilst the amount
>of data being backed up every 10 minutes cycle is always roughly the same.
>its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>month.
>I noticed that NOSKIP was being used in the command - i changed this to
>SKIP, but it made no difference in the amount of time the backup takes. Isnt
>this suppoed to stop some sort of integrity scan on all other backup sets in
>the archive?
>full syntax of the backup command that runs now is:
>BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>but as I say, the SKIP command instead of NOSKIP makes no difference.
>whats going on here?
>any help appreciated.
>Thanks
>Alastair.|||thanks for your response Roy.
if you go to server objects >backup devices you can configure a virtual
'tape' that is in effect a file on the physical disk. this is what im backing
up to. as far as I can tell the only differnece between using this method and
backing up direct to disk is that you get to use a shorter name in the backup
job command rather than a full file path.
anyhoo
may be this ISNT the only differnce. REWIND is default right? so maybe SQL
is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
what happens in 2 backups time (20 mins.)
cheers for the pointer
"Roy Harvey" wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> <Methodology@.discussions.microsoft.com> wrote:
> >Hi
> >
> >I run a full backup once a month and a transaction log backup every 10 mins
> >through working hours for the remainder of the month until the backup is
> >re-initialised at th start of the following month.
> >
> >I backup to virtual sql disk object devices.
> >
> >As the month progresses the backup takes longer and longer whilst the amount
> >of data being backed up every 10 minutes cycle is always roughly the same.
> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> >month.
> >
> >I noticed that NOSKIP was being used in the command - i changed this to
> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> >this suppoed to stop some sort of integrity scan on all other backup sets in
> >the archive?
> >
> >full syntax of the backup command that runs now is:
> >
> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> >whats going on here?
> >
> >any help appreciated.
> >
> >Thanks
> >
> >Alastair.
>|||On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>
> <Methodol...@.discussions.microsoft.com> wrote:
> >Hi
> >I run a full backup once a month and a transaction log backup every 10 mins
> >through working hours for the remainder of the month until the backup is
> >re-initialised at th start of the following month.
> >I backup to virtual sql disk object devices.
> >As the month progresses the backup takes longer and longer whilst the amount
> >of data being backed up every 10 minutes cycle is always roughly the same.
> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> >month.
> >I noticed that NOSKIP was being used in the command - i changed this to
> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> >this suppoed to stop some sort of integrity scan on all other backup sets in
> >the archive?
> >full syntax of the backup command that runs now is:
> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >whats going on here?
> >any help appreciated.
> >Thanks
> >Alastair.- Hide quoted text -
> - Show quoted text -
I dont have much idea about tapes but in BOL it says that
NOREWIND implies NOUNLOAD, and these options are incompatible within a
single BACKUP statement
and Methodology has specified also NOUNLOAD in the script.|||fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
implies the former
thanks
"amish" wrote:
> On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> > Looking at the BACKUP commands you posted, which includes REWIND and
> > NOFORMAT, it looks like the backup goes to tape. When you referred to
> > "virtual sql disk object devices" I was not expecting that. Tapes are
> > not virtual disks, tapes are tapes.
> >
> > I have seen that behavior when backing up directly to tape. The
> > problem in that case was reading the tape from the start to the point
> > where the prior backup ended. Lets consider that by the end of the
> > month there have been around 1000 log backups assuming a 5 day work
> > week and an 8 hour work day.
> >
> > I suggest changing the REWIND option to NOREWIND. From the
> > documentation: "NOREWIND -- Specifies that SQL Server will keep the
> > tape open after the backup operation. You can use this option to help
> > improve performance when performing multiple backup operations to a
> > tape."
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> >
> >
> >
> > <Methodol...@.discussions.microsoft.com> wrote:
> > >Hi
> >
> > >I run a full backup once a month and a transaction log backup every 10 mins
> > >through working hours for the remainder of the month until the backup is
> > >re-initialised at th start of the following month.
> >
> > >I backup to virtual sql disk object devices.
> >
> > >As the month progresses the backup takes longer and longer whilst the amount
> > >of data being backed up every 10 minutes cycle is always roughly the same.
> > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > >month.
> >
> > >I noticed that NOSKIP was being used in the command - i changed this to
> > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > >this suppoed to stop some sort of integrity scan on all other backup sets in
> > >the archive?
> >
> > >full syntax of the backup command that runs now is:
> >
> > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> > >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> > >whats going on here?
> >
> > >any help appreciated.
> >
> > >Thanks
> >
> > >Alastair.- Hide quoted text -
> >
> > - Show quoted text -
> I dont have much idea about tapes but in BOL it says that
> NOREWIND implies NOUNLOAD, and these options are incompatible within a
> single BACKUP statement
> and Methodology has specified also NOUNLOAD in the script.
>|||NOUNLOAD/UNLOAD and REWIND/NOREWIND are tape-specific commands. If
you are not using tape they should not be in the BACKUP command at
all.
However, I am paranoid when it comes to dealing with backups. I don't
know that anyone has ever confirmed that there are no side effects to
using tape-specific features with a disk backup. So while I would
suggest removing the tape-specific parameters if you are not using
tape, I would combine that with starting a fresh backup file. It
would not hurt to backup to another device or file for the rest of the
month.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:02:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>thanks for your response Roy.
>if you go to server objects >backup devices you can configure a virtual
>'tape' that is in effect a file on the physical disk. this is what im backing
>up to. as far as I can tell the only differnece between using this method and
>backing up direct to disk is that you get to use a shorter name in the backup
>job command rather than a full file path.
>anyhoo
>may be this ISNT the only differnce. REWIND is default right? so maybe SQL
>is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
>what happens in 2 backups time (20 mins.)
>cheers for the pointer
>"Roy Harvey" wrote:
>> Looking at the BACKUP commands you posted, which includes REWIND and
>> NOFORMAT, it looks like the backup goes to tape. When you referred to
>> "virtual sql disk object devices" I was not expecting that. Tapes are
>> not virtual disks, tapes are tapes.
>> I have seen that behavior when backing up directly to tape. The
>> problem in that case was reading the tape from the start to the point
>> where the prior backup ended. Lets consider that by the end of the
>> month there have been around 1000 log backups assuming a 5 day work
>> week and an 8 hour work day.
>> I suggest changing the REWIND option to NOREWIND. From the
>> documentation: "NOREWIND -- Specifies that SQL Server will keep the
>> tape open after the backup operation. You can use this option to help
>> improve performance when performing multiple backup operations to a
>> tape."
>> Roy Harvey
>> Beacon Falls, CT
>> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>> <Methodology@.discussions.microsoft.com> wrote:
>> >Hi
>> >
>> >I run a full backup once a month and a transaction log backup every 10 mins
>> >through working hours for the remainder of the month until the backup is
>> >re-initialised at th start of the following month.
>> >
>> >I backup to virtual sql disk object devices.
>> >
>> >As the month progresses the backup takes longer and longer whilst the amount
>> >of data being backed up every 10 minutes cycle is always roughly the same.
>> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>> >month.
>> >
>> >I noticed that NOSKIP was being used in the command - i changed this to
>> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
>> >this suppoed to stop some sort of integrity scan on all other backup sets in
>> >the archive?
>> >
>> >full syntax of the backup command that runs now is:
>> >
>> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>> >
>> >but as I say, the SKIP command instead of NOSKIP makes no difference.
>> >
>> >whats going on here?
>> >
>> >any help appreciated.
>> >
>> >Thanks
>> >
>> >Alastair.|||no difference - still taking 5 minutes...
heeeelllpppppp
"Methodology" wrote:
> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
> implies the former
> thanks
>
> "amish" wrote:
> > On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> > > Looking at the BACKUP commands you posted, which includes REWIND and
> > > NOFORMAT, it looks like the backup goes to tape. When you referred to
> > > "virtual sql disk object devices" I was not expecting that. Tapes are
> > > not virtual disks, tapes are tapes.
> > >
> > > I have seen that behavior when backing up directly to tape. The
> > > problem in that case was reading the tape from the start to the point
> > > where the prior backup ended. Lets consider that by the end of the
> > > month there have been around 1000 log backups assuming a 5 day work
> > > week and an 8 hour work day.
> > >
> > > I suggest changing the REWIND option to NOREWIND. From the
> > > documentation: "NOREWIND -- Specifies that SQL Server will keep the
> > > tape open after the backup operation. You can use this option to help
> > > improve performance when performing multiple backup operations to a
> > > tape."
> > >
> > > Roy Harvey
> > > Beacon Falls, CT
> > >
> > > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> > >
> > >
> > >
> > > <Methodol...@.discussions.microsoft.com> wrote:
> > > >Hi
> > >
> > > >I run a full backup once a month and a transaction log backup every 10 mins
> > > >through working hours for the remainder of the month until the backup is
> > > >re-initialised at th start of the following month.
> > >
> > > >I backup to virtual sql disk object devices.
> > >
> > > >As the month progresses the backup takes longer and longer whilst the amount
> > > >of data being backed up every 10 minutes cycle is always roughly the same.
> > > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > > >month.
> > >
> > > >I noticed that NOSKIP was being used in the command - i changed this to
> > > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > > >this suppoed to stop some sort of integrity scan on all other backup sets in
> > > >the archive?
> > >
> > > >full syntax of the backup command that runs now is:
> > >
> > > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> > >
> > > >but as I say, the SKIP command instead of NOSKIP makes no difference.
> > >
> > > >whats going on here?
> > >
> > > >any help appreciated.
> > >
> > > >Thanks
> > >
> > > >Alastair.- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > I dont have much idea about tapes but in BOL it says that
> >
> > NOREWIND implies NOUNLOAD, and these options are incompatible within a
> > single BACKUP statement
> >
> > and Methodology has specified also NOUNLOAD in the script.
> >
> >|||I guess the answer is not to write so many backups to a single file.
Personally I don't think I would set up a backup scheme with one
monthly database backup and log backups every ten minutes. If a
database is active enough to justify a ten-minute interval for the
logs, I would backup the enitre database at least weekly, but more
likely daily. Of course I have no way of knowing your environment and
specific factors that went into the design.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:40:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>no difference - still taking 5 minutes...
>
>heeeelllpppppp
>
>
>"Methodology" wrote:
>> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
>> implies the former
>> thanks
>>
>> "amish" wrote:
>> > On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
>> > > Looking at the BACKUP commands you posted, which includes REWIND and
>> > > NOFORMAT, it looks like the backup goes to tape. When you referred to
>> > > "virtual sql disk object devices" I was not expecting that. Tapes are
>> > > not virtual disks, tapes are tapes.
>> > >
>> > > I have seen that behavior when backing up directly to tape. The
>> > > problem in that case was reading the tape from the start to the point
>> > > where the prior backup ended. Lets consider that by the end of the
>> > > month there have been around 1000 log backups assuming a 5 day work
>> > > week and an 8 hour work day.
>> > >
>> > > I suggest changing the REWIND option to NOREWIND. From the
>> > > documentation: "NOREWIND -- Specifies that SQL Server will keep the
>> > > tape open after the backup operation. You can use this option to help
>> > > improve performance when performing multiple backup operations to a
>> > > tape."
>> > >
>> > > Roy Harvey
>> > > Beacon Falls, CT
>> > >
>> > > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>> > >
>> > >
>> > >
>> > > <Methodol...@.discussions.microsoft.com> wrote:
>> > > >Hi
>> > >
>> > > >I run a full backup once a month and a transaction log backup every 10 mins
>> > > >through working hours for the remainder of the month until the backup is
>> > > >re-initialised at th start of the following month.
>> > >
>> > > >I backup to virtual sql disk object devices.
>> > >
>> > > >As the month progresses the backup takes longer and longer whilst the amount
>> > > >of data being backed up every 10 minutes cycle is always roughly the same.
>> > > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>> > > >month.
>> > >
>> > > >I noticed that NOSKIP was being used in the command - i changed this to
>> > > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
>> > > >this suppoed to stop some sort of integrity scan on all other backup sets in
>> > > >the archive?
>> > >
>> > > >full syntax of the backup command that runs now is:
>> > >
>> > > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>> > > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>> > >
>> > > >but as I say, the SKIP command instead of NOSKIP makes no difference.
>> > >
>> > > >whats going on here?
>> > >
>> > > >any help appreciated.
>> > >
>> > > >Thanks
>> > >
>> > > >Alastair.- Hide quoted text -
>> > >
>> > > - Show quoted text -
>> >
>> > I dont have much idea about tapes but in BOL it says that
>> >
>> > NOREWIND implies NOUNLOAD, and these options are incompatible within a
>> > single BACKUP statement
>> >
>> > and Methodology has specified also NOUNLOAD in the script.
>> >
>> >

NOSKIP & SKIP make no difference to the amount of time backup take

Wednesday, March 21, 2012

Non-logged insert... select

Hi,

Is there any way in either sql2k or 2005 to do an "insert ... select" without writing to the tran log, similar to a non-logged bcp from a flat file? It seems crazy that I should have to go outside the engine and back in again just to avoid this logging. Or am I mistaken, and this is already what happens when you do an insert... select when the db is in bulk logged or simple mode?

Thanks!

Mike

Hey Mike. You can make use of the 'select...into' statement to perform this type of operation in bulk, of course that means the target table can't already exist. There is no way to perform an 'insert' statement with minimal logging. The only options you have for performing minimal logged operations in SQL 2000 are the following commands:

- BULK INSERT
- bcp
- select...into
- create index
- text and image operations (updatetext and writetext)

There are also prerequisites for minimally logged operations, including:

- Table not being replicated
- Table level locking is specified/used
- Specific index considerations (different depending on types of indexes present, whether or not data exists in the table, etc...see BOL for more information)

In SQL 2005, you have the option of using any of the above, and also the new 'BULK' option with the OPENROWSET command to load data from a text file using minimal logging. I suspect this may be your best option if you are running SQL 2005 and loading data into an existing table. The prerequisites for bulk loading still apply though. For more information on this new 2005 bulk operation, see BOL.

Also in SQL 2005, you could use one of the bulk load commands to bulk load data into a new table using minimal logging, then switch that table's data into an existing table if you make use of table partitioning correctly...the switch into the existing table's partition scheme could be a meta-data only operation if you design the partitioning strategy correctly. See SQL 2005 BOL for more information on table partitioning, or just post another question here and we'll see what we can get you for pointers.

HTH,

|||

Can I use the select...into and try to partition the table?

Such as:

select top(1000)*

into dbo.create_on_the_fly_tbl

from dbo.any_existing_tbl

on partitionrangeSCM(id)

Non-logged insert... select

Hi,

Thanks!

Mike

- BULK INSERT
- bcp
- select...into
- create index
- text and image operations (updatetext and writetext)

There are also prerequisites for minimally logged operations, including:

HTH,

|||

Can I use the select...into and try to partition the table?

Such as:

select top(1000)*

into dbo.create_on_the_fly_tbl

from dbo.any_existing_tbl

on partitionrangeSCM(id)

Nonfatal Errors after Adding WindowsNT Event Log as Logging Provider

This has probably been answered somewhere, but...

How do you keep the non-fatal error dialog box from coming up after you enable logging and specify the Windows NT Event Log as the log provider? I know the problem is that the "configuration" for a log provider is expected to have some value and none is needed for the Windows NT Event Log, but the message box is annoying when it comes up.

I could probably hack the XML for the package, but I have a customer who wants to add the Windows NT Event Log as a provider to a lot of packages, so hacking the XML for each package after adding the logging options doesn't sound like a great idea.

Anyone?

Thanks,
Dave Fackler
Hi Dave,

this was a problem with CTP15 and has been addressed since then. I'm afraid you'll have to wait until the next CTP is available.

regards,
ashsql

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:
I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.
What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:
Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

What could be causing this?
|||bump|||

graemeo wrote:
I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.
What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:
Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

What could be causing this?
|||bump|||

graemeo wrote:
I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.
What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:
Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

What could be causing this?
|||bump|||

graemeo wrote:
I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.
What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:
Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!sql

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

What could be causing this?
|||bump|||

graemeo wrote:
I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.
What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:
Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

Monday, March 12, 2012

non logged transactions with transactional replication

I know that if you're doing log shipping and someone runs a fast bcp ( non
logged ) activity against the database that subsequent transaction log dumps
won't load until you do another full dump and load. This can be a problem,
because of the time involved in dumping and loading. How does transactional
replication handle non logged activity? The same way as log shipping? Which
would be bad, or does the data that was inserted or changed as a result of
the non logged transaction make it to the target server?
John,
it is my understanding that in the full recovery model, every change to the
database is logged, so there's no issue with transactional replication. In
SQL Server 2005 the transaction log may be backed up after minimally logged
operations (eg Bulk load operations in the bulk-logged model) so log
shipping is also possible.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But, if you run a transaction that is truly minimally logged (BCP in
bulk-logged recovery model), you will have to reinitialize.
I don't understand why that is considered "bad". A minimally logged
transaction does not write everything to the tran log. Therefore, if you
were allowed to backup the tran log and restore it, you would wind up with a
corrupted database. With respect to replication, a minimally logged
transaction doesn't even hit the replication engine, so your publisher and
subscriber will completely out of synch and since there is no track of the
data, there is no ability to resynch them except by reinitializing.
(Replication doesn't even detect that a minimally logged transaction
occured.)
This is not an issue for either replication or log shipping. It is a side
effect of the way the transaction was executed. The replication and log
shipping features are simply protecting the integrity of the databases
against something they can not control.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OMFC2BDLGHA.648@.TK2MSFTNGP14.phx.gbl...
> John,
> it is my understanding that in the full recovery model, every change to
> the database is logged, so there's no issue with transactional
> replication. In SQL Server 2005 the transaction log may be backed up after
> minimally logged operations (eg Bulk load operations in the bulk-logged
> model) so log shipping is also possible.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It doesn't handle not logged activity because it is based on logged events
and you can't do non-logged events on tables you are replicating.
Non-logged activity will occur when the bulk copy recovery model is selected
and
1) you do certain operations, i.e. create index, select into, etc
2) fast bcp - which requires no indexes on the table among other things
transactional replication requires a table with an index, so you can't do
fast bcp with it. It also requires publications built on preexisting tables
so you won't be able to replicate a table you are selecting into.
Hilary Cotter
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
"John" <John@.discussions.microsoft.com> wrote in message
news:59BAC0A1-DE3B-4E1B-A121-0C4E9B78497C@.microsoft.com...
>I know that if you're doing log shipping and someone runs a fast bcp ( non
> logged ) activity against the database that subsequent transaction log
> dumps
> won't load until you do another full dump and load. This can be a problem,
> because of the time involved in dumping and loading. How does
> transactional
> replication handle non logged activity? The same way as log shipping?
> Which
> would be bad, or does the data that was inserted or changed as a result of
> the non logged transaction make it to the target server?
|||Yes, you can in fact perform minimally logged operations on tables that are
being replicated. Writetext and updatetext come to mind.
Also there is no such thing as a "non-logged" event. Every single operation
that manipulates data will place some kind of entry into the log.
Therefore, the correct term is "minimally logged" and it has been for about
a decade now.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eLcsW4vLGHA.1192@.TK2MSFTNGP11.phx.gbl...
> It doesn't handle not logged activity because it is based on logged
> events and you can't do non-logged events on tables you are replicating.
> Non-logged activity will occur when the bulk copy recovery model is
> selected and
> 1) you do certain operations, i.e. create index, select into, etc
> 2) fast bcp - which requires no indexes on the table among other things
> transactional replication requires a table with an index, so you can't do
> fast bcp with it. It also requires publications built on preexisting
> tables so you won't be able to replicate a table you are selecting into.
> --
> Hilary Cotter
> 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
> "John" <John@.discussions.microsoft.com> wrote in message
> news:59BAC0A1-DE3B-4E1B-A121-0C4E9B78497C@.microsoft.com...
>

Friday, March 9, 2012

non domain log shipping

According to KB article 314515 in regards to having log
shipping set up outside of a domain:
Q3: Do I have to start SQL Server related services under a
domain account as opposed to a local Windows NT account?
A3: It is possible to configure SQL Server services to
start under a local Windows NT account, unless SQL Server
is configured to run as a virtual server in conjunction
with Microsoft Cluster Service. You can use Windows NT
pass-through security for this purpose. Follow these steps
to configure pass-through security:
Create a Windows NT account on the primary, secondary and
monitor computers with the same name and passwords.
Configure SQL Server related services to start under these
Windows NT accounts on all computers.
So what if one of the computers is in a domain and one
isnt. The logins are set up like this:
Source
domain\administrator
Destination
local\administrator
Same passwords on both but Im still having problems.What problem are you having?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Saturday, February 25, 2012

No Windows, Only SQL Server Authentication

Greetings,
I am unable to log onto the MS SQL Server 2000 using my Windows Login name
and Password from the MS command prompt program locally on a new test
installation Windows Enterprise Server 2003 system. However, if I use an
SQL Server Login and Password, it works every time. The command string I am
using is:
"osql.exe -S (local) -U login_name -P password"
When successful (SQL Authentication), an SQL command prompt is returned.
When unsuccessful (Windows Authentication), the following message is
received:
"Login failed for user login_name"
The SQL Server 2000 installation is enabled for both Windows and SQL
authentication and has been updated to the latest service packs.
The installation is a new test set up to experiment with Web Services using
MS SQL Server. The original problem was the installation of the
"wscrRecordStore" demonstration data base from the book, Programming
Microsoft .NET XML WEB SERVICES, by Foggon et al. However, the problem was
reduced to the simple command line issue above in order to troubleshoot it.
Since I am also the Windows and SQL Server system administrator, I have
tried a wide variety of different login strategies. Only users set up with
SQL Authentication seem to be able to access the SQL Server programatically.
What's the problem? This seems to be a straight forward but stubborn
failure.
Richard Scott
Critical Connections, Inc.To establish a Windows authenticated connection from OSQL, you need to
specify the -E parameter instead of -U and -P. This will use the account of
the currently logged in user. For example:
OSQL -S (local) -E

> Only users set up with SQL Authentication seem to be able to access the
SQL Server programatically.
Your connection string needs to specify the type of authentication desired.
Assuming you are using SqlClient:
Windows authentication:
Data Source=MyServer;Integrated Security=SSPI;Initial
Catalog=MyDatabase
SQL Server authentication:
Data Source=MyServer;User ID=MyLogin;Password=MyPassword;Initial
Catalog=MyDatabase
Also, see the article below for additional information on establishing a
trusted connection from ASP.NET:
http://support.microsoft.com/defaul...989&Product=sql
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Scott" <rtscott@.pacbell.net> wrote in message
news:6XL6c.12481$zh1.7776@.newssvr27.news.prodigy.com...
> Greetings,
> I am unable to log onto the MS SQL Server 2000 using my Windows Login name
> and Password from the MS command prompt program locally on a new test
> installation Windows Enterprise Server 2003 system. However, if I use an
> SQL Server Login and Password, it works every time. The command string I
am
> using is:
> "osql.exe -S (local) -U login_name -P password"
> When successful (SQL Authentication), an SQL command prompt is returned.
> When unsuccessful (Windows Authentication), the following message is
> received:
> "Login failed for user login_name"
> The SQL Server 2000 installation is enabled for both Windows and SQL
> authentication and has been updated to the latest service packs.
> The installation is a new test set up to experiment with Web Services
using
> MS SQL Server. The original problem was the installation of the
> "wscrRecordStore" demonstration data base from the book, Programming
> Microsoft .NET XML WEB SERVICES, by Foggon et al. However, the problem
was
> reduced to the simple command line issue above in order to troubleshoot
it.
> Since I am also the Windows and SQL Server system administrator, I have
> tried a wide variety of different login strategies. Only users set up
with
> SQL Authentication seem to be able to access the SQL Server
programatically.
> What's the problem? This seems to be a straight forward but stubborn
> failure.
> Richard Scott
> Critical Connections, Inc.
>|||Not sure if this applies to you, but vefiry that the user who is attempting
to connect actually has a login established in SQL Server. You can do this
by expanding the Security folder in Enterprise Manager and looking at the
Logins.
Rand
This posting is provided "as is" with no warranties and confers no rights.

No Truncate Option in Log Backup

I need to peform a log backup with no truncate. My regular maintenance
plan runs like this:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
-DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
Where in here do I add the no truncate option?Techhead wrote:
> I need to peform a log backup with no truncate. My regular maintenance
> plan runs like this:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>
> Where in here do I add the no truncate option?
>
You don't. You'll have to run the BACKUP command directly, not from a
maintenance plan.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
option is only there in order for you to do the last log backup on a corrupt database. See:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> Techhead wrote:
>> I need to peform a log backup with no truncate. My regular maintenance
>> plan runs like this:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>>
>> Where in here do I add the no truncate option?
> You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Why would I not want to truncate? Because I have a third party backup
solution doing it for me and it will not work if my maintenance plan
and 3rd party backup truncate at the same time. I still want to do a
full backup in SQL but not truncate... maybe a copy of the tran logs
will work, but no truncate.
Tibor Karaszi wrote:
> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
> option is only there in order for you to do the last log backup on a corrupt database. See:
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> > Techhead wrote:
> >> I need to peform a log backup with no truncate. My regular maintenance
> >> plan runs like this:
> >>
> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >>
> >>
> >> Where in here do I add the no truncate option?
> >>
> >
> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
What is the backup command to use?
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||Techhead wrote:
> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time. I still want to do a
> full backup in SQL but not truncate... maybe a copy of the tran logs
> will work, but no truncate.
>
Just curious, have you attempted to RESTORE a complete database,
including these transaction log backups? I don't fully understand why
you have a third-party solution in place, AND you're using the SQL
BACKUP command.
Most folks here will tell you to avoid third-party backup agents for
your databases, and instead use the native BACKUP and RESTORE commands
to dump the database to disk files, and then backup those files to tape.
The third-party tools can be unreliable and difficult to restore from,
particularly a point-in-time restore.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time.
I strongly encourage you to read the article I posted a link to. You will see that even with
NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was written
for SQL Server 2000, I haven't tried it on 2005 yet.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time. I still want to do a
> full backup in SQL but not truncate... maybe a copy of the tran logs
> will work, but no truncate.
>
> Tibor Karaszi wrote:
>> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
>> option is only there in order for you to do the last log backup on a corrupt database. See:
>> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
>> > Techhead wrote:
>> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> plan runs like this:
>> >>
>> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >>
>> >>
>> >> Where in here do I add the no truncate option?
>> >>
>> >
>> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>
> What is the backup command to use?
>> >
>> >
>> > --
>> > Tracy McKibben
>> > MCDBA
>> > http://www.realsqlguy.com
>|||But I don't want to restore using SQL, I want to use my 3rd party
backup solution to do the restore. THis solution can restore SQL DB's
to point-in-time using truncated logs that the software backed up. i
just don't want SQL truncating when my backup software does.
Tibor Karaszi wrote:
> > Why would I not want to truncate? Because I have a third party backup
> > solution doing it for me and it will not work if my maintenance plan
> > and 3rd party backup truncate at the same time.
> I strongly encourage you to read the article I posted a link to. You will see that even with
> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was written
> for SQL Server 2000, I haven't tried it on 2005 yet.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> > Why would I not want to truncate? Because I have a third party backup
> > solution doing it for me and it will not work if my maintenance plan
> > and 3rd party backup truncate at the same time. I still want to do a
> > full backup in SQL but not truncate... maybe a copy of the tran logs
> > will work, but no truncate.
> >
> >
> > Tibor Karaszi wrote:
> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> >> > Techhead wrote:
> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> plan runs like this:
> >> >>
> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >>
> >> >>
> >> >> Where in here do I add the no truncate option?
> >> >>
> >> >
> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >
> >
> > What is the backup command to use?
> >> >
> >> >
> >> > --
> >> > Tracy McKibben
> >> > MCDBA
> >> > http://www.realsqlguy.com
> >|||Lets see if I get this right. I might have missed what you are doing. You want to do something like:
1 Backup database (disk or tape)
2 Tape backup log
3 Disk backup log with NO_TRUNCATE
4 Tape backup log
5 Disk backup log with NO_TRUNCATE
For above scenario, you will be able to restore 1, 2, 3, but not further. See
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
elaborate if above is not your scenario.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> But I don't want to restore using SQL, I want to use my 3rd party
> backup solution to do the restore. THis solution can restore SQL DB's
> to point-in-time using truncated logs that the software backed up. i
> just don't want SQL truncating when my backup software does.
> Tibor Karaszi wrote:
>> > Why would I not want to truncate? Because I have a third party backup
>> > solution doing it for me and it will not work if my maintenance plan
>> > and 3rd party backup truncate at the same time.
>> I strongly encourage you to read the article I posted a link to. You will see that even with
>> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
>> written
>> for SQL Server 2000, I haven't tried it on 2005 yet.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> > Why would I not want to truncate? Because I have a third party backup
>> > solution doing it for me and it will not work if my maintenance plan
>> > and 3rd party backup truncate at the same time. I still want to do a
>> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> > will work, but no truncate.
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis?
>> >> This
>> >> option is only there in order for you to do the last log backup on a corrupt database. See:
>> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> > Techhead wrote:
>> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> plan runs like this:
>> >> >>
>> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >>
>> >> >>
>> >> >> Where in here do I add the no truncate option?
>> >> >>
>> >> >
>> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>> >
>> >
>> > What is the backup command to use?
>> >> >
>> >> >
>> >> > --
>> >> > Tracy McKibben
>> >> > MCDBA
>> >> > http://www.realsqlguy.com
>> >
>|||Actually this is what I want:
1. Backup Database to Disk
2. Backup Log to Disk NO_TRUNCATE
3. Backup Database to Tape
4. Backup Log to Tape TRUNCATE
Tibor Karaszi wrote:
> Lets see if I get this right. I might have missed what you are doing. You want to do something like:
> 1 Backup database (disk or tape)
> 2 Tape backup log
> 3 Disk backup log with NO_TRUNCATE
> 4 Tape backup log
> 5 Disk backup log with NO_TRUNCATE
> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> elaborate if above is not your scenario.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> > But I don't want to restore using SQL, I want to use my 3rd party
> > backup solution to do the restore. THis solution can restore SQL DB's
> > to point-in-time using truncated logs that the software backed up. i
> > just don't want SQL truncating when my backup software does.
> >
> > Tibor Karaszi wrote:
> >> > Why would I not want to truncate? Because I have a third party backup
> >> > solution doing it for me and it will not work if my maintenance plan
> >> > and 3rd party backup truncate at the same time.
> >>
> >> I strongly encourage you to read the article I posted a link to. You will see that even with
> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
> >> written
> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> > Why would I not want to truncate? Because I have a third party backup
> >> > solution doing it for me and it will not work if my maintenance plan
> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> > will work, but no truncate.
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis?
> >> >> This
> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> > Techhead wrote:
> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> plan runs like this:
> >> >> >>
> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >>
> >> >> >>
> >> >> >> Where in here do I add the no truncate option?
> >> >> >>
> >> >> >
> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >> >
> >> >
> >> > What is the backup command to use?
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Tracy McKibben
> >> >> > MCDBA
> >> >> > http://www.realsqlguy.com
> >> >
> >|||So you will only have one transaction log backup between each database backup?
Why do you want to use NO_TRUNCATE for the log backup to disk?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> Actually this is what I want:
> 1. Backup Database to Disk
> 2. Backup Log to Disk NO_TRUNCATE
> 3. Backup Database to Tape
> 4. Backup Log to Tape TRUNCATE
>
> Tibor Karaszi wrote:
>> Lets see if I get this right. I might have missed what you are doing. You want to do something
>> like:
>> 1 Backup database (disk or tape)
>> 2 Tape backup log
>> 3 Disk backup log with NO_TRUNCATE
>> 4 Tape backup log
>> 5 Disk backup log with NO_TRUNCATE
>> For above scenario, you will be able to restore 1, 2, 3, but not further. See
>> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
>> elaborate if above is not your scenario.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
>> > But I don't want to restore using SQL, I want to use my 3rd party
>> > backup solution to do the restore. THis solution can restore SQL DB's
>> > to point-in-time using truncated logs that the software backed up. i
>> > just don't want SQL truncating when my backup software does.
>> >
>> > Tibor Karaszi wrote:
>> >> > Why would I not want to truncate? Because I have a third party backup
>> >> > solution doing it for me and it will not work if my maintenance plan
>> >> > and 3rd party backup truncate at the same time.
>> >>
>> >> I strongly encourage you to read the article I posted a link to. You will see that even with
>> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
>> >> written
>> >> for SQL Server 2000, I haven't tried it on 2005 yet.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> >> > Why would I not want to truncate? Because I have a third party backup
>> >> > solution doing it for me and it will not work if my maintenance plan
>> >> > and 3rd party backup truncate at the same time. I still want to do a
>> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> >> > will work, but no truncate.
>> >> >
>> >> >
>> >> > Tibor Karaszi wrote:
>> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
>> >> >> basis?
>> >> >> This
>> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
>> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> >> > Techhead wrote:
>> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> >> plan runs like this:
>> >> >> >>
>> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >> >>
>> >> >> >>
>> >> >> >> Where in here do I add the no truncate option?
>> >> >> >>
>> >> >> >
>> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>> >> >
>> >> >
>> >> > What is the backup command to use?
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > Tracy McKibben
>> >> >> > MCDBA
>> >> >> > http://www.realsqlguy.com
>> >> >
>> >
>|||Tibor Karaszi wrote:
> So you will only have one transaction log backup between each database backup?
Actually, after a disk backup, my tape backup will back up the log and
truncate every 4 hours.
> Why do you want to use NO_TRUNCATE for the log backup to disk?
Because, as I stated earlier, the tape backup does the truncation. Why
would I want my disk backup and tape backup both truncating? You would
have to restore from both disk and tape in order to do a complete log
restore to point-in-time.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> > Actually this is what I want:
> >
> > 1. Backup Database to Disk
> > 2. Backup Log to Disk NO_TRUNCATE
> > 3. Backup Database to Tape
> > 4. Backup Log to Tape TRUNCATE
> >
> >
> > Tibor Karaszi wrote:
> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
> >> like:
> >>
> >> 1 Backup database (disk or tape)
> >> 2 Tape backup log
> >> 3 Disk backup log with NO_TRUNCATE
> >> 4 Tape backup log
> >> 5 Disk backup log with NO_TRUNCATE
> >>
> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> >> elaborate if above is not your scenario.
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> >> > But I don't want to restore using SQL, I want to use my 3rd party
> >> > backup solution to do the restore. THis solution can restore SQL DB's
> >> > to point-in-time using truncated logs that the software backed up. i
> >> > just don't want SQL truncating when my backup software does.
> >> >
> >> > Tibor Karaszi wrote:
> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> > and 3rd party backup truncate at the same time.
> >> >>
> >> >> I strongly encourage you to read the article I posted a link to. You will see that even with
> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
> >> >> written
> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> >> > will work, but no truncate.
> >> >> >
> >> >> >
> >> >> > Tibor Karaszi wrote:
> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
> >> >> >> basis?
> >> >> >> This
> >> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> >> > Techhead wrote:
> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> >> plan runs like this:
> >> >> >> >>
> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> Where in here do I add the no truncate option?
> >> >> >> >>
> >> >> >> >
> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >> >> >
> >> >> >
> >> >> > What is the backup command to use?
> >> >> >> >
> >> >> >> >
> >> >> >> > --
> >> >> >> > Tracy McKibben
> >> >> >> > MCDBA
> >> >> >> > http://www.realsqlguy.com
> >> >> >
> >> >
> >|||Techhead wrote:
> Tibor Karaszi wrote:
>> So you will only have one transaction log backup between each database backup?
> Actually, after a disk backup, my tape backup will back up the log and
> truncate every 4 hours.
>> Why do you want to use NO_TRUNCATE for the log backup to disk?
> Because, as I stated earlier, the tape backup does the truncation. Why
> would I want my disk backup and tape backup both truncating? You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
You could really save yourself some trouble, and avoid a potential
restore failure, by eliminating this third-party log backup. Use the
native SQL backup command to do your backups to disk, and then let the
tape software backup those disk files. That's what any experienced DBA
does, because those third-party gizmos are unreliable, slow, and awkward
to restore from.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
Did you read below article I posted earlier?
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
If not, please do. You cannot skip restoring a log backup, even if it was taken using NO_TRUNCATE.
Period. If you chose to not believe me, you are welcome, even though I have proof in above article.
It will be your head when you need to do a production restore.
In 2005, we have a new option for the backup command named COPY_ONLY. It will do what you want.
I really cannot say more on this. Again, read the article, and then it is up to you whether you want
to be able to restore from your log backups or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1158255181.850317.47190@.h48g2000cwc.googlegroups.com...
> Tibor Karaszi wrote:
>> So you will only have one transaction log backup between each database backup?
> Actually, after a disk backup, my tape backup will back up the log and
> truncate every 4 hours.
>> Why do you want to use NO_TRUNCATE for the log backup to disk?
> Because, as I stated earlier, the tape backup does the truncation. Why
> would I want my disk backup and tape backup both truncating? You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
>> > Actually this is what I want:
>> >
>> > 1. Backup Database to Disk
>> > 2. Backup Log to Disk NO_TRUNCATE
>> > 3. Backup Database to Tape
>> > 4. Backup Log to Tape TRUNCATE
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
>> >> like:
>> >>
>> >> 1 Backup database (disk or tape)
>> >> 2 Tape backup log
>> >> 3 Disk backup log with NO_TRUNCATE
>> >> 4 Tape backup log
>> >> 5 Disk backup log with NO_TRUNCATE
>> >>
>> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
>> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
>> >> elaborate if above is not your scenario.
>> >>
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
>> >> > But I don't want to restore using SQL, I want to use my 3rd party
>> >> > backup solution to do the restore. THis solution can restore SQL DB's
>> >> > to point-in-time using truncated logs that the software backed up. i
>> >> > just don't want SQL truncating when my backup software does.
>> >> >
>> >> > Tibor Karaszi wrote:
>> >> >> > Why would I not want to truncate? Because I have a third party backup
>> >> >> > solution doing it for me and it will not work if my maintenance plan
>> >> >> > and 3rd party backup truncate at the same time.
>> >> >>
>> >> >> I strongly encourage you to read the article I posted a link to. You will see that even
>> >> >> with
>> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article
>> >> >> was
>> >> >> written
>> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> >> >> > Why would I not want to truncate? Because I have a third party backup
>> >> >> > solution doing it for me and it will not work if my maintenance plan
>> >> >> > and 3rd party backup truncate at the same time. I still want to do a
>> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> >> >> > will work, but no truncate.
>> >> >> >
>> >> >> >
>> >> >> > Tibor Karaszi wrote:
>> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
>> >> >> >> basis?
>> >> >> >> This
>> >> >> >> option is only there in order for you to do the last log backup on a corrupt database.
>> >> >> >> See:
>> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> >> http://www.solidqualitylearning.com/
>> >> >> >>
>> >> >> >>
>> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> >> >> > Techhead wrote:
>> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> >> >> plan runs like this:
>> >> >> >> >>
>> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> Where in here do I add the no truncate option?
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance
>> >> >> >> > plan.
>> >> >> >
>> >> >> >
>> >> >> > What is the backup command to use?
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > --
>> >> >> >> > Tracy McKibben
>> >> >> >> > MCDBA
>> >> >> >> > http://www.realsqlguy.com
>> >> >> >
>> >> >
>> >
>|||Tibor Karaszi wrote:
>> You would
>> have to restore from both disk and tape in order to do a complete log
>> restore to point-in-time.
> Did you read below article I posted earlier?
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> If not, please do. You cannot skip restoring a log backup, even if it
> was taken using NO_TRUNCATE. Period. If you chose to not believe me, you
> are welcome, even though I have proof in above article. It will be your
> head when you need to do a production restore.
> In 2005, we have a new option for the backup command named COPY_ONLY. It
> will do what you want.
> I really cannot say more on this. Again, read the article, and then it
> is up to you whether you want to be able to restore from your log
> backups or not.
Seems to be a theme around here this week, folks insist on using square
wheels on their car, in spite of all the suggestions given to use the
proper round ones... My guess is the third-party backup software has a
nice shiny GUI, or he convinced his boss that he MUST spend $$,$$$$ to
buy it, and now can't make it work...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am an experienced DBA and 99% of all my other SQL backups are within
SQL and backed up to disk. Then tape comes around and backs those up. I
100% agree that this is the preferred method and this is what I do for
about 15 SQL servers EXCEPT for 1 particular instance. My point in this
posting is not to argue about what method works best. Don't worry about
what I want to do in this special circumstance. What I do is nobody's
business. How I do it is the whole point in this posting. Please stick
to the objective and leave the opinionated comments for another group
or time.
Tracy McKibben wrote:
> Techhead wrote:
> > Tibor Karaszi wrote:
> >> So you will only have one transaction log backup between each database backup?
> > Actually, after a disk backup, my tape backup will back up the log and
> > truncate every 4 hours.
> >> Why do you want to use NO_TRUNCATE for the log backup to disk?
> >
> > Because, as I stated earlier, the tape backup does the truncation. Why
> > would I want my disk backup and tape backup both truncating? You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> You could really save yourself some trouble, and avoid a potential
> restore failure, by eliminating this third-party log backup. Use the
> native SQL backup command to do your backups to disk, and then let the
> tape software backup those disk files. That's what any experienced DBA
> does, because those third-party gizmos are unreliable, slow, and awkward
> to restore from.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tibor Karaszi wrote:
> > You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> Did you read below article I posted earlier?
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Yes I did and it has nothing to do with what I am doing.
> If not, please do.
You cannot skip restoring a log backup
"I am not skipping a log backup" *remember, my tape backup does the log
backup and truncates.
even if it was taken using NO_TRUNCATE.
> Period. If you chose to not believe me, you are welcome, even though I have proof in above article.
> It will be your head when you need to do a production restore.
> In 2005, we have a new option for the backup command named COPY_ONLY. It will do what you want.
There we go. COPY_ONLY. Just what I wanted to hear! This will solve my
issue once and for all.
SQL does a DB backup to disk and a COPY of the log to disk.
Tape does a DB backup to tape and a log backup to tape with TRUNCATION.
> I really cannot say more on this. Again, read the article, and then it is up to you whether you want
> to be able to restore from your log backups or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1158255181.850317.47190@.h48g2000cwc.googlegroups.com...
> > Tibor Karaszi wrote:
> >> So you will only have one transaction log backup between each database backup?
> > Actually, after a disk backup, my tape backup will back up the log and
> > truncate every 4 hours.
> >>
> >> Why do you want to use NO_TRUNCATE for the log backup to disk?
> >
> > Because, as I stated earlier, the tape backup does the truncation. Why
> > would I want my disk backup and tape backup both truncating? You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> >> > Actually this is what I want:
> >> >
> >> > 1. Backup Database to Disk
> >> > 2. Backup Log to Disk NO_TRUNCATE
> >> > 3. Backup Database to Tape
> >> > 4. Backup Log to Tape TRUNCATE
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
> >> >> like:
> >> >>
> >> >> 1 Backup database (disk or tape)
> >> >> 2 Tape backup log
> >> >> 3 Disk backup log with NO_TRUNCATE
> >> >> 4 Tape backup log
> >> >> 5 Disk backup log with NO_TRUNCATE
> >> >>
> >> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> >> >> elaborate if above is not your scenario.
> >> >>
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> >> >> > But I don't want to restore using SQL, I want to use my 3rd party
> >> >> > backup solution to do the restore. THis solution can restore SQL DB's
> >> >> > to point-in-time using truncated logs that the software backed up. i
> >> >> > just don't want SQL truncating when my backup software does.
> >> >> >
> >> >> > Tibor Karaszi wrote:
> >> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> >> > and 3rd party backup truncate at the same time.
> >> >> >>
> >> >> >> I strongly encourage you to read the article I posted a link to. You will see that even
> >> >> >> with
> >> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article
> >> >> >> was
> >> >> >> written
> >> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> >> >> > will work, but no truncate.
> >> >> >> >
> >> >> >> >
> >> >> >> > Tibor Karaszi wrote:
> >> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
> >> >> >> >> basis?
> >> >> >> >> This
> >> >> >> >> option is only there in order for you to do the last log backup on a corrupt database.
> >> >> >> >> See:
> >> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> >> >> > Techhead wrote:
> >> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> >> >> plan runs like this:
> >> >> >> >> >>
> >> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> Where in here do I add the no truncate option?
> >> >> >> >> >>
> >> >> >> >> >
> >> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance
> >> >> >> >> > plan.
> >> >> >> >
> >> >> >> >
> >> >> >> > What is the backup command to use?
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> > --
> >> >> >> >> > Tracy McKibben
> >> >> >> >> > MCDBA
> >> >> >> >> > http://www.realsqlguy.com
> >> >> >> >
> >> >> >
> >> >
> >|||Tracy McKibben wrote:
> Tibor Karaszi wrote:
> >> You would
> >> have to restore from both disk and tape in order to do a complete log
> >> restore to point-in-time.
> >
> > Did you read below article I posted earlier?
> > http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >
> > If not, please do. You cannot skip restoring a log backup, even if it
> > was taken using NO_TRUNCATE. Period. If you chose to not believe me, you
> > are welcome, even though I have proof in above article. It will be your
> > head when you need to do a production restore.
> >
> > In 2005, we have a new option for the backup command named COPY_ONLY. It
> > will do what you want.
> >
> > I really cannot say more on this. Again, read the article, and then it
> > is up to you whether you want to be able to restore from your log
> > backups or not.
> Seems to be a theme around here this week, folks insist on using square
> wheels on their car, in spite of all the suggestions given to use the
> proper round ones... My guess is the third-party backup software has a
> nice shiny GUI, or he convinced his boss that he MUST spend $$,$$$$ to
> buy it, and now can't make it work...
Unlike some people in this world, I can take a square wheel and turn it
into a round one.
Anyways, without Tracy's "in-the-box" suggestions, I've been able to
make it work and I can restore either way. How did I do it? We'll if
you can take your square MCDBA exam books and turn them into round ones
then I'll tell you.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Friday, March 30, 2012

NOSKIP & SKIP make no difference to the amount of time backup take

NOSKIP & SKIP make no difference to the amount of time backup take

NOSKIP & SKIP make no difference to the amount of time backup take

Wednesday, March 21, 2012

Non-logged insert... select

Non-logged insert... select

Nonfatal Errors after Adding WindowsNT Event Log as Logging Provider

non-existent step?

non-existent step?

non-existent step?

non-existent step?

non-existent step?

Monday, March 12, 2012

non logged transactions with transactional replication

Friday, March 9, 2012

non domain log shipping

Saturday, February 25, 2012

No Windows, Only SQL Server Authentication

No Truncate Option in Log Backup

interface supported

Blog Archive

About Me