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!!
CheersHi
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...
>> 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
>|||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...
>> 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
>>
>|||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
>|||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|||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...
>> 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
>|||"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|||Lovely stuff, thats given me something to crack on with, much
appreciated. This may sound stupid but would the following scenario be
viable and if so what sort of time would it take to fully restore
[approx]..
SUN - 02:00 - Full backups 1.3GB
> MON - SAT - 08:30 >> 18:30 - Log backups every 2 hours, meaning 6 logfiles per day approx 100mb per day.
This means I would only have 1 full backup per week, so should the
server die on Sunday morning I would need to restore last Sunbdays
full backup, then 30 log files...
Hmmm, looks a bit odd doesnt it?
Cheers|||"gstar" <gary.brett@.gmail.com> wrote in message
news:9f90951c-e505-4a5b-be4e-9101415dd3ad@.v29g2000hsf.googlegroups.com...
> Lovely stuff, thats given me something to crack on with, much
> appreciated. This may sound stupid but would the following scenario be
> viable and if so what sort of time would it take to fully restore
> [approx]..
> SUN - 02:00 - Full backups 1.3GB
>> MON - SAT - 08:30 >> 18:30 - Log backups every 2 hours, meaning 6
>> logfiles per day approx 100mb per day.
> This means I would only have 1 full backup per week, so should the
> server die on Sunday morning I would need to restore last Sunbdays
> full backup, then 30 log files...
> Hmmm, looks a bit odd doesnt it?
Yes and no.
You could schedule a differential each day if you wanted to minimize the
number of restores.
But, consider the case where you might do log backups every 10 minutes and a
full backup once a day. (That's what we did at our last job).
So 20 hours into the day you have 120 logfiles to restore.
Sounds like a pain but we routinely tested stuff like that.
If I were doing it manually I'd simply do
DIR *.log /b /od
Cut and paste that in query analyzer
Find/replace and prepend
restore log foo from disk='
and then append
' with norecevory
and then run it in a batch. Took about 5 minutes including cutting/pasting
and the find/replace.
So 30 logs, wouldn't bother me a bit.
> Cheers
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanx Greg
> If I were doing it manually I'd simply do DIR *.log /b /od, Cut and paste that in query analyzer
> Find/replace and prepend
> restore log foo from disk='
> and then append
> ' with norecevory
> and then run it in a batch.
What exactly would these commands be doing though, sorry to be thick!
Say I had db & logfile backups called sales.bak & sales.trn on D:
\Backups
salesdbfull.bak - 1gb
salesloghour.trn - 20mb
Into query analyzer -
DIR salesloghour.log /b /od
restore.bat - restore log foo from disk=D:\Backups
I'm not getting this am I!|||"gstar" <gary.brett@.gmail.com> wrote in message
news:f9bc834f-8e6f-4d23-af3f-979f483edc8a@.v29g2000hsf.googlegroups.com...
> Thanx Greg
>> If I were doing it manually I'd simply do DIR *.log /b /od, Cut and
>> paste that in query analyzer
>> Find/replace and prepend
>> restore log foo from disk='
>> and then append
>> ' with norecevory
>> and then run it in a batch.
> What exactly would these commands be doing though, sorry to be thick!
Hey, no problem. Better off asking questions now than after a disaster and
really be up a creak.
> Say I had db & logfile backups called sales.bak & sales.trn on D:
> \Backups
> salesdbfull.bak - 1gb
> salesloghour.trn - 20mb
>
Well I typically user maintenance plans or roll my own so my names would
more like:
sales_db_200801110200.bak - So, year, month, day, hour, minute.
Same for logs. So if I had four log files after that backup I'd have:
sales_log_200801110300.TRN
sales_log_200801110400.TRN
sales_log_200801110500.TRN
sales_log_200801110600.TRN
> Into query analyzer -
>
At the CMD prompt.I'd type
DIR sales_log_20080111*.TRN /b /od
That would return ONLY the names of the logs for the 11th with no
datetime/size information.
I'd cut and paste THAT list from DIR into query analyzer.
> DIR salesloghour.log /b /od
> restore.bat - restore log foo from disk=D:\Backups
>
In query analyzer I'd do a find on sales_log and replace it with restore log
sales from disk='sales_log
This would get me a series of commands:
restore log sales from disk='sales_log_200801110300.TRN
restore log sales from disk='sales_log_200801110400.TRN
restore log sales from disk='sales_log_200801110500.TRN
restore log sales from disk='sales_log_200801110600.TRN
And then I'd find .TRN and replace it with .TRN' with norecovery.
restore log sales from disk='sales_log_200801110300.TRN' with norecovery
restore log sales from disk='sales_log_200801110400.TRN' with norecovery
restore log sales from disk='sales_log_200801110500.TRN' with norecovery
restore log sales from disk='sales_log_200801110600.TRN' with norecovery
Then I'd execute that after restoring the DB backup itself with norecovery.
Once I'm satisified I haven't made any mistakes I'd
restore database sales with recovery
And we'd be good to go.
> I'm not getting this am I!
That's fine, ask away we'll do our best.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html