June 26, 2012 at 3:53 pm
D. Clark (6/26/2012)
Lynn Pettis (6/26/2012)
Maintenabce plans are good for small databases or when you find yourself an Accidental DBA having to get something into place. I used them when I first started working with SQL Server but after a bit I found them limiting. I started writing my own processes so that I could have more control over the processing, particularly the rebuilding or reorganization of indexes. I also wanted more control over scheduling the backups of databases and not having them all in one job (or maintenance plan), especially when I had databases with different recovery models and recovery requirements.
I couldn't agree more. The devs before me used maintenance plans, but I am actually planning on getting away from them. My biggest gripe so far is that if I modify a job associated with a maintenance plan (like adding a step to invoke a script to compress and copy backups) then I went back and modified the maintenance plan, I would find that the maintenance plan would restore the job to how it was before I made my changes to the job. Maybe there is a better way to do this, but right now I am just trying to stabilize everything. Plus, it just works.
You should not modify a mantenance plan job directly, except to change the notifications. Everything else should be done in the maintenance plan.
June 27, 2012 at 6:15 am
Michael Valentine Jones (6/26/2012)
You should not modify a mantenance plan job directly, except to change the notifications. Everything else should be done in the maintenance plan.
I can agree with that, but that's why I want to move away from them. I feel like they are too restrictive. It's true that they are a great help to get things going quickly, but it just seems easier to just use jobs if you want finer control over what happens. In fact, I wouldn't be surprised if I eventually come to the conclusion that even jobs are too restrictive or troublesome, and I just start using pure T-SQL scripts in the future.
However, I did look for a solution to my problem, and it seems that I could just add a T-SQL statement task to run my powershell script that I added to the maintenance plan job. I would just need to invoke the script with the xp_cmdshell stored procedure.
June 27, 2012 at 7:35 am
Print this out and hang it on your wall: BACKUP ALL THE DATABASES
You can backup the resource database's files through a simple robocopy script. Tempdb is the only database that you shouldn't back up.
February 8, 2013 at 3:29 am
Hi All,
I know it’s an old thread but seemed a right place to ask this question. I want to confirm that what databases I need to restore back if we decide to rollback an upgrade.
For example:
1. We took backup of all the databases.
2. We installed SQL Server 2008 R2 Service Pack 2.
In case we want to rollback this upgrade we can use “Add or Remove Programs” to uninstall this upgrade.
I want to confirm that, once we have uninstalled the SP2 successfully using control panel, do we need to restore the backups of system databases master, model, msdb & mssqlsystemresource again? Or is it not required & it is done by the uninstall process itself?
Any insight on this is appreciated 🙂
PS: I can create a new thread if you think that would be more appropriate.
February 8, 2013 at 3:48 am
For SQL2008 onwards the uninstall process takes care of it, no need to restore databases. SQL2005 its uninstall\reinstall so you would need to restore backups (master and msdb at least to get your configuration back)
this is a sql2005 thread so perhaps not the best place to ask the question, but hey ho.
---------------------------------------------------------------------
February 8, 2013 at 3:53 am
Thanks George. Thanks a lot, you cleared the confusion. 🙂
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply