Help with understanding Backup design
-
Hi,
I have two questions:
I have been trying to learn how to design a proper backup for a company.
Now I know this is dependent on the company's needs but it seems to be all over the place.
For example, one place I read the following:
1st of each month -- Do a full database backup.
Every day at midnight -- Do a differential database backup.
Every 15 minutes -- Do a transaction log backup.
In another, I read
Full backup every day
Differential backup every hour.
And a log backup every 15 minutes.
Question one:
Is there anything more that I can use to determine more what I should use?
Question two:
In all the things I read about how to do a backup, while they tell you how to set the Full backup; they do not tell you hot set up the log backup. How would you do that or get it if you were to restore it?
Thank you
-
Depends on your company needs. I perform nightly full backups of all SQL servers since activities for SQL servers are lower during non-business hours. Transaction log backups is dependent on the criticality of the data. For instance, on a SQL server that hosts our ERP system, transaction log backups are taken every 15 minutes. If we have a failure, we can go back to that 15 minutes before. To do so you will have to restore most recent full backup and apply all the transaction logs backup done after the full but before the failure. This will get you back to the most recent point of time before the failure occurred. The below show the last full backup with all of it transaction log backups to get you back to the chosen point of time. I do not perform differential database backup as it is not needed.
-
If your database is small, a daily full will run quickly and take few resources. If it's huge, may be better to run a Full less frequently and take Differentials daily, with T-Logs
-
Thanks for the reply, can you please tell me how do you set a backup for the transaction log backup?
Thank you
-
Hi and thanks for the reply.
I am also trying to learn exactly how you set this up.
So in my example:
Full backup every day
Differential backup every hour.
And a log backup every 15 minutes.
Would I go in and create a Full backup.
Then create a differential backup,
and finally, create another backup for the transaction log backup?
Thank you
-
Looks like you have never done this before so I'll give you the easiest way. Create a maintenance plan using the wizard for each or create on Plan with multiple subplan for each backups type. From the wizard you can specify which backup and schedule desire. After completion of the plan you will see them SQL Server Agent under job. More advance way to create backup plans can be found here: https://ola.hallengren.com/sql-server-backup.html Hope this helps as I been here before about 10years ago.
-
From the maintenance plan wizard you will need to create a task to cleanup your old backups using the task "maintenance cleanup task". This will delete old backups and retain only a certain amount of backups so that your backup drive does not fill-up and run out of disk space.
-
If your database is large, you make want to select "Compression".
Also, a "Clean-Up" task to delete old backups and logs after X days/weeks.
Probably dozens of videos or blogs on strategies, details & options.
And your backups should be backed up. If you're on a physical server, writing your backups to a local drive, and the drives crash, you may lose your database, and the backups.
-
Thank you this really helps.
-
thanks, everyone this is really great.
-
I'll leave you with one link as your answer. In the long term, look into using Ola Hallengren's Maintenance Solution. I would also strongly encourage you to look at Backup/Restore tutorials on places like MSSQLTips.com , SQLSKills.com or Pluralsight so you have a more complete understanding of this very critical topic. You can also go to blogs and see if they have a category for backups, like this one.
-
Another thought....TEST !
Practice restoring your backups to a test server, under various scenarios. Is a test server available ? If you need to restore production to a test area to recover specific tables, do you have room for "live" Prod, and "restore" Prod databases at the same time ?
Are you comfortable restoring a backup over an existing database ?
If you have to restore to a different server, do you know how to recreate SQL Logins & permissions ?
-
Thank you
This is great.
-
thank you
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply