How odd, strange behaviour

Dear folks,
I've got the following issue and I can't work out:
This statement fails:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET WITH (NOLOCK)
Servidor: mensaje 7377, nivel 16, estado 1, l_nea 2
Cannot specify an index or locking hint for a remote data source.
But this one, ends successful and pull up the data:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
Does anyone ever used or experienced any problem with that? are we talking
about the same or different goal with the different way? I mean, nolock, wit
h
nolock...
Thanks for any input and regards,You are defining just an alias here:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
Something like SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET SomeAlias
HTH, Jens Suessmeyer.|||sorry Jens, I don't understand you, what do you mean with that?
"Jens" wrote:

> You are defining just an alias here:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
>
> Something like SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET SomeAlias
>
> HTH, Jens Suessmeyer.
>|||Defining aliases for the tables often helps to clear queries due to
long names or names where you don=B4t know what the data is all about,
something like:
SELECT Address.*
FROM T3455NBGA Address --Normally this is an Adress table but you
don=B4t know from the name, so we give it an alias in the query
INNER JOIN SomeOtherTable
ON SOmeotherTable.ID =3D Address.ID
HTH, Jens Suessmeyer.|||Thanks in advance, anyway it doesn't works.
"Jens" wrote:

> Defining aliases for the tables often helps to clear queries due to
> long names or names where you don′t know what the data is all about,
> something like:
> SELECT Address.*
> FROM T3455NBGA Address --Normally this is an Adress table but you
> don′t know from the name, so we give it an alias in the query
> INNER JOIN SomeOtherTable
> ON SOmeotherTable.ID = Address.ID
> HTH, Jens Suessmeyer.
>|||> But this one, ends successful and pull up the data:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
So will this:

> SELECT NOLOCK.G61Num AS Curso,NOLOCK.G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
And this:

> SELECT bozo.G61Num AS Curso,bozo.G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET bozo
You would think that SQL Server would treat 'NOLOCK' as a reserved word, but
it doesn't.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:95D4CF5F-8E45-44E4-9F35-95F38A5286DA@.microsoft.com...
> Dear folks,
> I've got the following issue and I can't work out:
> This statement fails:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET WITH (NOLOCK)
>
> Servidor: mensaje 7377, nivel 16, estado 1, lnea 2
> Cannot specify an index or locking hint for a remote data source.
> But this one, ends successful and pull up the data:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
> Does anyone ever used or experienced any problem with that? are we talking
> about the same or different goal with the different way? I mean, nolock,
> with
> nolock...
> Thanks for any input and regards,|||Of course not. Because of how you placed NOLOCK in your code, it is simply
being used as an alternative table name, instead of the NOLOCK action that
you are trying to produce. The query that is working is not using NOLOCK as
you intend, but rather is using the term as a reference to your table. The
query that is failing is using NOLOCK as you intended, but SQL Server does
not permit you to use it in that context.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:5A50B17C-2705-4027-A5F6-24E025F0CD4F@.microsoft.com...
> Thanks in advance, anyway it doesn't works.
> "Jens" wrote:
>|||I should add that this is not your fault (IMO) since it is more than
reasonable to expect NOLOCK to be a reserved word. It is a simple syntax
error, and an honest mistake.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23PJDYbnJGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Of course not. Because of how you placed NOLOCK in your code, it is
simply
> being used as an alternative table name, instead of the NOLOCK action that
> you are trying to produce. The query that is working is not using NOLOCK
as
> you intend, but rather is using the term as a reference to your table.
The
> query that is failing is using NOLOCK as you intended, but SQL Server does
> not permit you to use it in that context.

How not to replicate update statement

Hi,
In a merge replicaton, how can I configure it so that the update statements
will not be replicated to subscriber database?
Thanks.
Pingx
This can't be done. On the subscriber side you can have permissions checked
for DML originating on the subscriber and then selectively modify
permissions of the PAL account so it can't perform this DML.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:9DD5A341-4608-4EC2-A30C-6F4896F5E8A4@.microsoft.com...
> Hi,
> In a merge replicaton, how can I configure it so that the update
> statements
> will not be replicated to subscriber database?
> Thanks.
> Pingx

How not to cache in Lookups to Oracle

Why can you not turn off the caching in a Lookup against Oracle?

I have an exceedingly complicated SQL statement like this -

SELECT OBJECT_ID, OBJECT_CODE FROM OBJECT_TABLE

If I turn off the cache for a lookup I get bombarded with this rubbish-

Error 8 Validation error. DFT Load STATUS: LKP Get RESULT_NO [128]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ". Update.dtsx 0 0

Error 9 Validation error. DFT Load DAY_STATUS: LKP Get RESULT_NO [128]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Update.dtsx 0 0

I have tried modifying the Cache SQL Statement as well, but to no avail. I am using the MSDAORA.1 provider against "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production".

Any ideas ?

I'm not a pro in Oracle, but just to make sure you are modifying the correct SQL statement: if you don't enable memory restrictions and lookup is in Fully cached mode, the SQL statement from first tab page is used. If you enable memory restrictions and the lookup is in Partial or No-cache mode, the caching SQL query from Advanced tab is used.

It looks like you want fully cached mode - so make sure the memory restrictions are not enabled (first checkbox on Advanced page should be clear).|||

Had this problem to,

solved when I used oraoledb.oracle.1 instead of msdaora.1

|||Where did you find this option?|||

J.A.J. wrote:

Where did you find this option?

Which option? The above post refers to a different OLE Oracle driver.|||Darren, did you ever get this resolved and if so how?|||

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

|||

J.A.J. wrote:

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

Looks like it's an Oracle provided OLE DB driver. You can check their Website.

How not to cache in Lookups to Oracle

Why can you not turn off the caching in a Lookup against Oracle?

I have an exceedingly complicated SQL statement like this -

SELECT OBJECT_ID, OBJECT_CODE FROM OBJECT_TABLE

If I turn off the cache for a lookup I get bombarded with this rubbish-

Error 8 Validation error. DFT Load STATUS: LKP Get RESULT_NO [128]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ". Update.dtsx 0 0

Error 9 Validation error. DFT Load DAY_STATUS: LKP Get RESULT_NO [128]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Update.dtsx 0 0

I have tried modifying the Cache SQL Statement as well, but to no avail. I am using the MSDAORA.1 provider against "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production".

Any ideas ?

I'm not a pro in Oracle, but just to make sure you are modifying the correct SQL statement: if you don't enable memory restrictions and lookup is in Fully cached mode, the SQL statement from first tab page is used. If you enable memory restrictions and the lookup is in Partial or No-cache mode, the caching SQL query from Advanced tab is used.

It looks like you want fully cached mode - so make sure the memory restrictions are not enabled (first checkbox on Advanced page should be clear).|||

Had this problem to,

solved when I used oraoledb.oracle.1 instead of msdaora.1

|||Where did you find this option?|||

J.A.J. wrote:

Where did you find this option?

Which option? The above post refers to a different OLE Oracle driver.|||Darren, did you ever get this resolved and if so how?|||

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

|||

J.A.J. wrote:

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

Looks like it's an Oracle provided OLE DB driver. You can check their Website.

How long is a SQL statement allowed to be?

I am trying to make the following SQL statement, but there seems to a limit
on how long a statement can be:

INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,
addressB, postal_number, city, country, home_phone, mobile_phone,
work_phone, fax, email, sale_procentage, bank, account_number,
creation_initials, creation_date, creation_reason) values ("test", "test",
"test", etc...);

But I can only enter this much text:

INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,
addressB, postal_number, city, country, home_phone, mobile_phone,
work_phone, fax, email, sale_procentage, bank, account_number,
creation_initials, creation_date, creation_reason) va

Is there some upper limit? And how do I make a long SQL statement like this?

JS"JS" <dsa.@.asdf.com> wrote in message news:d6ilga$rpj$1@.news.net.uni-c.dk...
>I am trying to make the following SQL statement, but there seems to a limit
> on how long a statement can be:
> INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,
> addressB, postal_number, city, country, home_phone, mobile_phone,
> work_phone, fax, email, sale_procentage, bank, account_number,
> creation_initials, creation_date, creation_reason) values ("test", "test",
> "test", etc...);
> But I can only enter this much text:
> INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,
> addressB, postal_number, city, country, home_phone, mobile_phone,
> work_phone, fax, email, sale_procentage, bank, account_number,
> creation_initials, creation_date, creation_reason) va
>
> Is there some upper limit? And how do I make a long SQL statement like
> this?
> JS

It sounds like you're using a window somewhere in Enterprise Manager? If so,
then just use Query Analyzer instead - it's a much better tool for
development and having precise control over what you're doing.

There is a maximum batch size for SQL commands, which depends on your
network packet size (see "Maximum Capacity Specifications" in Books Online),
but it's probably not a limit for most practical purposes.

Simon

how if esle statement works?

hi, everybody!

I have some problems with understanding howif esle statment in sql works. I'm trying to write my owncount()-function and its not working withif else :(( I've already implemented it withcase:
select sum(case (hiredate) when (null) then 0 else 1 end) as 'Count' from emp

It's working perfect. Now I want to have the same but usingif else:
select sum(
if( hiredate is null)
begin 0 end
else 1)
as 'Count' from emp

the error message is:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '0'.

What is wrong here?

Artur:

If / else does not work within the context of a select list like this; you need to implement as your first select statement with case statement or use a where condition such as something like:

select count(*)
from emp
where hiredate is not null

|||I think you are right, its not possible to use if/esle within select statement.
Thanks!!!
|||In SQL Server, IF - Else is a control flow statement that allows you to conditionaly run blocks of T-SQL code. Case is an expression that can be used inside some statements. SQL Server does not have a CASE Statement like some other languages.|||Artur,

You can have an 'if/then/else' statement within your code, it's just that you have to put 'case when' instead of 'if'. It works exactly the same way.

Try:

select sum(case when hiredate is null then 0 else 1 end) as 'Count'
from emp

Rob

How full backup works

Hello. Take this simple backup statement:
Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
With Name = 'MyDB full backup'
Would you think that if MyDB.bak already existed it would be overwritten
with the new backup? Or would it double in size, effectively containing 2
backups? I've seen it both ways I think. I ask because I'm trying to backup
this monolithic DB each night and we're running out of disk space on this
command.
I've seen some talk around here of backup compression utilities. In lieu of
that would it make sense to schedule simple batch job that deletes MyDB.bak
before proceeding with the backup? I realize we could be left vulnerable if
the subsequent backup should fail.
TIA,
KenKen,
Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
Not having a reliable backup scares me, so I hate to see you depending on
such an approach. Here are a couple of options:
1. Schedule a tape backup of your .BAK file during the day, between the
nightly backups.
2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH DIFFERENTIAL.
Then maybe you can fit a week's worth of backups on the disk. (Do the
database backup on the weekend if nothing much is happening then. Then, if
it fails, you can get a second shot at it before a lot of work happens.)
3. But better is to buy enough disk space for the needed backups. Use a
maintenance plan (whether SQL Server Maintenance Plan or one you roll
yourself) that will name each file with the datetime and a plan to delete
the backups that are older than you want to keep.
RLF
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||It won't work one way one time and a different way the next if the commands
are always the same. If you don't specify the INIT option the default is
NOINIT which means to append. Decide which way you need it to be and specify
it even if it is the default behavior so there is no mistake down the road.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||Hi there,
Default action of BACKUP command is "NOINIT" which appends to the existing
backup set. If you want to overwrite the backup set,
then you could use "WITH INIT" param.
In your case (to overwrite the existed backup set):
Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
With Name = 'MyDB full backup', INIT
Ekrem Ã?nsoy
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||Shoot, I glossed right over that param when looking at BOL. Thanks guys for
pointing it out. In fact I'm rolling my own to do what you suggest in #2, do
differentials 6 days a week and full on the 7th.
BTW, it's nice that SQL Server can do differential backups. having said that
they don't appear to be totally efficient. Each day our data stroe grows a
little but the differential backup grows a lot.
Ken
"Russell Fields" wrote:
> Ken,
> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
> Not having a reliable backup scares me, so I hate to see you depending on
> such an approach. Here are a couple of options:
> 1. Schedule a tape backup of your .BAK file during the day, between the
> nightly backups.
> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH DIFFERENTIAL.
> Then maybe you can fit a week's worth of backups on the disk. (Do the
> database backup on the weekend if nothing much is happening then. Then, if
> it fails, you can get a second shot at it before a lot of work happens.)
> 3. But better is to buy enough disk space for the needed backups. Use a
> maintenance plan (whether SQL Server Maintenance Plan or one you roll
> yourself) that will name each file with the datetime and a plan to delete
> the backups that are older than you want to keep.
> RLF
>
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> > Hello. Take this simple backup statement:
> >
> > Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> > With Name = 'MyDB full backup'
> >
> > Would you think that if MyDB.bak already existed it would be overwritten
> > with the new backup? Or would it double in size, effectively containing 2
> > backups? I've seen it both ways I think. I ask because I'm trying to
> > backup
> > this monolithic DB each night and we're running out of disk space on this
> > command.
> >
> > I've seen some talk around here of backup compression utilities. In lieu
> > of
> > that would it make sense to schedule simple batch job that deletes
> > MyDB.bak
> > before proceeding with the backup? I realize we could be left vulnerable
> > if
> > the subsequent backup should fail.
> >
> > TIA,
> > Ken
>
>|||Ken,
Differentials are cumulative. So, assuming a Sunday full backup and daily
differentials, the Wednesday differential contains the Monday, Tuesday, and
Wednesday changes. If you know that you never have to go back in time more
than a couple of days, you can start timing them out.
RLF
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:88947E5E-4D56-4CCA-9114-C934A18F14B0@.microsoft.com...
> Shoot, I glossed right over that param when looking at BOL. Thanks guys
> for
> pointing it out. In fact I'm rolling my own to do what you suggest in #2,
> do
> differentials 6 days a week and full on the 7th.
> BTW, it's nice that SQL Server can do differential backups. having said
> that
> they don't appear to be totally efficient. Each day our data stroe grows a
> little but the differential backup grows a lot.
> Ken
>
> "Russell Fields" wrote:
>> Ken,
>> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
>> Not having a reliable backup scares me, so I hate to see you depending on
>> such an approach. Here are a couple of options:
>> 1. Schedule a tape backup of your .BAK file during the day, between the
>> nightly backups.
>> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH
>> DIFFERENTIAL.
>> Then maybe you can fit a week's worth of backups on the disk. (Do the
>> database backup on the weekend if nothing much is happening then. Then,
>> if
>> it fails, you can get a second shot at it before a lot of work happens.)
>> 3. But better is to buy enough disk space for the needed backups. Use a
>> maintenance plan (whether SQL Server Maintenance Plan or one you roll
>> yourself) that will name each file with the datetime and a plan to delete
>> the backups that are older than you want to keep.
>> RLF
>>
>> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
>> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
>> > Hello. Take this simple backup statement:
>> >
>> > Backup Database MyDB To Disk =>> > N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
>> > With Name = 'MyDB full backup'
>> >
>> > Would you think that if MyDB.bak already existed it would be
>> > overwritten
>> > with the new backup? Or would it double in size, effectively containing
>> > 2
>> > backups? I've seen it both ways I think. I ask because I'm trying to
>> > backup
>> > this monolithic DB each night and we're running out of disk space on
>> > this
>> > command.
>> >
>> > I've seen some talk around here of backup compression utilities. In
>> > lieu
>> > of
>> > that would it make sense to schedule simple batch job that deletes
>> > MyDB.bak
>> > before proceeding with the backup? I realize we could be left
>> > vulnerable
>> > if
>> > the subsequent backup should fail.
>> >
>> > TIA,
>> > Ken
>>|||In addition to what Russell stated the Diff's backup at the Extent (64K)
level not the row. Meaning that if 1 bit changes on the extent you get the
entire extent in the Diff backup. So they can tend to be much larger than
the changes would imply.
If you are running out of disk space for backups I suggest you purchase one
of the 3rd party backup compression utilities. They start as low as a few
hundred $ and can compress the backups as much as 80% or more. Not to
mention they are generally faster than native backups as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23KNsytz6HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Ken,
> Differentials are cumulative. So, assuming a Sunday full backup and daily
> differentials, the Wednesday differential contains the Monday, Tuesday,
> and Wednesday changes. If you know that you never have to go back in time
> more than a couple of days, you can start timing them out.
> RLF
>
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:88947E5E-4D56-4CCA-9114-C934A18F14B0@.microsoft.com...
>> Shoot, I glossed right over that param when looking at BOL. Thanks guys
>> for
>> pointing it out. In fact I'm rolling my own to do what you suggest in #2,
>> do
>> differentials 6 days a week and full on the 7th.
>> BTW, it's nice that SQL Server can do differential backups. having said
>> that
>> they don't appear to be totally efficient. Each day our data stroe grows
>> a
>> little but the differential backup grows a lot.
>> Ken
>>
>> "Russell Fields" wrote:
>> Ken,
>> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
>> Not having a reliable backup scares me, so I hate to see you depending
>> on
>> such an approach. Here are a couple of options:
>> 1. Schedule a tape backup of your .BAK file during the day, between the
>> nightly backups.
>> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH
>> DIFFERENTIAL.
>> Then maybe you can fit a week's worth of backups on the disk. (Do the
>> database backup on the weekend if nothing much is happening then. Then,
>> if
>> it fails, you can get a second shot at it before a lot of work happens.)
>> 3. But better is to buy enough disk space for the needed backups. Use a
>> maintenance plan (whether SQL Server Maintenance Plan or one you roll
>> yourself) that will name each file with the datetime and a plan to
>> delete
>> the backups that are older than you want to keep.
>> RLF
>>
>> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
>> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
>> > Hello. Take this simple backup statement:
>> >
>> > Backup Database MyDB To Disk =>> > N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
>> > With Name = 'MyDB full backup'
>> >
>> > Would you think that if MyDB.bak already existed it would be
>> > overwritten
>> > with the new backup? Or would it double in size, effectively
>> > containing 2
>> > backups? I've seen it both ways I think. I ask because I'm trying to
>> > backup
>> > this monolithic DB each night and we're running out of disk space on
>> > this
>> > command.
>> >
>> > I've seen some talk around here of backup compression utilities. In
>> > lieu
>> > of
>> > that would it make sense to schedule simple batch job that deletes
>> > MyDB.bak
>> > before proceeding with the backup? I realize we could be left
>> > vulnerable
>> > if
>> > the subsequent backup should fail.
>> >
>> > TIA,
>> > Ken
>>
>