Solved

sql backups

Posted on 2015-01-30
7
65 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
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 46

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
Save on storage to protect fatherhood memories

You're the dad who has everything. This Father's Day, make sure your family memories are protected. My Passport Ultra has automatic backup and password protection to keep your cherished photos and videos safe. With up to 3TB, you have plenty of room to hold the adventures ahead.

 
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 46

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 46

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

Are your end users making ugly email signatures?

Have you left it up to your end users to create their own email signatures? Are they forgetting to add the company logo or using garish font colors? Take control and ensure all users have the same email signature.

Question has a verified solution.

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

Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…

863 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now