Backup 2GB database over WAN

Hi,
We have SQL server 2000 with 6 production dbs that we are putting into
a data center servered by a 2Mpbs internet link. Until now I have done
full backups at 02:00 every day then hourly log backups which are
taken offsite on tape each day.
I need to find a solution [cheap] which will allow me to continue
backing these dbs over a WAN link back to our HQ so I can restore
should the server die or other disaters strikes. The problem lies in
the fact one of the dbs is 1GB so wouldnt be suitable to take the
whole thing across as we are charged by the MB on data transfer. With
that in mind I looked at Red Gate software but it doesnt suggest
backing up over the WAN is a viable solution.
Can anyone suggest a better method to achieve this, sure I am not the
first person to have these requirements?
As you can probably guess I am no dba, just a sysadmin who is lumbered
with everything, to our director an IT person means you should be able
to do the lot!!
Cheers
Hi
Are you backing up to disc and then copying over the WAN? With SQL 2008
there will be compression options, with earlier versions you can use third
party tools such as Red Gate or roll your own solution although winzip has
file size limitations tools such as winrar has less of an issue. If you want
smaller files you can always back up to multiple files, this option may
actually speed up the backup especially if they are on separate discs with
their own controllers!
Another option would be to do differential backups and reduce the number of
full backups you do. This will not change the size of the full backups but
the differential backups should be smaller.
HTH
John
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers
|||Back up locally, then copy the resulting files after compressing.
If the database in teh data center goes south, you want the ability to
restore from disk without havnig to copy back to the server.
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers
|||Agreed, but you might look into using a USB or other externally connected
drive. That way, if the server goes pear-shaped, you can still get at the
local backup files.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:eaCe3l7UIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Back up locally, then copy the resulting files after compressing.
> If the database in teh data center goes south, you want the ability to
> restore from disk without havnig to copy back to the server.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "gstar" <gary.brett@.gmail.com> wrote in message
> news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
>
|||Good point. I was thinking of the database level failure only, not the
whole server tanking...
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:Ok6ZM27UIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Agreed, but you might look into using a USB or other externally connected
> drive. That way, if the server goes pear-shaped, you can still get at the
> local backup files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:eaCe3l7UIHA.1212@.TK2MSFTNGP05.phx.gbl...
>
|||All vaild suggestions and much appreciated, however I really need to
limit the anount of traffic across the WAN as we get charged by the
MB. Unsure what type of compression a 1GB db would reduce down too but
copying dbs each night back to HQ over a month would wipe out over
30GB of out data allowance..
Currently all my backups are done over LAN, the server goes into data
centre next week so I have a week to play around and figure out the
best solution..
Situation now - All done using SQL backup jobs onto local disk, then
onto tape and offsite
MON - SAT - 02:00 - Full backups 1.3GB
MON - SAT - 08:30 >> 18:30 - Log backups on the hour
I would like to continue the schedule albeit without the tape option
meaning the backups will still run to the local disk [data center],
its how I get these files back to HQ without wiping out the alloted
data limit...
Cheers
|||Litespeed...backup file is 75-90% smaller than native SQL backups. Or
Red-Gate. Or (insert other vendors that I can't recall)...
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"gstar" <gary.brett@.gmail.com> wrote in message
news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
> All vaild suggestions and much appreciated, however I really need to
> limit the anount of traffic across the WAN as we get charged by the
> MB. Unsure what type of compression a 1GB db would reduce down too but
> copying dbs each night back to HQ over a month would wipe out over
> 30GB of out data allowance..
> Currently all my backups are done over LAN, the server goes into data
> centre next week so I have a week to play around and figure out the
> best solution..
> Situation now - All done using SQL backup jobs onto local disk, then
> onto tape and offsite
> MON - SAT - 02:00 - Full backups 1.3GB
> MON - SAT - 08:30 >> 18:30 - Log backups on the hour
> I would like to continue the schedule albeit without the tape option
> meaning the backups will still run to the local disk [data center],
> its how I get these files back to HQ without wiping out the alloted
> data limit...
> Cheers
>
|||Idera makes one too.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:ewlS%23y8UIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Litespeed...backup file is 75-90% smaller than native SQL backups. Or
> Red-Gate. Or (insert other vendors that I can't recall)...
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "gstar" <gary.brett@.gmail.com> wrote in message
> news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
>
|||Backup to disk and then zip the backup file, transfer it, unzip and restore.
This can be automated start to finish too using various mechanisms.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers
|||"gstar" <gary.brett@.gmail.com> wrote in message
news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
> All vaild suggestions and much appreciated, however I really need to
> limit the anount of traffic across the WAN as we get charged by the
> MB. Unsure what type of compression a 1GB db would reduce down too but
> copying dbs each night back to HQ over a month would wipe out over
> 30GB of out data allowance..
I've seen compression with Winzip 50-80%. So a 1GB backed up nightly would
be about 300MB. That would fit under 30GB.

> Currently all my backups are done over LAN, the server goes into data
> centre next week so I have a week to play around and figure out the
> best solution..
> Situation now - All done using SQL backup jobs onto local disk, then
> onto tape and offsite
> MON - SAT - 02:00 - Full backups 1.3GB
> MON - SAT - 08:30 >> 18:30 - Log backups on the hour
>
In theory you could do backups every other night also and continue to copy
over the logfiles and just extend your recovery period a bit.
So between compression and slightly reducing number of full backups, I think
you can keep under the 30GB.
(and of course the question to ask the finance guys... "how much would it
cost us to up from 30GB to 60GB vs. if we don't have adequate backups?"

> I would like to continue the schedule albeit without the tape option
> meaning the backups will still run to the local disk [data center],
> its how I get these files back to HQ without wiping out the alloted
> data limit...
> Cheers
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html