backups to network share or local to the disk

Question

backups to network share or local to the disk

passivebyz

SSCommitted

Points: 1908
More actions
June 12, 2010 at 6:27 pm

#387143

Hi,
We have 3 node a/a/p setup for sql server 2005 and I'm taking the backups to the disk on both the active nodes. But what happens if the node crashes? My backups will be gone right?
So I'm planning to backup the databases to a network share (on other separate server) but I read in forums that do NOT take the backups to Network share
Please advice what is the best practice to backup the databases?
thanks

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

make the backup drive a share then make that share a cluster resource. Then if a node crashes the backup drive will failover as well.

You should also copy the backups off somewhere else, a network share or virtual tape.

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Fundamental backup principle - Do not store backups on the same physical drive as the data/logs. Use a separate drive. In terms of clustering, as George said, get a second drive as a clustered resource and use that.

You can script a copy to the network.

If you can't get another drive, just use a job to script a copy of the files to a network share after they are complete.

passivebyz SSCommitted Points: 1908 More actions · Answer 3

make the backup drive a share then make that share a cluster resource. Then if a node crashes the backup drive will failover as well.
You should also copy the backups off somewhere else, a network share or virtual tape.

you mean, we need to create the share on the same node & make it cluster resource?

I want to give you more details about the Cluster groups & Instances we have:

On Node1 (Active): Has one instance

SQLGroup1-->for ins1

BackupGroup1-->has one drive to keep the backups of ins1 and also created a cluster file share to keep the log shipping backups

Node2 (Active):: has 4 instances:

SQLGroup2 --> for ins2

SQLGroup3--> for ins3

SQLGroup4-->for ins4

SQLGroup5-->for ins5

BackupGroup2--> has one drive to keep the backups of all the 4 instances and also created a cluster file share to keep the Log shipping backups.

Node3 (Passive)

Please advice me what is the best practice to keep the backups for above configuration?

Is the one we have currently good backup strategy?

Do I need to create the a network share on a separate Server instead on the drive in Backupgroup on node1 & similarly on node2?

and also create a network share on separate server for Log shipping backups?

or just the configuration I have is good go?

Please advice..I'm really in a confused state and I'm NOT getting proper solution for this from many days?

Thanks

passivebyz SSCommitted Points: 1908 More actions · Answer 4

If you can't get another drive, just use a job to script a copy of the files to a network share after they are complete

Steve,

I have a full backup job which takes backups of 30 databases of total size of 100 GB and takes 90 mins to complete. So you mean that I need to call a copy job once the full backup completes to copy the backups to a network share right?

Could you please give me sample script to copy the backups to a network share once the full backup completes?

Many thanks

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

The key is for your backup drives to be a clustered resource. They should be visible from both nodes, just like the data files. However the physical drives should be separate.

For scripts, here are a couple:

http://www.sqlservercentral.com/scripts/67173/

http://www.sqlservercentral.com/scripts/31932/

passivebyz SSCommitted Points: 1908 More actions · Answer 6

The key is for your backup drives to be a clustered resource. They should be visible from both nodes, just like the data files. However the physical drives should be separate.

Thanks Steve,

So here my backup drive in Backupgroup1 on node1 and the backup drive in Backupgroup2 are clustered resources and that means I'm with best practices right?

or do I need to completely send the database backups to a Separate Network share on a separate server?

Please clarify me

Thanks

passivebyz SSCommitted Points: 1908 More actions · Answer 7

passivebyz

SSCommitted

Points: 1908

June 15, 2010 at 12:23 am

#1180211

Could you please advice me...

thanks

george sibbald SSC Guru Points: 104210 More actions · Answer 8

passivebyz (6/13/2010)
The key is for your backup drives to be a clustered resource. They should be visible from both nodes, just like the data files. However the physical drives should be separate.
Thanks Steve,
So here my backup drive in Backupgroup1 on node1 and the backup drive in Backupgroup2 are clustered resources and that means I'm with best practices right?

you are ok, the backup drives are cluster resources so will failover to the passive node should you lose a node.

or do I need to completely send the database backups to a Separate Network share on a separate server?

its not an OR, its and AND, you should copy your backups off somewhere else AS WELL, in case of total loss of the drive,

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 9

If they are clustered resources, then as George mentioned, they'll fail over. Is the backup drive 1 from Node 1 visible on Node2? It should be.

passivebyz SSCommitted Points: 1908 More actions · Answer 10

If they are clustered resources, then as George mentioned, they'll fail over. Is the backup drive 1 from Node 1 visible on Node2? It should be

In a 3 node a/a/p cluster, I can see the backup drive from both the active nodes but NO drives are visible from Passive node.

In a 2 node a/p cluster, No drives are visible from passive node.

So is my cluster set up correct or need any attention?

Thanks

george sibbald SSC Guru Points: 104210 More actions · Answer 11

Its a fundamental principal of clusters that the drives are only visible to the active node so yes that sounds correct.

If you were to failover the cluster resources you would then see the drives on the other node.

---------------------------------------------------------------------

vedpsoni Old Hand Points: 369 More actions · Answer 12

User should always use the diffrent drive to take backup and if want to take back up on network path then there will be these ways

1. share the network drive

2. generate script or programme with uses network path

by default using node if we take backup without describe the nework path the backup will store in server drivr

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 13

That's not true. You can have shared drives on the cluster that are visible to the active node only, but will failover if the cluster fails over.

Backing up to the network is problematic at times. It can fail with network traffic, and then you are left without backups. If you want to store it on the network, back up locally and copy it.

vedpsoni Old Hand Points: 369 More actions · Answer 14

thanks

but now i want to know that how can i know that the particular backup has fail

and how to make network cluster