Backup question 1

[takaoki]

Hi,

I think it is a very bad idea to place backup files in the same drive location as the data files... so why does Microsoft have "data" and "backup" folders in the same place?

My question is, does anyone actually do this? Does having a nightly tape backup exclude the need for placing these files (data and backup) on separate drives??

Thanks

 

[mutley1]

Hi Takaoki,

Yes - having the backups on the same drive as the data files is a VERY bad idea. It is a default when SQL is loaded that it goes under that directory structure. I don't know how you do your backups, but if you use a maintenence plan you can specify where you want to back the database up to (well, even if you use TSQL jobs to do the backup you can specify the destnation as well).

Tape backups do not negate anything at all - they are an additional level of resiliance against failures of any measure. E.g. If your data is on the D drive, your backup is on the X drive and the server catches fire, you have lost everything. If you backup the database to a .BAK file on the same server (if that is all you have), then the tape backup is seperate from the drive backups and you have a copy of the data that is not actually on a physical server and can, in respect, be restored anywhere you like once the new server has been built.

HTH,

Matt

 

[MDXer]

The most reasonable answer is to why is this the default setting is that there is no guarantee that the machine where the installation is taking place on has more than just the c drive.

DO people let the backups stay this way? the uneducated may but any developer or dba should no to change this when they create their backup jobs.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[takaoki]

Thanks much...

So where would the most ideal place for the backups be? (provided that tape backups are happening as well)

1.) another *physically* separate drive on the server?
2.) a mapped drive on another server?
3.) other ideas?

Note: We take one full backup each night, and may start adding some differential backups a few times a day.

 

[MDXer]

For me it would be a seperate physical drive on the server. Then as soon as this backup was finished it would either copy to a network share that would be backed up or rip it to tape from this drive. The faster you get it off the server the safer you are.

Also remember that a backup plan is as only as good as it's implementation. just like the software we develop a good back-up recovery plan needs to be tested.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mutley1]

Generally speaking (and I mean very generally as systems will vary from company to company and database sizes etc.), the DBA would have a separate drive on the SQL server dedicated to storing backups. The actual SQL backup would be written to this drive, then after liasing with the correct team (let's call them Infrastructure for the current example), get Infrastructure to back up that drive (or whatever they are backng up on that server) at a point where you are happy that the SQL backup would have finished. e.g. if your SQL backup runs at 10pm - 11pm then get them to run a tape backup at 12am.

Depending on timescales and bandwidth you can back up to another server, but it is not generally the best idea as you have another point of failure (i.e. the network).

1. Back up locally and get timescales of how long the jobs run for
2. Get infrastructure to backup your backup directory to tape / copy to their preferred location according to your timescales.
3. You can always add another step to the job when the backup has finished to copy it to another server for safety.

HTH,

M.

 

[takaoki]

Thanks much.

Unfortunately some of our sql servers do not have more than one drive.

Barring adding more drives to the servers, which would be a good idea, I was thinking this would be the best approach.

1.) backup to local drive (same drive as data files)
2.) tape backup the local drive as soon as the backups are finished to get them off of there
3.) copy the backups to a network share (this is last since this could take longer than going to tape)

Does this make sense?

Thanks

 

[mutley1]

Sounds fine. I'd get the guys to look at design before they build the next server though......AT LEAST C Drive for binaries, D for data, L for logs and another drive for backups.......(letters are interchangeable, but what we use for logical purposes).

Rgds,

M.

 

[takaoki]

Are you talking logical drives, or physical drives?

Thanks

 

[mutley1]

C drive relates to the local drive as the binaries need to be on the machine, but the rest of the drives is up to you - as I say, sepends on your system. Always better to have them on a SAN of somethng like that, but depends on what the company can afford.....blah blah......but just not on the C drive!

 

[takaoki]

But if the other drive, e.g. "x:" is just a logical partition of the same physical drive as "c:" then it really isn't a different drive, yes? And offers no additional benefit of protection.

 

[mutley1]

It all depends on your set up!!!I don't know how many physical disks some of your other servers have, but those with 1.....not really a good idea. If you cant afford more disks, then back up to share somewhere off the server.