Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Express Does Not Provide A Way to Setup Maintenance Plans


SQL Server Express Does Not Provide A Way to Setup Maintenance Plans

Author
Message
defyant_2004
defyant_2004
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 146
We just installed a couple of SQL Server Express editions with a few standard databases.

Our DBAs are insisting that SQL Server Express does NOT provide the necessary tools to create maintenance plans. Is this true? If so, how are all the SQL Server Express Editions out there being maintained? I would suspect the Express Edition does offer at least a limited version of the Maintenance Wizard to run some basic tasks.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39006
defyant_2004 (12/16/2013)
We just installed a couple of SQL Server Express editions with a few standard databases.

Our DBAs are insisting that SQL Server Express does NOT provide the necessary tools to create maintenance plans. Is this true? If so, how are all the SQL Server Express Editions out there being maintained? I would suspect the Express Edition does offer at least a limited version of the Maintenance Wizard to run some basic tasks.


that is correct; Express is a stripped down version of SQL, and does not include the SQL server agent, which contains the ability to create jobs/ maintenance plans.

you could create scheduled tasks in the Windows Scheduled Tasks to execute powershell or sqlcmd calls which perform the same kinds of functions, but it's a much more scripted-out solution, rather than a powerful GUI to help you.
alternatively, you might buy a Developer version for 50 dollars and replace the express instance with that instead; you cannot upgrade an express version, i think; that would require backing up the databases scripting out all sorts of settings , roles and users , uninstalling express, and installing Develoepr(or any otehr fuller version), and finally running the scripts and restorign the databases.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17641 Visits: 32268
Nope. No maintenance wizard, no SQL Agent, so to maintain Express editions you'll need to have some type of scheduling service and create T-SQL scripts for all the maintenance.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 585
just a quick note: Powershell and other batches from windows task is the way I've managed it in the past. Developer edition is not supposed to be used in production. There are licensing issues there. It is a lot more scripting, but it is possible to manage it entirely from the windows task scheduler.


One thing to think about... how much are you saving by using express instead of standard edition when you add in the cost of the DBA's to work on scripting out jobs manually? If it forces you all to need to hire an additional DBA, you may just be costing yourself money.

.
dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 1958
Most maintanace functions can be done in SQLExpress through SQL Server Management Studio, but manually. Most Express instances are maintained when a DBA gets around to it, with utilities developed by vendors of the applications hosted on them, or only when something breaks.

In my opinion, SQLExpress is appropriate for 1) Configuration databases that are rarely updated (when they are they should be manually backed up and copied off server), and 2) testing complex scripts or commands that you're nervious about running on a DEV server the first time.

That said, it is possible to script out maintanance routines and run them from the command line via Windows Task manager, but the more databases you have, the more unweildy and time consuming the management of this will get. If the data is that important and you don't have 3rd party vendors with their own maintananace tools, consider at least getting Standard Edition.
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3154 Visits: 3821
If you are setting up a lot of SQL Express instances it may be worth looking at SQL FineBuild.

The latest 3.2.0 release detects if Express Edition is being installed and sets up a number of Windows tasks to do the normal database maintenance processes (backups, index rebuild, etc). If an edition with SQL Agent is installed, FineBuild sets up the same tasks to run within SQL Agent.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
bharat546
bharat546
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
I have already installed the SQL server 2012 Express edition. Can I now install SQL Server finebuild to automate certain tasks.
will it overwrite the current sql Server installation?
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3154 Visits: 3821
Can I now install SQL Server finebuild to automate certain tasks


Sorry, FineBuild can not be used to configure an existing instance. This is something tha tmight get added, but for now the answer is no.

If you use FineBuild to install a new instance, then it will install the maintanance tasks for that instance.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search