sql backups

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?
LVL 4
Cobra25Asked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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
 
Benjamin MOREAUProject ManagerCommented:
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
 
Scott PletcherSenior DBACommented:
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
Problems using Powershell and Active Directory?

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

 
Cobra25Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Does that sound good? I also have VEEAM doing backups every hour.
Yes, sounds good if VEEAM isn't making database backups.
0
 
Cobra25Author Commented:
Vitor, why shouldn't veeam do SQL?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.