Solved

sql backups

Posted on 2015-01-30
7
69 Views
Last Modified: 2015-02-16
hi guys,

we run a sql application 24/7/365. I wanted to ideally backup this database every hour because even an hour loss of data is crucial, its running on vmware.

What recommendations do you have?
0
Comment
Question by:Cobra25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 9

Expert Comment

by:Benjamin MOREAU
ID: 40581176
Hello,

I think you can add a maintenance Plan to Backup the Database every Day (FULL every days or FULL + Incremental)
 
After that, you define another Maintenance Plan to backup the transactionnal logs every hour (for this, add an automatic delete of backups after 5 days for example).
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40584113
Transaction log backups are the only type of backups that allows you to restore to a point in time (keyword STOPAT) but for that you need to set the database recovery model to FULL.
Then, depending on the size of your database you can set a backup plan for daily full backups (for small databases) plus hourly transaction log backups.
If databases are larger then I'll recommend you for a weekly full backup and daily differential backups and keep the hourly transaction log backups.
For periods that isn't expect the use of the database you don't need the hourly backup (for example nights and weekends) so you can define better the schedule for your backup plan.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40584439
It's too much overhead to backup the whole database every hour, and even then it doesn't allow you to recover except to the specific time of the backup.

You need to put your dbs in Full recovery model, if they're not already, and do transaction log backups periodically during the day to insure recovery.  You could start with every hour and see if that works out ok.  Often it's best to have different dbs on different schedules; for example, most dbs might do tlog backups every hour, but a few large and/or very busy ones might need every 15 minutes.

You could also look into using differential backups to save backup time and reduce recovery time.  But be very sure you have the last full backup before the differential properly saved, as a differential backup is worthless without its corresponding full backup.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 4

Author Comment

by:Cobra25
ID: 40609287
I was thiking full every week, differentials night, and t logs every hour. Does that sound good? I also have VEEAM doing backups every hour.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40611773
Does that sound good? I also have VEEAM doing backups every hour.
Yes, sounds good if VEEAM isn't making database backups.
0
 
LVL 4

Author Comment

by:Cobra25
ID: 40611777
Vitor, why shouldn't veeam do SQL?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40611823
I meant you don't need to have two tools doing the same backups (SQL Server and VEEAM). Only use one of them.
The backup plan sounds ok but you just need to use one tool to execute it.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question