How can I copy backup files

After the backup job is completed can I copy and send the file to another server automatically so that I have another set of files existing in another server and in case of failure I can restore and connect the application in only few minutes of downtime. I am using sqlserver 2008r2 on windows server 2008r2.

more ▼

asked Oct 05, 2012 at 09:12 PM in Default

avatar image

140 37 42 45

How large is your backup? The recovery time would depend on the size of the backup.

Have you considered hot stand-by methods, like mirroring/log shipping etc.

Oct 05, 2012 at 09:16 PM sp_lock

The biggest database backup file is about 3g. Will this cause any issue. No I have not thought about implementing either of them. But tried working on snapshot replication but it was cumbersome and was not real time. Are those above process easy to do and just requires another sqlserver server 2008 r2.

Oct 05, 2012 at 09:33 PM Chitrarekha
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

@Chitrarekha +1 for thinking about your DR/HA solution. I have a couple of questions for you though. Are you looking at this as your disaster recovery solution or a high availability solution? Based on your question of just moving the backups I am leaning more towards DR verses HA. Database mirroring is an HA solution and is a solid technology and very simple to setup. The problem I have with people using it for DR is if someone ran an update statement without a where clause or dropped the wrong table, this transaction is mirrored as well. You then have to revert to a backup to recover. Now in your case with a 3GB backup, that would only take minutes but imagine a 500 GB database.

A better solution for DR that can still get you close to HA times is log shipping. This too is easy to setup, even has a wizard for configuring it. With log shipping you could set it up with a 24 hour load delay meaning the transaction logs are regularly being applied but 1 day behind. Then if a disaster or oops moment happens you can simply roll the logs forward to a point in time just before the issue. Depending on the system the logs from the past 24 hours should be fairly small in comparison to the full backups. You would need to set the log backups to what ever interval fits within your service level agreements. Common times are 5, 10, 15, 30, and 60 minutes with 10 to 15 minutes being the most common I have seen.

Another set of questions is what is your RTO (Recovery Time Objective) and your RPO (Recovery Point Objective)? Or asked another way, how long can it be down and how much data can you lose?

more ▼

answered Oct 06, 2012 at 02:26 PM

avatar image

40.9k 39 95 168

Hi Tim,

Thanks for the advice, the application can be down for 2-3 hrs and we prefer for no data loss.

Oct 09, 2012 at 01:30 PM Chitrarekha

Zero data loss is pratically a myth. It is nearly impossible to achieve. Most organizations have settled on 5, 10, 15 minutes. It all depends on the scenario of the disaster. In the event of a total system and storage loss, you only have your backups to go off of. If you had mirroring in place and crash recovery was successful you would have near real time data but those in flight transactions would be lost. If you had log shipping in place you would have all data up to the last log backup (could be within 5 minutes). If you had a more graceful disaster where you could still access the transaction log you could always back up the tail end of the log and recover up to nearly the point in time of the disaster.

Oct 09, 2012 at 02:37 PM Tim

Hi Tim,

That sounds fair assumption but since I am the only one in my organization working with sqlserver, which one is easier to setup? Is it log shipping or mirroring?

Oct 09, 2012 at 03:47 PM Chitrarekha

@Tim it depends on how you define zero data loss. If you are willing to accept the performance hit of multi-stage commits, you can come exceedingly close to zero data loss through mirroring with a witness. Naturally in flight transactions would be rejected in the event of certain types of failures there, but for certain scenarios that may not be defined as data loss since the event is effectively rejected instead.

But even getting to the point of mirroring-with-witnesses is beyond what most organizations are willing to pay for and it still requires being prepared to reject transactions in certain circumstances.

Oct 09, 2012 at 05:17 PM TimothyAWiseman

@TimothyAWiseman, valid point, but back to a true DR scenario involves a copy outside of your primary datacenter. Leveraging synchronous database mirroring across a WAN surely is not wise for most cases.

It still all comes down to what the SLA is and what the person is truly trying to prepare for. But I like your suggestion though, that truly would gurantee two sets of data with complete log of each and every transaction. If you had enough availability in your SLA to account for a full base restore and rolling the logs forward you could recover to any point in time. I like it.

Oct 09, 2012 at 05:27 PM Tim
(comments are locked)
10|1200 characters needed characters left

3Gb would normally take around 5ish mins depending on your infrastructure. I would test this on your stand-by server to get a exact time. But plan around your DB growing over time.

Correct. Mirroring also gives you the option of having a failover partner (secondary server) within a connection string to the DB (if using an application), along with a witness server this could be an automatic failover. Determining the type of mirror would be dependant on your requirements and/or the SQL edition.

The limitation of log shipping is that depending on the time/type of failure on the primary server you could lose date in between your log backups.

more ▼

answered Oct 05, 2012 at 09:50 PM

avatar image

10.9k 27 37 37

(comments are locked)
10|1200 characters needed characters left

3 good answers so far but I'll add a little.

3GB would be a lot longer that 5mins on our network, getting 3GB to our alternate site would be at least 30 minutes. I would recommend you test it before relying on it.

What you have described is a basic form of log shipping, although you only mention copying the full back up files and LS copies each log file over to the alternate site and applies it to the database there. This gives you a warm standby database but depends on you having a spare SQL Server license, a database in Full recovery mode and network capacity at the times you want to implement the copies. Log files are usually much smaller during standard OLTP hours but, maintenance windows may generate bigger files as more data gets touched in short periods of time.

Before you copy the files make sure they are compressed. Some backup solutions do this as part of the backup process.

Indeed, some solutions incorporate the option to copy the backup to an alternate location as part of the backup process. You may or may not have a solution like this. If not then I'd recommend taking a look at what options there are on the market as having something that is supported in times of crisis can be a real comfort rather than trying to work out how a home-grown solution can be used to recover from a disaster.

more ▼

answered Oct 07, 2012 at 09:51 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

The simple answer is yes, provided the network infrastructure and security setup permit it.

If you are doing this inside of T-SQL you can use xp_cmdshell to copy the file to the target server. That of course assumes xp_cmdshell is enabled and whatever context that script will be running under has the appropriate read and write permissions on the file systems.

But I'll join sp_lock in saying that if you are truly going for minimum downtime, mirroring may be better.

more ▼

answered Oct 05, 2012 at 10:57 PM

avatar image

15.6k 22 57 38

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 05, 2012 at 09:12 PM

Seen: 1519 times

Last Updated: Oct 09, 2012 at 05:27 PM

Copyright 2018 Redgate Software. Privacy Policy