I studied about scheduling backups with SQL Server Agent and Maintenance Plans (which leads to the SQL Server Agent).
My first concern is, what is the best way to schedule my backups?
I want full backup every day, differential backup every hour and transactional log backup every 15 mins.
My second concern is, is this a good practice?
I noticed an issue when I used SQL Server Agent. My full and differential backups are overwritten. I am ok with having the full backup overwritten but not for the differential since when I need to recover it, I would only have one differential backup which is not the purpose of differential backups.
How can I not let this overwrite on previous backups?
The last concern and question is regarding the way to implement the backup scheduling. I am going to use Maintenance Plans , and may schedule three different backups. One for full, one for differential and one for transnational.
Is this the best practice for a database that is used everyday by users? (at least 2000 transactions per day)
Best Answer
Adding to the answer of Shekar Kola.
Personally I always suggest and use custom scripts over maintenance plans as these are easier to deploy on multiple servers, and easier to configure.
If you're going for the route of the custom scripts instead of the maintenance plans, consider using Ola Hallengren scripts. Ola hallengren website
These are some amazing well documented and frequently used scripts that cover everything about backup, index maintenance and itegrity checks. These scripts get recommended by some of the best DBA's around.
The backups wont overwrite eachother and the script will add dates to the .bak files.
The only thing you have to do is run the scripts, and add some schedules yourself. IF you want more configuration, check the documentation, there's a lot you can add/change yourself if you'd like.
UPDATE:
Yes nothing wrong with it.
Doing a diff during work hours can put a lot of stress on your database, and can hurt performance A LOT. Often this is not acceptable. Again, depends on your case, how long backups take, how often your data changes.
The amount of backups are normally decided by the business. You should look up RTO and RPO SQLSkills RTO/RPO. How many backups and which backups you need is related to those numbers, and to the amount of disk space you can use. An often used schedule is, 1 full each week, 1 diff each day, log backups every 10 - 15 min. But it of course depends on your case.
No they will not be fired with the backups, they are totally separate jobs. But yes, you should have some kind of Integritychecks and index maintenance running. Read up on the documentation of what exactly they do. Very short: Integrity checks check your data for corruption, and IndexOptimize will check your indexes for fragmentation.
But as a starter, just make sure you take backups, it's much more important to have backups not scheduled perfectly, than not having backups at all.