automatically compress backups

I have a maintenance plan for handling backups. I need to be able to amend the jobs so that the latest backup can be compressed (e.g. command line compression utility) before it is copied off across the network to a DR machine.

The issue is that the Maintenance Plan produces data/time specific backup file names - mulitiple backups stored in the same directory. Therefore, within a job step, I'm finding it difficult to code it such that only the latest backup is compressed. The command line syntax of these compression utilities is somewhat limited...

e.g. wzzip zipname.zip dbbackup_*.bak

nb. The '*' represents the wild card for the date/time part of the backup files created by the maintenance plan.

The problem is that the above command would keep adding multiple backups to the same zip archive. What I want is to simply add only the latest backup to the archive but to achieve this I would have to know the name of the backup file (created by the maintenance plan) programatically within the job.

I would have thought this was a fairly common requirement/problem. Has anyone got some suggestions/solutions?

CliveRE:
Q1 Has anyone got some suggestions/solutions?

A1 I doubt you'll like hearing this, my suggestion is don't compress production backups if you can possibly avoid it in any production setting (especially certain kinds of software based compression).

I've seen too many failed validation restores that seem to have only the fact of having been compressed and decompressed in common to heavily rely on any compression of backup dumps. (Granted, it does work 'most' of the time, which is generally fine for many development environments. However, in some situations where compression is implemented with larger backups I've seen routine daily restore validations fail or restore with problems every few weeks.)
---

If you are intent on going ahead anyway, what Sql Server version you are running is important. (In 2k installs I've implemented UDF functions instead of stored procedures for extracting dump information.)

Unfortunately no version has provided particularly rich built in backup / maintenence procedures to address the information needs you would require. (There are lots of undocumented ones that are worth looking at, but they are as subject to unannounced changes as the underlying MSDB table structures. I think you may be stuck implementing and maintaining your own special functions / stored procedures). Current Maintenance Plan Procedures (ver 7 / 2k) include:
sp_add_maintenance_plan
sp_add_maintenance_plan_db
sp_add_maintenance_plan_job
sp_delete_maintenance_plan
sp_delete_maintenance_plan_db
sp_delete_maintenance_plan_job
sp_help_maintenance_plan

The following user special stored procedure (implemented on some ver. 7 installs) returns the last DB or TL dumpfor a specified DB and Full or Log Dump on 7.0 and 2k (but not 6.x) installs:

Use
Master
Go
DROP PROCEDURE sp_LastDump
go
CREATE PROCEDURE sp_LastDump
--Fully Qualified Select most recent DBDump or TLDump
--@.pBkpSetTyp = I, D, L, @.pDBNam = DB name,
-- sp parameters:
@.pDBNam VarChar(512) = 'Master',
@.pBkpSetTyp VarChar(50) = 'D'
AS
exec ('Set NoCount On')
SELECT MsDb..backupmediafamily.physical_device_name
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE MsDb..backupset.backup_finish_date =
(SELECT MAX(MsDb..backupset.backup_finish_date)
AS Mxbackup_finish_date
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE (MsDb..backupset.database_name = @.pDBNam) AND
(MsDb..backupset.type = @.pBkpSetTyp))

Note: If running 6.x or earlier, you'll need to create a proc referencing 6.x msdb tables instead e.g.(sysbackuphistory sysbackupdetail). If later upgrade to 7.0 or 2k, or later versions, you'll need to rdefine any special stored procs you create to reflect newer MSDB tables.|||How is the currently developed maintenance plan programmed - through the maintenance wizard / dts / stored procedure ... ? Which compression software package are you using ?

Automatic Identity Range Handling warning message.

Ive implemented merge replication with SQL Server and an Access Database.
SQL Server is the publisher and Access database is the subscriber.
I have ONE table in the schema that can be updated in both the subscriber
side as well as the publisher side. I have an IDENTITY column in this table
and I have set the property "Automatic Identity Range Handling" ON for this
table.
Ive also created a script to create this publisher. Now, heres my problem.
When I create the publisher, I get a warning message thats stated below:
"Warning: only Subscribers running SQL Server 2000 can synchronize with
publication '<database>' because automatic identity ranges are being used."
OK, Ive done some resonable testing on this statement and it SEEMS that
synchronization with Automatic Identity Range Handing enabled DOES work for
Access databases as well even though the warning states otherwise! Im using
JET to connect to access and not the desktop engine. Once the threshold is
reached, and after the merge agent kicks in, the indentity seed is reset to
the appropriate values on both the access and the sql server side.
Appropriate check constraints are also placed.
I just want to make sure if this is correct - and this warning is just
bogus.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know whether it
matters when warning is shown for "automatic identity ranges are being
used". Have I understood you? If there is anything I misunderstood, please
feel free to let me know.
Based on my scope, there are some known issues for us by using automatic
identity ranges. You could have a view on them
BUG: Unable to Change Identity Range of Publisher If You Use Auto Identity
Range
http://support.microsoft.com/default...b;en-us;310540
BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs
Continuously
http://support.microsoft.com/default...b;en-us;304706
Additionaly, there is a by design issue for automatic identity range based
on the descriptions in the following documents.
PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent
Runs an Insert Trigger
http://support.microsoft.com/default...b;en-us;324361
I am afraid the above all are why this warning is shown up.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

automatic identity range handling - newbie question

I am having problems when attempting to publish my database. When I try to
set the automatic identity range handler, the checkbox is dimmed out.
I am using SQL Server 2000 and my subscribers are using Windows CE. I have
set all the indentity fields to INT (2). Everything seems to be in order, but
the checkbox is still dimmed out.
All help will be greatly appreciated
Dale
Dale,
do you already have a subscription to this publication? If it is dimmed out
when looking at the publisher properties, you'll have to drop the
subscriptions, drop the article (apply) then readd the article and it will
then be possible to have automatic range management.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply.
I am very new to this, so could you please explain how I "drop" the
subscriptions, and "drop" the article.
thanks
Dale
"Paul Ibison" wrote:

> Dale,
> do you already have a subscription to this publication? If it is dimmed out
> when looking at the publisher properties, you'll have to drop the
> subscriptions, drop the article (apply) then readd the article and it will
> then be possible to have automatic range management.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Dale,
you can right-click on the publication and select the publisher properties.
On the subscriptions tab, delete each one. Apply. On hte articles tab,
uncheck the table in question. Apply. Next, check this article (table) and
on the article properties you'll be able to enable automatic range
management. After that, readd the subscriptions and run the snapshot agent
and merge agents.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your help Paul!
I found the problem, one of the identity fields was accidently set to
cluster. Once fixed the publication went smoothly.
BTW your website is great!
Dale
"Paul Ibison" wrote:

> Dale,
> you can right-click on the publication and select the publisher properties.
> On the subscriptions tab, delete each one. Apply. On hte articles tab,
> uncheck the table in question. Apply. Next, check this article (table) and
> on the article properties you'll be able to enable automatic range
> management. After that, readd the subscriptions and run the snapshot agent
> and merge agents.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

auto_increment_offset

I need to setup a way to manage auto_inc keys on MSSQL without the
distributor handling it. I am using MSSQL 2005 transactional replication
with updateable subscriptions.
I have done this with MySQL using auto_increment_offset
see article:
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
if you want more info about the process.
Can MSSQL do anything like this?
Thanks
You can use dbcc checkident for this. It is better to let replication handle
it through automatic identity range mangement. Here is an article on it.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
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
"Sintel Silverblade" <sintel@.segamer.com> wrote in message
news:e5%235uVhqHHA.4324@.TK2MSFTNGP04.phx.gbl...
>I need to setup a way to manage auto_inc keys on MSSQL without the
> distributor handling it. I am using MSSQL 2005 transactional replication
> with updateable subscriptions.
> I have done this with MySQL using auto_increment_offset
> see article:
> http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
> if you want more info about the process.
> Can MSSQL do anything like this?
> Thanks
>