Solved

sql backups

Posted on 2015-01-30
7
68 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 49

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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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 49

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 49

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

756 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