OMO: Physical server backup (SQL)

Morning All,
We run most of our servers on VMware and backup using Veeam which is a breeze to use since moving from Symantec's Backup Exec but now I've had to create a physical SQL server for performance reasons, I'd like suggestions on how to back it up with something approaching the simplicity of Veeam.

Any ideas?
Thanks

You're talking MS SQL Server? Using physical disks (i.e. not on SAN)?
A snapshotting file system is by far the best way but assuming you don't have one available, backup to disk first and then copy that to tape (reduces table locks and DBCC problems, certainly on the older versions of SQL Server, I've not used the very recent versions)
Or are you asking about the software to use?

Not sure I can answer your question directly however I do work for a large backup software/storage vendor so have some insight into what customers generally do in this situation:

- Veeam does not support backup of physical systems
- Some customers periodically convert their physical system to a VM (P2V) then use Veeam to back up the resulting VM - I believe vSphere can do incremental updates from physical to virtual using its Converter SDK
- Other customers use native tools (for example Windows Backup) to back up physical systems then write resulting backup sets to disk on a VM which is then backed up via Veeam
- Alternatively native tool backup sets can be backed up from the physical system via Veeam using a file to tape copy job

The problem with all of the above approaches is complexity during backup/restore which may mean, especially if the system is important, that you cannot backup with as much granularity/frequency that you would like and that should the worst happen any restore may be complex/slow.

The most common solution seems to be to continue to use a second product to backup any remaining physical systems. I would guess this makes backup/restore easier but means you have to administer two products and pay for 2 sets of licenses/support and so on. With regards to which second product to use there are loads but in the SMB space Backup Exec does seem to be popular. Enterprise customers tend to use Networker/NetBackup/CommVault but if you are a relatively small shop these are probably overkill.

Cheers, James

Yeah sorry, software to do it. It's a windows 2012 R2 server running SQL 2012 on a fusion iODrive2 for faster SQL access, windows sees it as any other drive so backing it up shouldn't require anything special other than something which is SQL aware and ideally uses VSS I guess. I can potentially use backup exec which I'm sure will do it but it is such a lumbering beast of a program that I'd rather use something simpler (if such a thing exists)

Thank for the reply!

Just use SQL backup, create a job and schedule for it. You will need to make sure the SQL agent is running under a domain account, and back the DB up to a network share on one of your VMs. VEEAM can then capture that, and back that up to whatever backup solution you're using. Put compression on the backup as well, you'll be surprised how small the backup set will be.

[timwilky]

A slight variation on atassiedevil suggestion.

I wrote a bit of c# to extract the database names from an sql server and using those backup in turn each database to hard drive. My code maintained the last two backups on local hard drive and 5 last on network storage, the network storage being subject to the normal backup regime. This meant I could recover to within a week without having to go cap in hand to the idiots that manage our data centres and requesting a recovery from backup that costs about €350 per request.


You then run the c# as a scheduled task.

Brettus, how big is this database, and is there more than one? You may want to continue this in PM with me, to stop it getting lengthy on here.

Thanks for all the suggestions guys and sorry for lack of response, it's been a busy day.
DB isn't too big as yet, it is a new ERP system (Dynamics AX) and currently the entire backup folder for all versions of the DBs, live, test, training etc only come to 8gb. I'm expecting the server to be around 200gb ultimately. It has a 785gb iodrive so it should never exceed that and I suspect it'll never come close.

Currently the bak and trn files are copied off to another server so we have a DB backup it is more a complete machine restore that I'd like to be able to do, is this practical?

Msbackup. Do the entire server and the system state, if you've media you can mount during a fresh install of the OS, you can restore everything bare metal style.

Wow didn't think msbackup was so powerful, does it play nice with SQL? Seemed too simple of an answer so I'd overlooked it.