August 5, 2014 at 9:34 am
I'll start off by saying that I'm not a SQL admin by any stretch of the imagination. I work for a consulting company and do IT work for many different clients of ours.
One of our clients has a SQL 2008 database that is updated very frequently during the day. The director there would like us to do a full backup every night at 2am (which is currently being done) but would also like backups running every 30 minutes during the day so that if something happens and they need to do a restore, they won't lose everything they've done since the 2am full backup. He would like it to run from 8:30am to 1:30am daily, and then after the full is done at 2am, all the backups that were done during the day could be deleted.
The backup software they are currently using is Symantec Backup Exec 2012.
What would be the best way to set this up? As I'm not a SQL admin, I'm not familiar enough with what is what with the backups. From what I can tell, and please correct me if I'm wrong, incremental backups are transaction log backups, and differential backups are of the database itself. I'm not sure which would need to be backed up to give him the protection is he looking for.
Also, would this be done via Backup Exec or would I do it through SQL Manager?
Thanks in advance for any help in this matter. Please ask me anything you need to know!
August 5, 2014 at 2:50 pm
Since you are new to SQL I would recommend using the SQL Maintenance Plan Wizard to set up your backups. It is easy to use and setup for doing basic database backups.
What you are looking to do is as follows (assuming you are backing up to a local drive), make sure your database is in Full recovery mode first:
Take a full backup (Nightly) - Maintenance Plan can help you schedule this.
Take a transaction log backup (every 30 minutes) - Maintenance Plan can help you schedule this.
The flow above will achieve your RPO (recovery point objective) of 30 mins of data loss. To remove the old backup files you can use the maintenance plan again to remove backups files older than x days. Make sure you are testing your backups and saving them at least for a couple of days, that way if one backup is bad you can still go back and try another one.
This is a great article that explains how to backup Backup Under the Full Recovery Model
The restore plan would be you first restore the full backup and then you restore all the transaction logs in order they were taken. The differential backups are a convenience thing. In a recovery situation rather than restoring the full backup and 48 transaction log file backups you could instead restore the full backup, the latest differential backup and (assuming you are doing a diff backup every hour) 2 trans log backups.
Remember what I outlined above is very basic and is based on some assumptions around your server configuration.
August 5, 2014 at 4:03 pm
What is doing the full backup currently being done?
In order to know if you can even do log backups against the database you need to know what recovery model the database is in. You can do this through the Properties window of the database in SSMS (right-click), it will be under the Options page of the window. You can also run this query to find out:
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id NOT IN (1,2,3,4);
GO
As long as the "recovery_model_desc" shows FULL or BULK_LOGGED you can do log backups against the database. If it is not then you will have to change it, under the options page mentioned above. If you have to change it, log backups cannot occur until a full backup is taken.
Depending on how large this database is I would actually suggest doing a full, log backups, and then differentials:
2:00 AM - FULL
8:30 AM - 1:30 AM -- LOG
12:00 PM - DIFFERENTIAL
This will greatly reduce the number of files needed for recovering when an emergency hits. You can setup one maintenance plan with three different sub plans, setting the schedule accordingly. You can add a maintenance cleanup task for the files to be older than 1 day. I would ensure the full backup of the server (file system level) is picking up the native backups from this maintenance plan as well.
I would also ensure integrity check is being done on the database, possibly add it to the FULL sub-plan. If it cannot be run that frequently then I generally just add a plan for it to be executed every week.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply