Is there a MS SQL Backup tool that will zip or compress the backup files?

Currently we use a SQL maintenance plan to do a full backup of all our databases daily (about 40 databases on our production server). As you can imagine, this eats up disk space quickly so currently we manually zip the backup files and/or move them to an archive drive. I considered writing an application to walk through the backup folder structure and zip any .bak file it finds, but I know there are some third party tools out there that will backup/restore a MS SQL database.

I was wondering if any of these also zip the backups once they are created. Any recommendations or suggestions are welcome.

First of all, for the sake of your business and clients, I would keep an agreed-upon set of backups in the original state. While it hasn't happened to me often, I have had the problem of a corrupted zip file before. Anything that compresses data has the possibility for corruption. I would recommend at least a week (7 days) worth of backups; at least that's what I would want.

As for a quick, easy, and free way to compress files (using XP or higher), just send the backup files to a compressed folder. Create a new folder, right-click it, choose 'Properties, Click 'Advanced' under the General tab, and choose 'Compress contents to save disk space'. If you hover your mouse over the folder, it gives you the space the folder would take up if uncompressed; if you right-click the folder again and choose 'Properties', you'll see the original size of all the filesand the actual size of all the files on the disk. You can also do this with individual files by viewing properties.

Note: compressed files show in blue.

By compressing a folder, any new file created in it will be compressed automatically. However, if you simply 'Move' an existing file, the file will retain its current state (if it wasn't compressed to begin with, it won't compress).

Is the query using the cache

How do i find out whether the query is picking the data and query plan from
the cache.Use in your trace Events - Stored Procedures - SP:CacheInsert, SP:CacheHit,
SP:CacheMiss and SP:CacheRemove. See BOL for more information.
AMB
"Neeraj" wrote:

> How do i find out whether the query is picking the data and query plan fro
m
> the cache.sql

Is Sql Mobile a generic database engine?

Hi,

I am about to write an application of the Pocket PC 2003 SE. I plan on using CF2.0 and VS 2005. The application will need to store several different peices of information, I immediately thought of using a database.

I have read somewhere that Sql Mobile 05 will only allow databases on the device via replication with Sql 2005. I don't want to use replication. I just want to store several diffent peices of information for use with my mobile app.

Would Sql mobile be suitable or should I stick to something like persisted ADO Data tables?

Thanks for your help

GrahamWell, so far it seems to be!

I created a CF2.0 PPC 2003 project and inserted a Sql Mobile database. I could then define tables and insert data from the mobile app at runtime.
Graham|||

The answer to your question in the subject is yes.

There are few things to keep in mind. SQL Server 2005 Mobile or it's earlier version SQL Server CE have simpler engine and thus have simpler set of features and somewhat more restrictions.

E.g. a recent post on this forum was about using semicolon to separate statements. It is not strictly required on full version but it is on Mobile.

You can find some additional information here

http://msdn.microsoft.com/SQL/2005/mobile/default.aspx

and by searching MSDN for SQL Mobile specific topics. You can also download Books Online only for SQL Server 2005 and search there. Check this URL (it is referred from the general SQL Server page):

http://www.microsoft.com/downloads/details.aspx?FamilyId=ADC75E35-7245-4038-9B8A-B8FABAEC16DA&displaylang=en

In addition to that – may be a useful feature in the development – if you have SQL Server 2005 (not Mobile version) Management Studio installed, you can create a SQL Server Mobile database (select SQL Server Mobile in server types when connecting in Object Explorer). It may help you to “play with” queries, syntax, etc. It is very likely that you can do the same from VS2005. I haven’t tried that myself.

Regards,
Boris.

|||Yet another URL of interest:

http://www.microsoft.com/sql/ce/productinfo/SQLMobile.mspx

Is server avaible during reorganize data/index

we are going to setup maintainance plan to reorganize
data/index, say, twice a month. I read somewhere that if
you rebuild index on a clustered index, the server won't
be availble. But I don't know if it's the case when you
reorganize data/index.
Is there any article on this topic?
Thanks
Frankthe server will be available. The table in question will just have an
exclusive table lock on it.
gaj|||So for that table, no records can be delete/updated but new records can be inserted, is that right?|||no.
The lock is Exclusive for Clustered Indexes. Shared lock for NonClustered
Indexes (Users can read data only)
table is totally locked until clustered index is rebuilt.
You can use dbcc indexdefrag Instead of DBCC dbreindex. This does not hold
locks as long as dbreindex. However, it takes longer to run AND IndexDefrag
does NOT automatically update statistics....
Hope this info helps
gAJ
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:ADB20672-6B47-4C3B-B66D-EA8C62A29085@.microsoft.com...
> So for that table, no records can be delete/updated but new records can be
inserted, is that right?

Is My Backup Plan Safe?

I need to setup an online backup mechanism which allows restoration of data
up to point of failure in the event of DB file corruption or hard disk drive
failure. The current DB size is ~ 100M with a growth rate of ~15M/month for
the last 6 months. I'm thinking of :
1. Create a scheduled daily full backup from c drive to d drive
2. Create a scheduled hourly transaction log backup from c drive to d drive
3. Create a scheduled weekly full backup of backup folder in d drive to tape
drive with Windows backup utility.
4. Create scheduled daily differential backup of backup folder in d drive to
tape drive with Windows backup utility.
5. Two tapes will be rotated every alternate week for backup to tapes. Tape
not in used will be taken off-site.
Is the above plan good enough for the recovery scenario I want to address?Ong
Yep it looks pretty good. But remember that everything you did so far has
no value if you cannot restore the database. Verify that backups are not
corrupted and you can easily restore the database
"Ong" <Ong@.discussions.microsoft.com> wrote in message
news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
>I need to setup an online backup mechanism which allows restoration of data
> up to point of failure in the event of DB file corruption or hard disk
> drive
> failure. The current DB size is ~ 100M with a growth rate of ~15M/month
> for
> the last 6 months. I'm thinking of :
> 1. Create a scheduled daily full backup from c drive to d drive
> 2. Create a scheduled hourly transaction log backup from c drive to d
> drive
> 3. Create a scheduled weekly full backup of backup folder in d drive to
> tape
> drive with Windows backup utility.
> 4. Create scheduled daily differential backup of backup folder in d drive
> to
> tape drive with Windows backup utility.
> 5. Two tapes will be rotated every alternate week for backup to tapes.
> Tape
> not in used will be taken off-site.
> Is the above plan good enough for the recovery scenario I want to address?|||Thank you for your comment & precaution on backup verification.
"Uri Dimant" wrote:
> Ong
> Yep it looks pretty good. But remember that everything you did so far has
> no value if you cannot restore the database. Verify that backups are not
> corrupted and you can easily restore the database
> "Ong" <Ong@.discussions.microsoft.com> wrote in message
> news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
> >I need to setup an online backup mechanism which allows restoration of data
> > up to point of failure in the event of DB file corruption or hard disk
> > drive
> > failure. The current DB size is ~ 100M with a growth rate of ~15M/month
> > for
> > the last 6 months. I'm thinking of :
> > 1. Create a scheduled daily full backup from c drive to d drive
> > 2. Create a scheduled hourly transaction log backup from c drive to d
> > drive
> > 3. Create a scheduled weekly full backup of backup folder in d drive to
> > tape
> > drive with Windows backup utility.
> > 4. Create scheduled daily differential backup of backup folder in d drive
> > to
> > tape drive with Windows backup utility.
> > 5. Two tapes will be rotated every alternate week for backup to tapes.
> > Tape
> > not in used will be taken off-site.
> >
> > Is the above plan good enough for the recovery scenario I want to address?
>
>|||Is the C and D drive the same physical drive? Then you could lose you
database drive and your backup drive.
If C and D are separate physical drives are they on the same disk
controller? If the controller fails then you won't be able to access your
database and backups until you replace the controller or put the drive in
another box..
Imagine your server and tape drive are stolen. It sounds like you would have
to go to tape and possibly lose a week of transactions.
I would backup across the network or directly to the tape drive or get a
separate external hard drive. If your only moving data offsite once a week
get a fireproof safe.
I don't think your backup plan is safe and I don't believe you will be able
to restore to the point of failure if say your building burns down.
"Ong" <Ong@.discussions.microsoft.com> wrote in message
news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
> I need to setup an online backup mechanism which allows restoration of
data
> up to point of failure in the event of DB file corruption or hard disk
drive
> failure. The current DB size is ~ 100M with a growth rate of ~15M/month
for
> the last 6 months. I'm thinking of :
> 1. Create a scheduled daily full backup from c drive to d drive
> 2. Create a scheduled hourly transaction log backup from c drive to d
drive
> 3. Create a scheduled weekly full backup of backup folder in d drive to
tape
> drive with Windows backup utility.
> 4. Create scheduled daily differential backup of backup folder in d drive
to
> tape drive with Windows backup utility.
> 5. Two tapes will be rotated every alternate week for backup to tapes.
Tape
> not in used will be taken off-site.
> Is the above plan good enough for the recovery scenario I want to address?|||Terri ,
Thanks for your important input.
I didn't check this thread for a few days & only saw your reply today.
I hope you can clarify my questions below:
"Terri" wrote:
> Is the C and D drive the same physical drive? Then you could lose you
> database drive and your backup drive.
> If C and D are separate physical drives are they on the same disk
> controller? If the controller fails then you won't be able to access your
> database and backups until you replace the controller or put the drive in
> another box..
FYI, D is an external usb drive
> Imagine your server and tape drive are stolen. It sounds like you would have
> to go to tape and possibly lose a week of transactions.
> I would backup across the network or directly to the tape drive or get a
> separate external hard drive.
Will backup across the network be slow and cause network jam?
>If your only moving data offsite once a week
> get a fireproof safe.
Do you mean to put the tape in a fireproof safe in the office or offsite?
> I don't think your backup plan is safe and I don't believe you will be able
> to restore to the point of failure if say your building burns down.
Sounds like for solution with tape, the only safe way is to use 7 (or 5
excluding weekends) backup tapes to do full db backup daily and transaction
logs backup hourly and keep all the unused tapes offsite.