• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

Do I need to backup SQL now that we are using VMWare?

As an old-school DBA backing up your databases has always been a pretty important part of the job. Protect the data has been drilled into my head. But as we move forward with technology we are looking at SQL backups actually causing problems.

We are using VMWare Data Recovery, as well as vRanger to do point-in-time full system recovery and if I backup the database to a file it actually increases the backup of the VM because it is an incremental backup.

I wanted to see if there were other DBAs out there with similar problems and what everyone is coming up with as the best practice for this shift in technology.
0
zriecken
Asked:
zriecken
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Paul MacDonaldDirector, Information SystemsCommented:
Do you have a plan that allows for loss of your datacenter?  That is, what happens if all your VMs are destroyed?
0
 
pg_vinodCommented:
If you take every backup to file with timessttamp on it then it will never append.

BACKUP DATABASE [dba] TO  DISK = N'O:\MSSQL\BACKUP\dba_db_201110102030.bak'
WITH INIT,  NAME = N'dba-Full Database Backup'
GO
make sure you remove old backup files (1 day or older than 2 days) up on successful database backup.

In this case third party software will never get incremental backup file.

Thanks,
Vinod Pottekkatt
0
 
Anthony PerkinsCommented:
pg_vinod,

I suspect you may have posted in the wrong thread.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
BrandonGalderisiCommented:
Do you want to have to restore your VM in order to recover your data?  If the answer is yes, the answer is still yes you should backup.  If the answer is no, the answer is still yes.  You should always back up your database regardless of having a bare metal backup of the machine.
0
 
zrieckenAuthor Commented:
Paul - We have two data centers on property (for DR/Fault Tolerance), SANs that raid across the two sites, and we have VMotion to move the VMs as needed. If we loose one data center everything automatically comes up on the other side with little to no impact on the user.

Brandon - They aren't just bare metal backups - they are backing up all drives. We would have to restore the VM to get to data out. Our Recovery Time Objective (RTO - aka the amount of time system would take to recover function) is 4+ hours for onsite restore and 16+ hours for onsite tape restore. Our Recovery Point Objective (RPO - the acceptable amount of data lost in units of time) is between 15 mins for our mission critical systems and up to 24 hours for our other systems. So we with this recovery solution we are within that time frame.
0
 
Anthony PerkinsCommented:
I just wanted to make sure I understand, you are suggesting that one approach it that you don't do any SQL Backups (Full, Differential or Transaction) and instead rely on the VMWare backup.  Is that correct?  If so, what Recovery Model are you using?
0
 
zrieckenAuthor Commented:
Acperkins - I am suggesting that I would be using VMWare and the tools we have to be our only source of recovery. I would be setting the DBs over to a Recovery Model of Simple and then do a checkpoint with truncate (for older servers - I have to be more creative in 2008).
0
 
BrandonGalderisiCommented:
Why would you want the hassle and aggravation of restoring the VM to restore data out of the VM?  You won't want to restore the entire machine to restore data deleted from one table.
0
 
zrieckenAuthor Commented:
I guess that depends on how you store you backups. I have 100+ databases which consume multiple TB of data. Just do the the limitations of the amount of space they consume (with backup compression) I can only keep 48 hours worth of backups on the data store before I purge them and if I want to go further back than that I have to request a tape restore. It would be just as easy to SSIS the data out once it is restored.

As far as why... VM backups use differential so creating a new file every day means that every day the size of the database is having to be backed up again when it is already covered. To restore we just restore it into its own VLAN so it is a fairly easy process (at least for me I just ask and it gets done).  :)
0
 
Anthony PerkinsCommented:
>>I would be setting the DBs over to a Recovery Model of Simple and then do a checkpoint with truncate (for older servers - I have to be more creative in 2008). <<
If you are in Simple Recovery Model there should be nothing to truncate.  Further it is detrimental to the database to do so (fragmentation).

It seems you are bound and determined to rely on VM backups.  I have never heard of this done this way, but then I would never dream of having Production databases using Simple Recovery Model either.  So I would close this question by accepting your comment, just make sure if possible to get back to us in a reasonable amount of time say 12-18 months and give us a review of how it went.  I suspect we could all learn something from it.
0
 
Anthony PerkinsCommented:
Could you share with us your experiences backing up your databases? Did VM backups do it for you?  And if so have you managed to test them out?
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now