| Author |
Topic |
|
Abu-Dina
Posting Yak Master
206 Posts |
 Posted - 2012-08-22 : 06:08:51
|
| Good morning,We have 5 SQL Servers used by operations team.QBDB01 - 62 DatabasesQBDB02 - 41 DatabasesQBDB03 - 39 DatabasesQBDB04 - 31 DatabasesQBDB05 - 28 DatabasesThe databases are mainly staging databases where data files are imported, scrubbed, merged etc.. It's usually one person working on the database at any one time.The current backup process works like this:1) All user databases set to Simple recovery model2) A SQL job is run manually every two weeks to perform a full backup of all the databases except for master and tempdb3) Another job which performs differential backups is run daily at midnight.They've had this setup for a number of years now but I'm thinking of creating a maintenance plan on each SQL server to perform the backups instead. Would this be a good idea?Any thoughts on this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-22 : 11:39:32
|
| how come you're not backing up the master db?Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-22 : 14:10:38
|
| Abu-Dina - do you mean model and tempdb ?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
komkrit
Yak Posting Veteran
60 Posts |
Posted - 2012-08-22 : 14:15:06
|
| SQL Agent Job can achieve the same goal as Maintenance Plan.While it's already done, you don't need to do Maintenance Plan to got the same thing.But !!! if you look into the advantages of Maintenance Plan over SQL Agent Job, focus something that benefit for you.If it's not worthy to do, leave SQL Agent Job to be the same.- Maintenance Plan give a friendly UI, don't need to remember any command, just click and go.- Don't need to make script yourself, especially some task that need to create BATCH Command (e.g. delete file older than xx days).- Most important part, dynamic database name, you can create task to backup "All User Databases" without specifying exactly database name.This is beneficial to the system that new databases were added frequently. Just specify "All User Databases", it will automatically create lists of databases to be backup at run-time.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulWelcome for all questions. Let us know if our solution solved your problem. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-22 : 15:35:19
|
quote:
Originally posted by tkizer
I don't see why you need a maintenance plan for this, but one is fine. I would just stick with the current setup though, no use spending time on something that is already setup fine.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
That's a fair point and it just shows how confused I am at the moment.The current setup is fine you're right. There are some things missing:1) masterdb is not backed up and this I can change easily. It's being intentionally left out from our backup sproc so I can make this change no problems there.2) the fortnightly SQL job is being run manually. The job needs a schedule.The other thing I want to do is setup email notification (currently disabled - going to take a long time to get approval to enable it)Something I would like to implement is a reports that I can send to management about the status of the backups, db growth on servers etc.I'd be interested to know if anyone has come across something similar?Thanks. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-22 : 15:39:12
|
quote:
Originally posted by jackv
how come you're not backing up the master db?Jack Vamvas--------------------http://www.sqlserver-dba.com
I don't know I started this new role last month. From knowledge gained so far I also agree it should be backed up along with the other databases. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-22 : 15:40:29
|
quote:
Originally posted by jackv
Abu-Dina - do you mean model and tempdb ?Jack Vamvas--------------------http://www.sqlserver-dba.com
The current sproc backs up all databases except for tempdb and master. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-22 : 15:48:13
|
quote:
Originally posted by komkrit
SQL Agent Job can achieve the same goal as Maintenance Plan.While it's already done, you don't need to do Maintenance Plan to got the same thing.But !!! if you look into the advantages of Maintenance Plan over SQL Agent Job, focus something that benefit for you.If it's not worthy to do, leave SQL Agent Job to be the same.- Maintenance Plan give a friendly UI, don't need to remember any command, just click and go.- Don't need to make script yourself, especially some task that need to create BATCH Command (e.g. delete file older than xx days).- Most important part, dynamic database name, you can create task to backup "All User Databases" without specifying exactly database name.This is beneficial to the system that new databases were added frequently. Just specify "All User Databases", it will automatically create lists of databases to be backup at run-time.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulWelcome for all questions. Let us know if our solution solved your problem.
All fair points.Of course you are right but i am not a seasoned DBA Im afraid so maybe I'm going for the easier solution! everything that a maintenance plan does can be done with TSQL and SQL jobs it's true I know :)I may need to take a step back and re-think my whole approach. I must sound so confused lol. See my reply to Tara above. I'd be interested to know your thoughts.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-22 : 16:52:54
|
Heh.. I actually meant master not tempdb sorry! (i've corrected my error above).Thanks for pointing this out  |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-22 : 16:55:00
|
| This answers my question also:http://www.sqlservercentral.com/articles/Backup+%2f+Restore/66564/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-23 : 01:24:02
|
| Depending on how the environment size , it's of value to create a report , which run regularly. The report should include information on every database - including when it was last backed up. That way you can pick up on any anomalies in your backup strategyJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-23 : 03:16:23
|
quote:
Originally posted by jackv
Depending on how the environment size , it's of value to create a report , which run regularly. The report should include information on every database - including when it was last backed up. That way you can pick up on any anomalies in your backup strategyJack Vamvas--------------------http://www.sqlserver-dba.com
Thanks Jack.That's the idea. The IT Manager who runs the backup jobs thinks there are databases being missed in the backup process. A report should hopefully highlight this if it's true.Will report back in a couple of weeks on my progress.Regards. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-23 : 13:15:04
|
| OK, let us know how you get onJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
Is using Maintenance plans a good idea for this?