basic authentication with olap failing

Hi,
I've got a situation where I am trying to connect from my local PC to
a windows 2003 server that is running SQL Server2k EE with Analysis
Services and IIS.
I have followed a number of Microsoft articles for configuring http
access to the Analysis Services, and my msolap.asp file is located
correctly and I have tested that I can access it with both anonymous
and basic authentication enabled on the site. The connection string I
am using is pointing to the correct directory for my msolap.asp file.
I can type the url to my msolap.asp file into my browser and do not
get any errors, both on anonymous or basic authentication access (I
need to have basic authentication).
The problem lies in the fact that when I try and access my web site
pages that contain the OWC components, if I turn on basic
authentication on msolap.asp I get unspecified errors and cannot load
data. If I leave the msolap.asp file as anonymous access and my web
site virtual directory with basic authentication, I am promted to
login and the data displays correctly. However, if I refresh the
page, I am prompted to login again - which should not be happening.
The frustrating thing is, if I remove the dynamic OWC component from
my page but leave the static OWC component, then I do not get prompted
to log in multiple times and it behaves as it should.
I know I've outlined a few problems in this post, but I'm pretty new
to OLAP and I'm just trying to get it running on a server.
Thanks in advanceTo clear up a few items, it appears there is no impact of the OWC
component being dynamic or static, as either one of them results in
multiple login prompts. If I disable basic authentication on
msolap.asp, then the strange behaviour I notice is that when I have the
site marked as "Trusted" in my browser, I am prompted to login on first
page access (which is correct), but then prompted to login again on any
hyperlink click on the page or on a page refresh. If I remove the
"Trusted" status, then I am only prompted to login on first access to
the site and on any subsequent refresh of the page in the browser.
Is this the correct behaviour for OWC/OLAP components?
Thankssql

bar chart problem

I have read through all posts regarding 'bar chart" or "column chart", but
did not find simliar situation which would resolve my problem, although it
looks simple.
A table/dataset cotains two columns, first ProductID, second
SalesAmountPercentageChange. I linked the bar chart with the dataset, I added
in "values" =!Field.SalesAmountPercentageChange, I added in "series groups"
=!Fields.ProductID. This way, I can have the bars displayed in different
color, which would be the same color if I add the !Fileds.ProductID into
"Category group".
Now the trouble is this, although the SalesAmoutPercentageChange is SAME for
different ProductID ( I have 5 product with 39% change for each) as designed,
the bar length VARIES (!!!) for different ProductID, which does not make
sense. Also, there are five 39% change corresponding to 5 products spread out
along the horizontal-axis (in my opinion should be named as X-axis, yet funny
enough it is actually Y-axis in RS).
I am quite confused. I tried many different combinations, turning things on
and off, still can not make it work. Any help will be greatly appreciated.
Also, I am trying to add into the bar chart another element
"TotalPercentageChange", not sure how to do that. It looks I can not simply
add a value2 on top of the value "!Field.SalesAmountPercentageChange". I
figured that it is necessary to pass a new dataset into chart, this new
dataset would contain the total + original table. Total is something
accessible in the report scope, the question is, where does it reside, how
can I use select statement to pick it out and combine it with original table
into a new one?
$10 for solving this problem, just give me your paypal account :).
--
SincerelyDid you get this resolved?
If not, can you post your RDL? In addition, if you have SP1 installed, there
should be a data cache file (.rdl.data) in the same directory as your .rdl
file. The .rdl.data would show the actual data values you are using.
Alternatively, it would help if you can reproduce the issue with the sample
databases (Northwind or Adventure Works) and post the corresponding RDL
file.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:247C97F2-9783-45C2-8ACA-E085075AFBDE@.microsoft.com...
> I have read through all posts regarding 'bar chart" or "column chart", but
> did not find simliar situation which would resolve my problem, although it
> looks simple.
> A table/dataset cotains two columns, first ProductID, second
> SalesAmountPercentageChange. I linked the bar chart with the dataset, I
added
> in "values" =!Field.SalesAmountPercentageChange, I added in "series
groups"
> =!Fields.ProductID. This way, I can have the bars displayed in different
> color, which would be the same color if I add the !Fileds.ProductID into
> "Category group".
> Now the trouble is this, although the SalesAmoutPercentageChange is SAME
for
> different ProductID ( I have 5 product with 39% change for each) as
designed,
> the bar length VARIES (!!!) for different ProductID, which does not make
> sense. Also, there are five 39% change corresponding to 5 products spread
out
> along the horizontal-axis (in my opinion should be named as X-axis, yet
funny
> enough it is actually Y-axis in RS).
> I am quite confused. I tried many different combinations, turning things
on
> and off, still can not make it work. Any help will be greatly appreciated.
> Also, I am trying to add into the bar chart another element
> "TotalPercentageChange", not sure how to do that. It looks I can not
simply
> add a value2 on top of the value "!Field.SalesAmountPercentageChange". I
> figured that it is necessary to pass a new dataset into chart, this new
> dataset would contain the total + original table. Total is something
> accessible in the report scope, the question is, where does it reside,
how
> can I use select statement to pick it out and combine it with original
table
> into a new one?
> $10 for solving this problem, just give me your paypal account :).
>
>
> --
> Sincerely|||If the <Min></Min> element is missing in the RDL file, then the chart will
use the "autoscale" mode. I.e. the minimum value of all datapoints in the
chart will become the minimum value for the y-axis.
If a Min element is present (but the content is not a valid number), then
the chart control will not use the autoscale mode.
Note: removing a previously specified Min-value in the chart properties
dialog in report designer will only remove the Min element in the underlying
RDL file if the Min-field in the dialog is completely cleared out before
hitting OK.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:E59D78EF-7B4C-4B33-8C3E-3C5D649674DF@.microsoft.com...
> This is an absolute bug. I digged more.
> I made a new chart, adding data set, then value, then series, and it
works,
> But the old chart with problem is still there. I compared the xml section
for
> these two charts side by side, line by line.
> I found only one line of difference, the working chart's rdl has <Min>
> </Min>, the other does not. I went to the report layout and accessed the
> chart property of not working one. in "Y axis" --> "Scale" --> "minimum",
I
> typed in a space, (any other character works two), then it works. all bar
> length goes back to normal. If I do not type anything, then it won't work.
> I believe under the hood, there is no default setting for min, and this
> causes serious problem.
> I am going to reproduce this problem in Northwind db and post the rdl. I
> hope it will be reproduced soon.
> By far, this is a bug in bar chart, I am pretty sure.
>
> "Robert Bruckner [MSFT]" wrote:
> > Did you get this resolved?
> > If not, can you post your RDL? In addition, if you have SP1 installed,
there
> > should be a data cache file (.rdl.data) in the same directory as your
.rdl
> > file. The .rdl.data would show the actual data values you are using.
> > Alternatively, it would help if you can reproduce the issue with the
sample
> > databases (Northwind or Adventure Works) and post the corresponding RDL
> > file.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
> > news:247C97F2-9783-45C2-8ACA-E085075AFBDE@.microsoft.com...
> > >
> > > I have read through all posts regarding 'bar chart" or "column chart",
but
> > > did not find simliar situation which would resolve my problem,
although it
> > > looks simple.
> > >
> > > A table/dataset cotains two columns, first ProductID, second
> > > SalesAmountPercentageChange. I linked the bar chart with the dataset,
I
> > added
> > > in "values" =!Field.SalesAmountPercentageChange, I added in "series
> > groups"
> > > =!Fields.ProductID. This way, I can have the bars displayed in
different
> > > color, which would be the same color if I add the !Fileds.ProductID
into
> > > "Category group".
> > >
> > > Now the trouble is this, although the SalesAmoutPercentageChange is
SAME
> > for
> > > different ProductID ( I have 5 product with 39% change for each) as
> > designed,
> > > the bar length VARIES (!!!) for different ProductID, which does not
make
> > > sense. Also, there are five 39% change corresponding to 5 products
spread
> > out
> > > along the horizontal-axis (in my opinion should be named as X-axis,
yet
> > funny
> > > enough it is actually Y-axis in RS).
> > >
> > > I am quite confused. I tried many different combinations, turning
things
> > on
> > > and off, still can not make it work. Any help will be greatly
appreciated.
> > >
> > > Also, I am trying to add into the bar chart another element
> > > "TotalPercentageChange", not sure how to do that. It looks I can not
> > simply
> > > add a value2 on top of the value "!Field.SalesAmountPercentageChange".
I
> > > figured that it is necessary to pass a new dataset into chart, this
new
> > > dataset would contain the total + original table. Total is something
> > > accessible in the report scope, the question is, where does it reside,
> > how
> > > can I use select statement to pick it out and combine it with original
> > table
> > > into a new one?
> > >
> > > $10 for solving this problem, just give me your paypal account :).
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Sincerely
> >
> >
> >

backupset

Here is my situation:
I have a stored procedure that runs full, differential and
log backups for a particular database.
Each backup has unique name based on the type, day of the
week and the time (e.g. Full_Backup_Monday_12-30).
Thus, backups are kept for one week.
When I review the backup set table, I see the name of my
backup, but in many cases I see an old date for the backup
and start and completion dates. However, when I review
the server, the newer backups are present.
My questions are:
Why isn't the backupset table being updated with the most
recent information?
When is the backupset table updated?
Thanks,
MichaelHi Michael,
Could you create a database backup as following steps?
1. Expand a server group, and then expand a server.
2. Expand databases, right-click the database, point to all tasks, and then
click backup database
3. Type the backup set name in the Name box and Select Database -complete
4. Under Destination, click Tape or Disk, and then specify a backup
destination.
If no backup destinations appear, click Add to add an existing backup
device or to create a new one.
5. Click Ok to create (Don't select the Schedule check box)
6. Check to see the backupset table again.
Is the backupset table updated immediately?
According to my test, the backupset table is updated on my side. When a
real backup finishes, a new record will be inserted in the table. Does it
work on your side? Generally, we do not recommended to directly query
system table. Could you tell me your detailed scenario?
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
Michael Shao
Microsoft Support Engineer
| Content-Class: urn:content-classes:message
| From: "Michael" <michael_schall@.unionsanitary.com>
| Sender: "Michael" <michael_schall@.unionsanitary.com>
| Subject: backupset
| Date: Mon, 7 Jul 2003 16:51:46 -0700
| Lines: 22
| Message-ID: <06c801c344e2$b9f90bf0$a301280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNE4rn5YC8MduACSZi2OM0YFuqZFQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23185
| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here is my situation:
|
| I have a stored procedure that runs full, differential and
| log backups for a particular database.
|
| Each backup has unique name based on the type, day of the
| week and the time (e.g. Full_Backup_Monday_12-30).
| Thus, backups are kept for one week.
|
| When I review the backup set table, I see the name of my
| backup, but in many cases I see an old date for the backup
| and start and completion dates. However, when I review
| the server, the newer backups are present.
|
| My questions are:
| Why isn't the backupset table being updated with the most
| recent information?
|
| When is the backupset table updated?
|
| Thanks,
| Michael
|

Backup-Questions about Logshipping

Hi,
I am new on this topic and I allready use google. I have some questions
about Backup a Server that is Logshipping.
Situation:
2 SQL 2000 Server
Logshipping a 100GB DB from Server 1 to Server 2. --> it's working!
Questions:
I need all databases (System-DBs and the Logshipped) on my backup.
What is possible to backup and on witch way (Full, Copy,..)?
Is there a way to truncate the Logs of the system-DBs?
Thanks a lot for helping me.
RalphRalph
Do yopu want all databases including system to be copied (.BAK) on remote
server?
Backup all system databases and use COPY of the DOS function to copy the
files on remote server
"Ralph" <Ralph@.discussions.microsoft.com> wrote in message
news:6BFF8189-DC17-404C-B274-88E3EFA2FA50@.microsoft.com...
> Hi,
> I am new on this topic and I allready use google. I have some questions
> about Backup a Server that is Logshipping.
> Situation:
> 2 SQL 2000 Server
> Logshipping a 100GB DB from Server 1 to Server 2. --> it's working!
> Questions:
> I need all databases (System-DBs and the Logshipped) on my backup.
> What is possible to backup and on witch way (Full, Copy,..)?
> Is there a way to truncate the Logs of the system-DBs?
> Thanks a lot for helping me.
> Ralph|||Hi Uri
I would like to backup the System-Databases in SQL. Like a fullbackup.
not copy in dos or windows.
Thanks for the fast reply.
"Uri Dimant" wrote:
> Ralph
> Do yopu want all databases including system to be copied (.BAK) on remote
> server?
> Backup all system databases and use COPY of the DOS function to copy the
> files on remote server
>
> "Ralph" <Ralph@.discussions.microsoft.com> wrote in message
> news:6BFF8189-DC17-404C-B274-88E3EFA2FA50@.microsoft.com...
> > Hi,
> >
> > I am new on this topic and I allready use google. I have some questions
> > about Backup a Server that is Logshipping.
> >
> > Situation:
> > 2 SQL 2000 Server
> > Logshipping a 100GB DB from Server 1 to Server 2. --> it's working!
> >
> > Questions:
> > I need all databases (System-DBs and the Logshipped) on my backup.
> > What is possible to backup and on witch way (Full, Copy,..)?
> > Is there a way to truncate the Logs of the system-DBs?
> >
> > Thanks a lot for helping me.
> >
> > Ralph
>
>|||Ralph
Take a look at Maintanace Plan to create backup for system (and not only)
databases
"Ralph" <Ralph@.discussions.microsoft.com> wrote in message
news:27757BB3-4DE7-4724-88E5-2D9E4F619C3B@.microsoft.com...
> Hi Uri
> I would like to backup the System-Databases in SQL. Like a fullbackup.
> not copy in dos or windows.
> Thanks for the fast reply.
> "Uri Dimant" wrote:
>> Ralph
>> Do yopu want all databases including system to be copied (.BAK) on remote
>> server?
>> Backup all system databases and use COPY of the DOS function to copy the
>> files on remote server
>>
>> "Ralph" <Ralph@.discussions.microsoft.com> wrote in message
>> news:6BFF8189-DC17-404C-B274-88E3EFA2FA50@.microsoft.com...
>> > Hi,
>> >
>> > I am new on this topic and I allready use google. I have some questions
>> > about Backup a Server that is Logshipping.
>> >
>> > Situation:
>> > 2 SQL 2000 Server
>> > Logshipping a 100GB DB from Server 1 to Server 2. --> it's working!
>> >
>> > Questions:
>> > I need all databases (System-DBs and the Logshipped) on my backup.
>> > What is possible to backup and on witch way (Full, Copy,..)?
>> > Is there a way to truncate the Logs of the system-DBs?
>> >
>> > Thanks a lot for helping me.
>> >
>> > Ralph
>>|||tempdb: Can't backup - no reason to.
master - can only do db backup (can't do log backup)
model - can do log backup, but overkill
msdb - can do log backup, might be overkill. However Agent sets it to simple recovery at startup.
As long as a db is in simple recovery then there is no reason to "truncate the log" for that db.
Restoring the system db's are not that easy, though... For instance a good start is KB 227071.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ralph" <Ralph@.discussions.microsoft.com> wrote in message
news:6BFF8189-DC17-404C-B274-88E3EFA2FA50@.microsoft.com...
> Hi,
> I am new on this topic and I allready use google. I have some questions
> about Backup a Server that is Logshipping.
> Situation:
> 2 SQL 2000 Server
> Logshipping a 100GB DB from Server 1 to Server 2. --> it's working!
> Questions:
> I need all databases (System-DBs and the Logshipped) on my backup.
> What is possible to backup and on witch way (Full, Copy,..)?
> Is there a way to truncate the Logs of the system-DBs?
> Thanks a lot for helping me.
> Ralph

Backup using GZIP

I'm sure I'm not the only one in this situation: out of disk space on
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_b
logpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11
_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.

Backup using GZIP

I'm sure I'm not the only one in this situation: out of disk space on
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.

Backup to file - why NOFORMAT and NOSKIP?

What good would the NOFORMAT and NOSKIP options be on a scheduled backup to
a file?
I'm coming into this situation later in the game, but a third party had set
up jobs in SQL Server to back up the databases on a server, using the
commands:
BACKUP DATABASE [dbname] TO DISK = N'D:\backup\DBNAME.BAK'
WITH INIT, NOUNLOAD, NAME = N'PRODUCTION backup', NOSKIP , STATS = 10, NOFORMAT
Each backup goes to a different file. It is then backed up to tape, but not
deleted from the hard drive.
Recently, the drive where the backups were written ran out of disk space and
it appears that the DBNAME.BAK file was (apparently) corrupted. The backup
command would no longer run after that happened, terminating with an error -
and of course, no one was checking the SQL Server logs until I happened to
look at it for another purpose a week later. The error was due to some
problem with the header in the .BAK file, and it was looking at the header
because of the NOFORMAT and NOSKIP options. Deleting the corrupted .BAK
file got the backup working again.
It would appear to me that the NOSKIP and NOFORMAT options serve no purpose
if we want it to overwrite the backup file each time no matter what. Am I
correct or am I missing something?Hi,
NOFORMAT and NOSKIP are used when you backup directly to tape. For disk file
backup both the options are not required.
WITH INIT option in BACKUP DATABASE command is used to overwrite the backup
file.
Thanks
Hari
SQL Server MVP
"JJ" <jjjj@.newsgroup.nospam> wrote in message
news:e%23K9t$XkFHA.2156@.TK2MSFTNGP14.phx.gbl...
> What good would the NOFORMAT and NOSKIP options be on a scheduled backup
> to a file?
> I'm coming into this situation later in the game, but a third party had
> set up jobs in SQL Server to back up the databases on a server, using the
> commands:
> BACKUP DATABASE [dbname] TO DISK = N'D:\backup\DBNAME.BAK'
> WITH INIT, NOUNLOAD, NAME = N'PRODUCTION backup', NOSKIP , STATS => 10, NOFORMAT
> Each backup goes to a different file. It is then backed up to tape, but
> not deleted from the hard drive.
> Recently, the drive where the backups were written ran out of disk space
> and it appears that the DBNAME.BAK file was (apparently) corrupted. The
> backup command would no longer run after that happened, terminating with
> an error - and of course, no one was checking the SQL Server logs until I
> happened to look at it for another purpose a week later. The error was
> due to some problem with the header in the .BAK file, and it was looking
> at the header because of the NOFORMAT and NOSKIP options. Deleting the
> corrupted .BAK file got the backup working again.
> It would appear to me that the NOSKIP and NOFORMAT options serve no
> purpose if we want it to overwrite the backup file each time no matter
> what. Am I correct or am I missing something?
>|||Hello,
You can refer to the topic BACKUP in SQL server Books Online(BOL):
--
NOFORMAT
Specifies the media header should not be written on all volumes used for
this backup operation and does not rewrite the backup device unless INIT is
specified.
INIT
Specifies that all backup sets should be overwritten, but preserves the
media header. If INIT is specified, any existing backup set data on that
device is overwritten.
The backup media is not overwritten if any one of the following conditions
is met:
** All backup sets on the media have not yet expired. For more
information, see the EXPIREDATE and RETAINDAYS options.
***The backup set name given in the BACKUP statement, if provided, does
not match the name on the backup media. For more information, see the NAME
clause.
Use the SKIP option to override these checks. For more information
about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the
Remarks section.
Note If the backup media is password protected, SQL Server does not write
to the media unless the media password is supplied. This check is not
overridden by the SKIP option. Password-protected media may be overwritten
only by reformatting it. For more information, see the FORMAT option.
NOSKIP
Instructs the BACKUP statement to check the expiration date of all backup
sets on the media before allowing them to be overwritten.
Fore more inforamtion, refer to the BACKUP topic in BOL.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Backup to file - why NOFORMAT and NOSKIP?

What good would the NOFORMAT and NOSKIP options be on a scheduled backup to
a file?
I'm coming into this situation later in the game, but a third party had set
up jobs in SQL Server to back up the databases on a server, using the
commands:
BACKUP DATABASE [dbname] TO DISK = N'D:\backup\DBNAME.BAK'
WITH INIT, NOUNLOAD, NAME = N'PRODUCTION backup', NOSKIP , STATS =
10, NOFORMAT
Each backup goes to a different file. It is then backed up to tape, but not
deleted from the hard drive.
Recently, the drive where the backups were written ran out of disk space and
it appears that the DBNAME.BAK file was (apparently) corrupted. The backup
command would no longer run after that happened, terminating with an error -
and of course, no one was checking the SQL Server logs until I happened to
look at it for another purpose a week later. The error was due to some
problem with the header in the .BAK file, and it was looking at the header
because of the NOFORMAT and NOSKIP options. Deleting the corrupted .BAK
file got the backup working again.
It would appear to me that the NOSKIP and NOFORMAT options serve no purpose
if we want it to overwrite the backup file each time no matter what. Am I
correct or am I missing something?
Hi,
NOFORMAT and NOSKIP are used when you backup directly to tape. For disk file
backup both the options are not required.
WITH INIT option in BACKUP DATABASE command is used to overwrite the backup
file.
Thanks
Hari
SQL Server MVP
"JJ" <jjjj@.newsgroup.nospam> wrote in message
news:e%23K9t$XkFHA.2156@.TK2MSFTNGP14.phx.gbl...
> What good would the NOFORMAT and NOSKIP options be on a scheduled backup
> to a file?
> I'm coming into this situation later in the game, but a third party had
> set up jobs in SQL Server to back up the databases on a server, using the
> commands:
> BACKUP DATABASE [dbname] TO DISK = N'D:\backup\DBNAME.BAK'
> WITH INIT, NOUNLOAD, NAME = N'PRODUCTION backup', NOSKIP , STATS =
> 10, NOFORMAT
> Each backup goes to a different file. It is then backed up to tape, but
> not deleted from the hard drive.
> Recently, the drive where the backups were written ran out of disk space
> and it appears that the DBNAME.BAK file was (apparently) corrupted. The
> backup command would no longer run after that happened, terminating with
> an error - and of course, no one was checking the SQL Server logs until I
> happened to look at it for another purpose a week later. The error was
> due to some problem with the header in the .BAK file, and it was looking
> at the header because of the NOFORMAT and NOSKIP options. Deleting the
> corrupted .BAK file got the backup working again.
> It would appear to me that the NOSKIP and NOFORMAT options serve no
> purpose if we want it to overwrite the backup file each time no matter
> what. Am I correct or am I missing something?
>
|||Hello,
You can refer to the topic BACKUP in SQL server Books Online(BOL):
NOFORMAT
Specifies the media header should not be written on all volumes used for
this backup operation and does not rewrite the backup device unless INIT is
specified.
INIT
Specifies that all backup sets should be overwritten, but preserves the
media header. If INIT is specified, any existing backup set data on that
device is overwritten.
The backup media is not overwritten if any one of the following conditions
is met:
** All backup sets on the media have not yet expired. For more
information, see the EXPIREDATE and RETAINDAYS options.
***The backup set name given in the BACKUP statement, if provided, does
not match the name on the backup media. For more information, see the NAME
clause.
Use the SKIP option to override these checks. For more information
about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the
Remarks section.
Note If the backup media is password protected, SQL Server does not write
to the media unless the media password is supplied. This check is not
overridden by the SKIP option. Password-protected media may be overwritten
only by reformatting it. For more information, see the FORMAT option.
NOSKIP
Instructs the BACKUP statement to check the expiration date of all backup
sets on the media before allowing them to be overwritten.

Fore more inforamtion, refer to the BACKUP topic in BOL.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Backup to file - why NOFORMAT and NOSKIP?

What good would the NOFORMAT and NOSKIP options be on a scheduled backup to
a file?
I'm coming into this situation later in the game, but a third party had set
up jobs in SQL Server to back up the databases on a server, using the
commands:
BACKUP DATABASE [dbname] TO DISK = N'D:\backup\DBNAME.BAK'
WITH INIT, NOUNLOAD, NAME = N'PRODUCTION backup', NOSKIP , STATS =
10, NOFORMAT
Each backup goes to a different file. It is then backed up to tape, but not
deleted from the hard drive.
Recently, the drive where the backups were written ran out of disk space and
it appears that the DBNAME.BAK file was (apparently) corrupted. The backup
command would no longer run after that happened, terminating with an error -
and of course, no one was checking the SQL Server logs until I happened to
look at it for another purpose a week later. The error was due to some
problem with the header in the .BAK file, and it was looking at the header
because of the NOFORMAT and NOSKIP options. Deleting the corrupted .BAK
file got the backup working again.
It would appear to me that the NOSKIP and NOFORMAT options serve no purpose
if we want it to overwrite the backup file each time no matter what. Am I
correct or am I missing something?Hi,
NOFORMAT and NOSKIP are used when you backup directly to tape. For disk file
backup both the options are not required.
WITH INIT option in BACKUP DATABASE command is used to overwrite the backup
file.
Thanks
Hari
SQL Server MVP
"JJ" <jjjj@.newsgroup.nospam> wrote in message
news:e%23K9t$XkFHA.2156@.TK2MSFTNGP14.phx.gbl...
> What good would the NOFORMAT and NOSKIP options be on a scheduled backup
> to a file?
> I'm coming into this situation later in the game, but a third party had
> set up jobs in SQL Server to back up the databases on a server, using the
> commands:
> BACKUP DATABASE [dbname] TO DISK = N'D:\backup\DBNAME.BAK'
> WITH INIT, NOUNLOAD, NAME = N'PRODUCTION backup', NOSKIP , STATS =
> 10, NOFORMAT
> Each backup goes to a different file. It is then backed up to tape, but
> not deleted from the hard drive.
> Recently, the drive where the backups were written ran out of disk space
> and it appears that the DBNAME.BAK file was (apparently) corrupted. The
> backup command would no longer run after that happened, terminating with
> an error - and of course, no one was checking the SQL Server logs until I
> happened to look at it for another purpose a week later. The error was
> due to some problem with the header in the .BAK file, and it was looking
> at the header because of the NOFORMAT and NOSKIP options. Deleting the
> corrupted .BAK file got the backup working again.
> It would appear to me that the NOSKIP and NOFORMAT options serve no
> purpose if we want it to overwrite the backup file each time no matter
> what. Am I correct or am I missing something?
>|||Hello,
You can refer to the topic BACKUP in SQL server Books Online(BOL):
NOFORMAT
Specifies the media header should not be written on all volumes used for
this backup operation and does not rewrite the backup device unless INIT is
specified.
INIT
Specifies that all backup sets should be overwritten, but preserves the
media header. If INIT is specified, any existing backup set data on that
device is overwritten.
The backup media is not overwritten if any one of the following conditions
is met:
** All backup sets on the media have not yet expired. For more
information, see the EXPIREDATE and RETAINDAYS options.
***The backup set name given in the BACKUP statement, if provided, does
not match the name on the backup media. For more information, see the NAME
clause.
Use the SKIP option to override these checks. For more information
about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the
Remarks section.
Note If the backup media is password protected, SQL Server does not write
to the media unless the media password is supplied. This check is not
overridden by the SKIP option. Password-protected media may be overwritten
only by reformatting it. For more information, see the FORMAT option.
NOSKIP
Instructs the BACKUP statement to check the expiration date of all backup
sets on the media before allowing them to be overwritten.
Fore more inforamtion, refer to the BACKUP topic in BOL.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.