March 13, 2013 at 11:07 pm
Is it best practice to take backups of system databases and user created databases in separate through maintenance plans and jobs. Else we should take them together.
March 14, 2013 at 12:52 am
The best practise to do it separately.
Regarding to system databases:
They should be backup each time a server or database configuration are modified or added. These include Service Packs, Hot Fixes, Cumulative Update, login changes, job changes, operator changes, database configuration changes, SSIS package changes, replication changes, etc...
March 14, 2013 at 4:23 am
I want to implement only daily full backups and i am not taking taking any other backups for system databases is that a good idea
March 14, 2013 at 4:47 am
If you implement only daily backups then you are removing the possibility of performing point-in-time recoveries, something that in a production environment may result in data loss. With that in mind, I don't think daily backups only in a production environment is a good idea. In a development environment it may well be OK but that depends on the developers and their requirements.
As regards the system databases: This is a situation where you should know what is happening on your server. Your server/infrastructure team will be able to tell you when patches and updates are applied and your development team know whether any DDL or structural changes to the database have been made. That tells you when you need to backup the master database.
As the administrator, you know when jobs, SSIS Packages etc have been created so you know when to backup the MSDB database.
If you change the template for one or more databases, you will need to backup the model database. If you don't back it up appropriately, tempdb won't be created correctly the next time the instance is restarted. Something to think about there.
Ideally, you need to look at and assess in a very granular fashion what each system database does and decide for yourself when it needs to be backed-up.
Me? I fully backup everything once a day with a retention period of 14 days. Transaction Logs and Differential Backups are performaed as required on the user databases only.
Lastly, and don't take this the wrong way, if you are asking this sort of question, it wouldn't hurt to get your head in some books and learn a little more. If you don't know when to back a database up, then I doubt you know how to restore it properly and what the consequences of your actions might be.
March 14, 2013 at 5:13 am
Thanks for your advice and it is really helpful to me...
March 14, 2013 at 6:18 am
Sound advice above and much better than the words I had scribbled down 🙂
I let the business needs determine the backup strategy to a point. For example, if a database is only populated on a friday for reporting purproses then you dont need a full backup twice a day. If a database is used throughout the day and the business cannot afford any lost data at all then you need full data and log backups for point in time restores, and peace of mind 😀
Its why I dont particularly like maintenance plans for 'all user databases', each database has its disaster recovery own requirements.
'Only he who wanders finds new paths'
March 14, 2013 at 7:12 am
I've heard it said by someone here that "You don't need a backup strategy...you need a restore strategy". I don't remember who said it, but it stuck with me because it was so true. You should test your backups by doing some restores. Please save yourself some pain and don't have the first restore you do be a live one because you need it. Practice it and get used to how it works so you know the "ins and outs" of it before you need to do it for real.
You should also look into transaction log backups. As stated above, point-in-time restores are a fact of live and are sometimes necessary to recover data.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply