September 23, 2009 at 6:25 pm
Jeffrey Williams-493691 (9/21/2009)
No, BackupExec and other utilites like that won't backup SQL Server database files that are open. SQL Server locks the files and will not allow them to be backed up.
This should be corrected to say: BackupExec and other utilities won't backup SQL Server database files unless their SQL Server "agent" software is purchased seperately and installed. Please see: http://www.backupexecfaq.com/articles/concepts/backing-up-microsoft-sql-server.html Other enterprise backup programs have similar - ask your vendor's rep.
April 25, 2011 at 8:08 am
Quick question hopefully :).
Network team manages backups to tape and said tape system/application can not be setup to backup at specific times. It has some kind of queue system with windows of opportunity. Also, we have a SAN backup process that does 4 backups during business hours which I control times and frequency to some degree. Network team configures backend filer/SAN system wich is a black hole to me at the moment.
Now all my databases are set to simple. My problem is, I do think we could benefit from Full Recovery Model in many of our databases. So how would one overcome the challenge of balancing tape and SAN methods? And how does one deal with difficult Network Admins 🙂 and managers who don't understand a thing you are talking about :).
I absolutely hate tape backups 🙂 and it's a love/hate relationship with SAN. Occasionally, I have issues with SAN method filling up and needing to remove snapshots do to a large transaction. I would love to write to disk and have them backup the disk drive :).
April 25, 2011 at 2:52 pm
Sophisticated method: Tape backup systems (the one we use, for example) will work with a purchased agent that can be installed on the SQL Server's server. When the tape backup system wants to back up, it contacts the agent, which contacts VSS (a SQL Server service on the SQL Server) to back up the databases to tape.
Dumbed down method (the one we use): Pick a time which is likely to be before the tape backup window of opportunity, and create a SQL Server maintenance plan/job which backs up the databases at that time to a specified folder (for example, SQLBackup). Tell the tape backup system to back up folder SQLBackup. This method wastes resources, including the space needed for the backups (stored on disk as well as on tape), I/O (writes to disks and then copied to tape), etc. We use it because our databases tend to be small, that is, 100 MB to 1 GB, and we have long windows for backup.
I have so many questions for your network admin, you really really need a better relationship with that person. (Example of questions--what are the SAN backups doing? And are they interfacing correctly with SQL Server? If so your backups may be right there ...) Perhaps you can convince the network admin that they could solve some of their existing problems if they worked more closely with you... Good luck!
April 26, 2011 at 8:14 am
Your sophisticated method for tape sounds like something similar to what we have. (NetBackup)
I totally would like to write to disk and have them back that up to what ever medium they wish. This method would cost a good bit in storage but I think it is well worth the cost for stability and I can manage the backups for SQL. We have a happy mix of large and small databases majority are ranging from 1 - 10 GB and a handful of them upper 30 GB and 1-2 in the 300 GB range.
As far as the relationship with the Network Admins it is not that it is bad, I guess its just neither knows enough of the other world :). I have been digging into the SAN information (NetApp) to understand it better and try and speak intelligently about the products but without being able to touch and play it is very difficult.
After a little further research, the SAN uses a client end to interface with SQL much like the tape method you mentioned. The Client pauses, takes snapshot of SQL and then I think it triggers a drive level copy (not 100% sure how that fully works, yet). This side works and I use this on occasion to make copies for dev with the larger databases. Now keep in mind I use the client side to control how often they occur and type of backup. Currently, we kind of use this as a nice to have backup where tape is the major line of defense. As I mentioned, if the SAN backup method runs out of space then I have to clear some or all backups or I lose the drive. If I was in full recovery mode and taking log backups say every 15min and have to clear backups I lose the integrity of full recovery mode. with the added question what do I do for the tape backup if log backups occurring every 15min from another system (SAN). I would imagine a possible scheduling conflict could occur let alone a pain to restore. I haven't seen anyone mention how they balance the different methods and I can not make a recommendation to our team.
The SAN we are using could be used as a source for tape backups, which I asked about. The reply is "We just don't have resources to dedicate to figuring out how as it requires lots of custom scripting". Anyway, perhaps someday we will find a better solution :).
April 26, 2011 at 10:04 am
Interesting. Thanks for sharing.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply