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:
> 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:
>
|||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:
>
>
>
>
>
>
> - 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:
> 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:
[vbcol=seagreen]
>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:
|||no difference - still taking 5 minutes...
heeeelllpppppp
"Methodology" wrote:
[vbcol=seagreen]
> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
> implies the former
> thanks
>
> "amish" wrote:
|||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:
[vbcol=seagreen]
>no difference - still taking 5 minutes...
>
>heeeelllpppppp
>
>
>"Methodology" wrote:

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

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 , N
OINIT ,
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 min
s
> 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 amou
nt
> 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. Is
nt
> 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 amoun
t
>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. Isn
t
>this suppoed to stop some sort of integrity scan on all other backup sets i
n
>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:
>
>
>
>
>
>
>
>
>
>
>
> - 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.

Normalizing help and naming conventions

I have an Access Database that I am working on normalizing to SQL Server.
This Access database has been revised multiple times, each time the
programmer (not me!) didn't work to get the existing data to the same level
as the revised program. For instance, the HairColor column has the
following DISTINCT values,
Auburn
Black
Black/Gray
Blonde
Brown
Brown/Gray
Dark Brown
Gray
Gray/Black
Gray/Brown
Lt. Brown
Red
Red-blonde
Red/Blonde
Red/Brown
Now the current revision of the Access database limits the values for the
HairColor column to,
Black
Blonde
Brown
Gray
Red
So I need to make the current values reflect the current dropdown. I know
how to go about this, but have a question about normalizing this database.
Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of just
leaving the text in there?
Also, if I am to create a new table, how does the naming convention work
with that? Should I prefix my table name with fk_HairColor, or something
like that?
Any advice is appreciated!
Thanks!
drewI would create a table named HairColor with the columns:
HairColorID int
HairColor varchar (20)
In any table that references this table, you can create a foreign key. You
will take up less space in your DB overall. Also, consider what would
happen if you decided to change "Lt. Brown" to "Light Brown". With the
normalized version, you change it in only one place.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
I have an Access Database that I am working on normalizing to SQL Server.
This Access database has been revised multiple times, each time the
programmer (not me!) didn't work to get the existing data to the same level
as the revised program. For instance, the HairColor column has the
following DISTINCT values,
Auburn
Black
Black/Gray
Blonde
Brown
Brown/Gray
Dark Brown
Gray
Gray/Black
Gray/Brown
Lt. Brown
Red
Red-blonde
Red/Blonde
Red/Brown
Now the current revision of the Access database limits the values for the
HairColor column to,
Black
Blonde
Brown
Gray
Red
So I need to make the current values reflect the current dropdown. I know
how to go about this, but have a question about normalizing this database.
Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of just
leaving the text in there?
Also, if I am to create a new table, how does the naming convention work
with that? Should I prefix my table name with fk_HairColor, or something
like that?
Any advice is appreciated!
Thanks!
drew|||Thanks for the response... I thought it was better to replace it with a FK,
but am trying to convince my boss the same...
So you don't use a different naming convention with FKs? I just have about
30 tables on this database and it is getting harder to read for each FK
table I include...
Thanks,
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>I would create a table named HairColor with the columns:
> HairColorID int
> HairColor varchar (20)
> In any table that references this table, you can create a foreign key.
> You
> will take up less space in your DB overall. Also, consider what would
> happen if you decided to change "Lt. Brown" to "Light Brown". With the
> normalized version, you change it in only one place.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
> I have an Access Database that I am working on normalizing to SQL Server.
> This Access database has been revised multiple times, each time the
> programmer (not me!) didn't work to get the existing data to the same
> level
> as the revised program. For instance, the HairColor column has the
> following DISTINCT values,
> Auburn
> Black
> Black/Gray
> Blonde
> Brown
> Brown/Gray
> Dark Brown
> Gray
> Gray/Black
> Gray/Brown
> Lt. Brown
> Red
> Red-blonde
> Red/Blonde
> Red/Brown
> Now the current revision of the Access database limits the values for the
> HairColor column to,
> Black
> Blonde
> Brown
> Gray
> Red
> So I need to make the current values reflect the current dropdown. I know
> how to go about this, but have a question about normalizing this database.
> Should I create a seperate table, HairColor and then assign ID's to the
> HairColor column? What is the benefit of doing it this way, instead of
> just
> leaving the text in there?
> Also, if I am to create a new table, how does the naming convention work
> with that? Should I prefix my table name with fk_HairColor, or something
> like that?
> Any advice is appreciated!
> Thanks!
> drew
>|||I did say to use a foreign key. Here's an example. Let's say that you have
a Persons table, and each person has a hair colour. Here's how the Persons
table would go:
create table Persons
(
PersonID int primary key
, HairColorID int not null
FOREIGN KEY FK1_Persons
REFERENCES HairColor
, ...
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
news:uW2Bs7LXGHA.196@.TK2MSFTNGP04.phx.gbl...
Thanks for the response... I thought it was better to replace it with a FK,
but am trying to convince my boss the same...
So you don't use a different naming convention with FKs? I just have about
30 tables on this database and it is getting harder to read for each FK
table I include...
Thanks,
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>I would create a table named HairColor with the columns:
> HairColorID int
> HairColor varchar (20)
> In any table that references this table, you can create a foreign key.
> You
> will take up less space in your DB overall. Also, consider what would
> happen if you decided to change "Lt. Brown" to "Light Brown". With the
> normalized version, you change it in only one place.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
> I have an Access Database that I am working on normalizing to SQL Server.
> This Access database has been revised multiple times, each time the
> programmer (not me!) didn't work to get the existing data to the same
> level
> as the revised program. For instance, the HairColor column has the
> following DISTINCT values,
> Auburn
> Black
> Black/Gray
> Blonde
> Brown
> Brown/Gray
> Dark Brown
> Gray
> Gray/Black
> Gray/Brown
> Lt. Brown
> Red
> Red-blonde
> Red/Blonde
> Red/Brown
> Now the current revision of the Access database limits the values for the
> HairColor column to,
> Black
> Blonde
> Brown
> Gray
> Red
> So I need to make the current values reflect the current dropdown. I know
> how to go about this, but have a question about normalizing this database.
> Should I create a seperate table, HairColor and then assign ID's to the
> HairColor column? What is the benefit of doing it this way, instead of
> just
> leaving the text in there?
> Also, if I am to create a new table, how does the naming convention work
> with that? Should I prefix my table name with fk_HairColor, or something
> like that?
> Any advice is appreciated!
> Thanks!
> drew
>|||>> Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of
just
leaving the text in there? <<
Let's start with basics:
1) Do your research! Is there an industry standard code for hair
colors? Use it
2) If not, then design an encoding that has a missing and/or misc code
value -- Hey, I am bald!
3) If you expect the values to change, the list to be long, or that you
willneed to add other information later, then put the encoding into a
separate table.
4) If you expect the values to be static, the list to be short or that
you will not need to add other information later, then put the encoding
into a CHECK() constraint. For example, the ISO sex codes are going to
stay the same for awhile, as are the two-letter state codes.
Get a copy of SQL PROGRAMMING STYLE. You never, never prefix a data
element name with thigns to tell you (1) Where it is used -- so no
table names (2) how it is used -- so no pk- or fk- crap (3) how it is
store, so no data types. Follow ISO-11179 rules and name a thing for
what it is, independent of its location or usage.
Do not blindly design an encoding as a numbered list. Think about
hierarchical ("Dewey Decimal") codes, abbreviation codes, etc. That
is also in SQL PROGRAMMING STYLE.|||I second getting SQL Programming Style. I read it in 2 ws. It is
any easy read.|||For the record, I was agreeing with you!
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYsy3EMXGHA.752@.TK2MSFTNGP02.phx.gbl...
>I did say to use a foreign key. Here's an example. Let's say that you
>have
> a Persons table, and each person has a hair colour. Here's how the
> Persons
> table would go:
> create table Persons
> (
> PersonID int primary key
> , HairColorID int not null
> FOREIGN KEY FK1_Persons
> REFERENCES HairColor
> , ...
> )
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:uW2Bs7LXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response... I thought it was better to replace it with a
> FK,
> but am trying to convince my boss the same...
> So you don't use a different naming convention with FKs? I just have
> about
> 30 tables on this database and it is getting harder to read for each FK
> table I include...
> Thanks,
> Drew
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>

normalize a table with 3 similar columns (was "Plz help out")

Hi,I am working on SQL Server.
I have a table with columns that need to be broken down. I nees to break this table:

TelNo1 TelNo2 TelNo3

555-44-33 555-43-88 555-46-89

into:

Tel_Number

555-44-33
555-43-88
555-46-89

I tried using the union statement but i just did'nt succed. Can anyone help me,pleaseIt helps if you post the statements where you didn't "succeed". But anyway:
SELECT TelNo1
FROM theTable
UNION
SELECT TelNo2
FROM theTable
UNION
SELECT TelNo3
FROM theTable

NooB Scripting Question How to use mailto

I am a noob to sql and asp programming. I am working on a db for the
gaming squad I am a member of. One of the pages I created is a roster
list of all the squad members. Part of this roster is listing each
member's email address. What several people have asked of me is to
make it so the email addresses can be clicked on to open their email
programs, just as html allows the mailto function to work.

Here is a copy of the coding I am currently using:
<td align="center"><font face="Arial"
color="#C0C0C0"><%=rst("email")%></font></td>
This will list the email address of the person, but how do I make it
so the line will function as a mailto command?
The page is saved as an .asp.

Thanks for any advice!

Davedavestrike (davestrike@.nventure.com) writes:
> Here is a copy of the coding I am currently using:
><td align="center"><font face="Arial"
> color="#C0C0C0"><%=rst("email")%></font></td>
> This will list the email address of the person, but how do I make it
> so the line will function as a mailto command?
> The page is saved as an .asp.

Actually, I don't have the slightest idea, nor do I feel to compelled
to have one, since this is an SQL Server forum, and not related to ASP.

But they say ASP has something to do with HTML, in which case I would
try with:

<td align="center"><font face="Arial" color="#C0C0C0">
<A HEF="mailto:%=rst("email")%"><%=rst("email")%></A>
</font></td
But if that does not work (and it probably doesn't) you should look
for an ASP forum.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Noob question

I've been working with SQL Server for a few years now, but I'm new to this
particular newsgroup (heh, to think I pretty much just used Usenet for
gaming discussions in the past). I hope to do my part to contribute as I
pick up a ton of useful information.
Anyway, I've noticed a couple of people here with similarly cryptic aliases
such as "examnotes". Is there some significance to these
names? Do they have something to do with some sort of text encoding scheme
or something?
Just curious.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin FranklinAre you using a web-based newsreader?
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Rumbledor" <Rumbledor@.hotspamsuxmail.com> wrote in message
news:Xns96308C9E3F4BRumbledorhotmailcom@.
204.127.199.17...
> I've been working with SQL Server for a few years now, but I'm new to this
> particular newsgroup (heh, to think I pretty much just used Usenet for
> gaming discussions in the past). I hope to do my part to contribute as I
> pick up a ton of useful information.
> Anyway, I've noticed a couple of people here with similarly cryptic
aliases
> such as "examnotes". Is there some significance to these
> names? Do they have something to do with some sort of text encoding scheme
> or something?
> Just curious.
> --
> Rumble
> "Write something worth reading, or do something worth writing."
> -- Benjamin Franklin|||On Wed, 06 Apr 2005 18:49:17 GMT, Rumbledor wrote:

>I've been working with SQL Server for a few years now, but I'm new to this
>particular newsgroup (heh, to think I pretty much just used Usenet for
>gaming discussions in the past). I hope to do my part to contribute as I
>pick up a ton of useful information.
>Anyway, I've noticed a couple of people here with similarly cryptic aliases
>such as "examnotes". Is there some significance to these
>names? Do they have something to do with some sort of text encoding scheme
>or something?
Hi Rumbledor,
I think that your newsreader isn't properly formatting some encoded
string. The first characters of the string you quote are =?Utf-8. UTF-8
is one of the unicode encoding schemes. So I guess that someone posted a
message with the name in UTF-8, but your newsreader didn't recognise it
as such.
FWIW, I've never seen names like that in this group, so either I missed
those messages (quite unlikely), or my news reader does format the UTF-8
encoded names correctly (quite likely).
More information on UTF-8: http://en.wikipedia.org/wiki/UTF-8
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Non-release version (600) is not supported by this version of SQL Server

I downloaded the 101 Samples installation (i.e. 101SamplesCS.msi) from Microsoft's website, which contains SQL Server database files.

While working with the databases in the "Data Access" samples from the 101 Samples projects, I get the following error:

"Database <mdf_name> cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database..."

I assume the database files were created with a beta or CTP version of SQL Server 2005. Does anyone know where I can get an updated version of these database files or scripts to update them?

Please help.

Mike.

I was getting this error back with the Beta versions when it was updated to Beta 2 and the last of the CTP. This generally means that the database file was created with an older version of the express products and can not go through the automatic upgrade processes. To fix it I had to get the SQL Scripts and re create the database structure.

If you have a look at the mdf name and post that we might be able to direct you to the latest versions, but you might find that if you download the latest samples from the downloads site for the SQL Express product you might get the file you need. More then likley it will be the northwind, pups or adventure works database.

|||

the error is caused by having a database that is an older format, usually beta. I believe the 101 Code Samples have been updated recently to contain release format databases. You can find the download link at http://msdn.microsoft.com/vstudio/downloads/101samples/default.aspx.

Try installing a fresh copy of the samples and see if they work. I can confirm the 'Multiple Active ResultSets' sample is able to load the database without a problem.

Mike

|||

I am having an identical problem with the plants database - used in the XPath/XSLT data sample - I've tried reloading the plants.mdf from both the latest c# & VB.net sample on the VS 2005 101 samples with no luck. The actual error is

Database '9D5A6E69A8EF2325FC6D4339CED21FB2_CS101SAMPLESDATAACCESS\XPATH AND XSLT TRANSFORMATIONS ENHANCEMENTS\XPATHXSL\DATABASE\PLANTS.MDF' cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

|||

I would post this issue to the Feedback Center (http://connect.microsoft.com) for Visual Studio, since this is there sample. VS will need to update the sample as opening the file would require a pre-release build of SQL Server 2005 that is not available.

Regards,

Mike

|||

I note I can work around this by using the equivalent PlantInfo table in the C:\Documents and Settings\dlaub\My Documents\MSDN\CS101SamplesAll\CS101SamplesDataAccess\Using the XML data type with SQL Server 2005\XMLDataType\Database\XMLData.mdf database

You have to tweak the source code a bit (different database name, table name, column names)

And you just create a new table in ANY sql 2005 database - all this table has is an identity column and a xml column

|||

This problem

"cannot be upgraded because its non-release version (600) is not
supported by this version of SQL Server. You cannot open a database that
is incompatible with this version of sqlservr.exe. You must re-create
the database."

still exists in the testdata.mdf database (dated 2006-02-20) shipped with the 'DataReader vs. DataSet comparision' example in the CS101SamplesDataAccess folder of the CS101SamplesAll which I have downloaded several times most recently in April 2007.

I'm running Visual Studio 2005 Standard Edition SP1

Robert Wishlaw

Non-release version (600) is not supported by this version of SQL Server

I downloaded the 101 Samples installation (i.e. 101SamplesCS.msi) from Microsoft's website, which contains SQL Server database files.

While working with the databases in the "Data Access" samples from the 101 Samples projects, I get the following error:

"Database <mdf_name> cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database..."

I assume the database files were created with a beta or CTP version of SQL Server 2005. Does anyone know where I can get an updated version of these database files or scripts to update them?

Please help.

Mike.

I was getting this error back with the Beta versions when it was updated to Beta 2 and the last of the CTP. This generally means that the database file was created with an older version of the express products and can not go through the automatic upgrade processes. To fix it I had to get the SQL Scripts and re create the database structure.

If you have a look at the mdf name and post that we might be able to direct you to the latest versions, but you might find that if you download the latest samples from the downloads site for the SQL Express product you might get the file you need. More then likley it will be the northwind, pups or adventure works database.

|||

the error is caused by having a database that is an older format, usually beta. I believe the 101 Code Samples have been updated recently to contain release format databases. You can find the download link at http://msdn.microsoft.com/vstudio/downloads/101samples/default.aspx.

Try installing a fresh copy of the samples and see if they work. I can confirm the 'Multiple Active ResultSets' sample is able to load the database without a problem.

Mike

|||

I am having an identical problem with the plants database - used in the XPath/XSLT data sample - I've tried reloading the plants.mdf from both the latest c# & VB.net sample on the VS 2005 101 samples with no luck. The actual error is

Database '9D5A6E69A8EF2325FC6D4339CED21FB2_CS101SAMPLESDATAACCESS\XPATH AND XSLT TRANSFORMATIONS ENHANCEMENTS\XPATHXSL\DATABASE\PLANTS.MDF' cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

|||

I would post this issue to the Feedback Center (http://connect.microsoft.com) for Visual Studio, since this is there sample. VS will need to update the sample as opening the file would require a pre-release build of SQL Server 2005 that is not available.

Regards,

Mike

|||

I note I can work around this by using the equivalent PlantInfo table in the C:\Documents and Settings\dlaub\My Documents\MSDN\CS101SamplesAll\CS101SamplesDataAccess\Using the XML data type with SQL Server 2005\XMLDataType\Database\XMLData.mdf database

You have to tweak the source code a bit (different database name, table name, column names)

And you just create a new table in ANY sql 2005 database - all this table has is an identity column and a xml column

|||

This problem

"cannot be upgraded because its non-release version (600) is not
supported by this version of SQL Server. You cannot open a database that
is incompatible with this version of sqlservr.exe. You must re-create
the database."

still exists in the testdata.mdf database (dated 2006-02-20) shipped with the 'DataReader vs. DataSet comparision' example in the CS101SamplesDataAccess folder of the CS101SamplesAll which I have downloaded several times most recently in April 2007.

I'm running Visual Studio 2005 Standard Edition SP1

Robert Wishlaw

Non-release version (600) is not supported by this version of SQL Server

I downloaded the 101 Samples installation (i.e. 101SamplesCS.msi) from Microsoft's website, which contains SQL Server database files.

While working with the databases in the "Data Access" samples from the 101 Samples projects, I get the following error:

"Database <mdf_name> cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database..."

I assume the database files were created with a beta or CTP version of SQL Server 2005. Does anyone know where I can get an updated version of these database files or scripts to update them?

Please help.

Mike.

I was getting this error back with the Beta versions when it was updated to Beta 2 and the last of the CTP. This generally means that the database file was created with an older version of the express products and can not go through the automatic upgrade processes. To fix it I had to get the SQL Scripts and re create the database structure.

If you have a look at the mdf name and post that we might be able to direct you to the latest versions, but you might find that if you download the latest samples from the downloads site for the SQL Express product you might get the file you need. More then likley it will be the northwind, pups or adventure works database.

|||

the error is caused by having a database that is an older format, usually beta. I believe the 101 Code Samples have been updated recently to contain release format databases. You can find the download link at http://msdn.microsoft.com/vstudio/downloads/101samples/default.aspx.

Try installing a fresh copy of the samples and see if they work. I can confirm the 'Multiple Active ResultSets' sample is able to load the database without a problem.

Mike

|||

I am having an identical problem with the plants database - used in the XPath/XSLT data sample - I've tried reloading the plants.mdf from both the latest c# & VB.net sample on the VS 2005 101 samples with no luck. The actual error is

Database '9D5A6E69A8EF2325FC6D4339CED21FB2_CS101SAMPLESDATAACCESS\XPATH AND XSLT TRANSFORMATIONS ENHANCEMENTS\XPATHXSL\DATABASE\PLANTS.MDF' cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

|||

I would post this issue to the Feedback Center (http://connect.microsoft.com) for Visual Studio, since this is there sample. VS will need to update the sample as opening the file would require a pre-release build of SQL Server 2005 that is not available.

Regards,

Mike

|||

I note I can work around this by using the equivalent PlantInfo table in the C:\Documents and Settings\dlaub\My Documents\MSDN\CS101SamplesAll\CS101SamplesDataAccess\Using the XML data type with SQL Server 2005\XMLDataType\Database\XMLData.mdf database

You have to tweak the source code a bit (different database name, table name, column names)

And you just create a new table in ANY sql 2005 database - all this table has is an identity column and a xml column

|||

This problem

"cannot be upgraded because its non-release version (600) is not
supported by this version of SQL Server. You cannot open a database that
is incompatible with this version of sqlservr.exe. You must re-create
the database."

still exists in the testdata.mdf database (dated 2006-02-20) shipped with the 'DataReader vs. DataSet comparision' example in the CS101SamplesDataAccess folder of the CS101SamplesAll which I have downloaded several times most recently in April 2007.

I'm running Visual Studio 2005 Standard Edition SP1

Robert Wishlaw

Non-queried default values not working

This is driving me crazy. I can't get non-queried default values to
work with non-queried string report parameters. I'm using Reporting
Services 2000. Here's a test case: I have a simple Report Parameter: a
string, called "Color". It has three non-queried label-value pairs:
Red-red, Green-green, Blue-blue. I then try to define a default
non-queried value of green. Run the report, it always says "Select a
value". If I change the label-values to single-digit values, like
this: Red-1, Green-2, Blue-3, and set the default value to 2, then
Green comes up as the selected value (which is what I want). But I
cannot use a string for the value - it never matches. I've also tried
two-digit numbers: Red-11, Green-22, Blue-33, and used 22 as the
default. Comes up with "Select a value".
Can you not use strings as default values?Found the answer myself. Yes, you can use strings as default values,
as you would expect. Everything worked great in the Preview pane of
Visual Studio (the defaults would show properly) but when I put the
report on the report server, the dropdowns would say "Select a value".
To fix it, I had to completely delete the report from the server, and
upload a brand new copy of the RDL file. It did not work to simply use
the "Update" option in the Report Properties web page on the server.
It would update the report (i.e., I could see other changes I made) but
for some reason, the parameters would not work properly until I
completed deleted the report and uploaded it again. Go figure.

NONEMPTYCROSSJOIN : how does it work with linked measure groups ?

Hi,

I am experiencing a strange behaviour with both NONEMPTYCROSSJOIN() and NON(CROSSJOIN()) Mdx statements when working with linked measure groups.

Bascially, I have created a very simple "regular" MOLAP cube on SSAS2005 with one fact table and 3 dimensions. I have one single Measure Group for this cube.

Now, if I create an empty cube (no data source view) and add the measure group of my first cube as a Linked Measure Group, it seems like the Mdx function NonEmptyCrossJoin() never returns anything whereas it does on the underlying cube.

Same problem occurs when using NonEmpty(CrossJoin()).

Is there anything I need to parameter to have the NonEmpty baheviour work correclty on the linked measure group ? I've read there's a "NonEmpty behaviour" parameter for calculated members, but I'm not even using calculated measures.

Is such a behaviour a known issue ?

Do you need a more detailed example? if so, on which database ?

To give some backgroung, I have first noticed this problem after migrating a virtual cube from SSAS 2000.

Many thanks in advance

Rgds,

Francois

I eventually found an answer to this.

There was obviously some type of confusion in the migration of the virtual cube (which is based in SSAS2000 on 3 physical cubes) because not all of the sub cubes' dimensions where used.

I have recreated by linked measure groups manually, removed the unused dimension and the nonemptycrossjoin works fine now.

Hope that helps anyone having the same type of problem.

- F.

Nonempty Problem

following query is nt working properly. need a solution (need to filter the non empty rows)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
non empty CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]NonEmpty({filter(CROSSJOIN([CustomTimeSet],[GeneralLedgerSet]),[Measures].[MdaCodeTotal] <> 0 )})on rows

I am a bit suspicious about the [Measures].[Description] measure. Is this a calculated measure? It could be what is causing the non empty clause not to work. If you are using SSAS 2005, something like the following might work:

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
nonempty( CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) , {Measures.MdaCodeTotal})on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]

Note I am using the second parameter in the NonEmpty() function to set the measure context for the non empty evaluation.

Non-additive measure - Actual and Average

I am working on a project at a manufacturing client. The measure I'm having trouble with is LeadTime (the number of days from manufacturing schedule to completion). It is used in a calculation to determine over/under inventory levels.

I need it to use the actual LeadTime for the calc at the SKU level and the average LeadTime at all of the aggregate levels.

Thanks for the help,

Dave

Hello! I am not sure about what you mean with SKU level but if you have two dates in the fact table, ManufacturingScheduleDate and ManufacturingCompletionDate, you can make a named calculation in the data source view(Analysis Services 2005) or a calculation in the ETL-process with SSIS.

Use the TSQL function DATEDIFF() for that and the difference between the MScheduleDate and the MCompletionDate.

I am not sure about your problem with the averages and what you are doing averages of?

You should be able to solve this with the MDX AVG() function.

HTH

Thomas Ivarsson

|||

SKU is the lowest level of data...the individual product being manufactured and sold. The LeadTime does not need to be calculated it is already determined for each product.Here is a table that I hope helps:

LeadTime

Product

Actual

Average

1001

3

4

1002

4

4

1003

5

4

Sub-total Sum

12

Sub-total Avg

4

When I set the measure LeadTime to Sum the calculation that uses LeadTime is correct at the product level, but wrong at the aggregate level.If I set the measure to Average then the calc is wrong at the product and right at the aggregate level.

I need to show the cube data with LeadTime as actual at the product level and average at the aggregate level.

Dave

|||

Hello! Can you also indicate the expected values in the table for Actual and Average Lead time?

From what I have seen with other clients is that you have a manufacturing order id for each production of a product.

It is not a part of your table but can it be a part of the problem?

Regards

Thomas Ivarsson

|||

Here is a little description of the table from my last post.Three product ID’s (1001, 1002, 1003) with actual LeadTime for each product in days (3, 4, 5 – respectively).

The LeadTime measure is used with other measures (On-hand, Daily demand, Safety stock, etc.) in an inventory over/under calculation.But, LeadTime is the only non-additive measure.Because of that the calculation is correct at the Product ID level (3, 4, 5), but wrong at the aggregate level (12).If I set the measure to Average LeadTime at the Product level is wrong (4, 4, 4), but the aggregate is correct (4).I’m looking for a solution that gives me the correct calculation at all levels

This project is only dealing with inventory levels.Orders are not a part of the calculation except as a part of the Daily Demand figure.

NON_EMPTY_BEHAVIOR if NON EMPTY isn't used

A cube I'm working on has lots of calculations. But none of the MDX queries in the reports use the NON EMPTY clause or the NonEmpty function. What I'm wondering is whether the optimizer or any other part of the Formula Engine uses NON_EMPTY_BEHAVIOR for anything else besides removing empty cells from the resultset.

If the Sum function is summing two calculated measures, will a NEB on those two calculated measures help the Sum function, for example? That's the kind of thing I'm looking for... places where NEB is used other than removing entire cells from the resultset.

Yes, NON_EMPTY_BEHAVIOR is used even if there are no NON EMPTY queries. Some more information about it here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx|||

Mosha, thanks for the reply.

The takeaway from your blog regarding NEBs is that the multiplication operator (as in the * operator used for math, not for crossjoin) uses the NEB. Are there any others?

What I was hoping for was sort of a list of places where NEB's are used unless that's an enormous list.

|||

Multiplication was one of the examples. Many other (but not all) MDX functions also take it into account. The exact list is not easy to build, because there are some other additional factors affecting whether or not NEB will be used.

The most important thing though, is to ensure that NEB is used correctly, before thinking about perf. Even in this forum, we routinely see people using NEB incorrectly, which can and does cause wrong results.

no working drilldown in reportViewer, ASP.NET

I have Report A and report B.
Report A has under "navigation->Jump To Report", report B selected from the
list. I also used the parameters button to insert both parameters required
by Report B.
Deploy both reports.
On the catalog (server/reports/<folder>/A), I run the report A and can drill
to report B.
PROBLEM:
When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
reportViewer (report viewer) in my asp.net page to show the report. When I
drill down on report A, the frame of the report seems to be reloading (get
the green thing) but then the same reports appears!
Additional info: I tried adding to my asp.net page code a handler for the
ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
function. But when I put a breakpoint there, it seems I don't even get
there. In any case, all the samples I saw use this function for windows
forms, and then they set the datasource which seems like something I
shouldn't be bothered with.
anyone can help?
regards.I am facing the same problem, if you got the solution pls let me know. Thanx
in advance.
Regards
"csmba" wrote:
> I have Report A and report B.
> Report A has under "navigation->Jump To Report", report B selected from the
> list. I also used the parameters button to insert both parameters required
> by Report B.
> Deploy both reports.
> On the catalog (server/reports/<folder>/A), I run the report A and can drill
> to report B.
> PROBLEM:
> When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
> reportViewer (report viewer) in my asp.net page to show the report. When I
> drill down on report A, the frame of the report seems to be reloading (get
> the green thing) but then the same reports appears!
> Additional info: I tried adding to my asp.net page code a handler for the
> ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
> function. But when I put a breakpoint there, it seems I don't even get
> there. In any case, all the samples I saw use this function for windows
> forms, and then they set the datasource which seems like something I
> shouldn't be bothered with.
> anyone can help?
> regards.
>
>|||We finally got the solution.
Pls check whether the report A is called only once, use postback.
"csmba" wrote:
> I have Report A and report B.
> Report A has under "navigation->Jump To Report", report B selected from the
> list. I also used the parameters button to insert both parameters required
> by Report B.
> Deploy both reports.
> On the catalog (server/reports/<folder>/A), I run the report A and can drill
> to report B.
> PROBLEM:
> When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
> reportViewer (report viewer) in my asp.net page to show the report. When I
> drill down on report A, the frame of the report seems to be reloading (get
> the green thing) but then the same reports appears!
> Additional info: I tried adding to my asp.net page code a handler for the
> ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
> function. But when I put a breakpoint there, it seems I don't even get
> there. In any case, all the samples I saw use this function for windows
> forms, and then they set the datasource which seems like something I
> shouldn't be bothered with.
> anyone can help?
> regards.
>
>

No SS2005 Express

I have SS 2005 express* working(yrs) on a XP home ver. laptop. I got a new Acer w/ Vista hm/premium and I registered/can't get SS 2005 express* to download from MSDN.

Norton's gives a file block, I click the 'download file' and nothing happens. No dialog, hd drv activity, nothing - just 'Done'

on IE7 status. I have Visual Web Developer beta v2 2008** running on the Acer and don't want to trash the install.

.NET fwrk v2.0 is listed required for SS2005, but .NET fwrk v3.5 says it includes v2.0 + WCF,...(right? - install both?)

I need a SS for VWD 2008. I've ask/tried several times. What am I doing wrong?

Mark

There are no blocks of any kind on the site that would prevent you from downloading the installer package for SQL Express, it's just a simple file download. The download does not check for anything at all. You should investigate if you have anything configured about Norton or Windows (IE?) that is stopping the download of certain types of files.

Mike

No SS2005 Express

I have SS 2005 express* working(yrs) on a XP home ver. laptop. I got a new Acer w/ Vista hm/premium and I registered/can't get SS 2005 express* to download from MSDN.

Norton's gives a file block, I click the 'download file' and nothing happens. No dialog, hd drv activity, nothing - just 'Done'

on IE7 status. I have Visual Web Developer beta v2 2008** running on the Acer and don't want to trash the install.

.NET fwrk v2.0 is listed required for SS2005, but .NET fwrk v3.5 says it includes v2.0 + WCF,...(right? - install both?)

I need a SS for VWD 2008. I've ask/tried several times. What am I doing wrong?

Mark

There are no blocks of any kind on the site that would prevent you from downloading the installer package for SQL Express, it's just a simple file download. The download does not check for anything at all. You should investigate if you have anything configured about Norton or Windows (IE?) that is stopping the download of certain types of files.

Mike