Solved

sql backups

Posted on 2015-01-30
7
67 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 48

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 48

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 48

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

837 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