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 are attempting to setup SQL Server Maintenance Plans, however, our SQL Server Express installations do not have Maintenance Wizard or Designer. How can maintenance plans be setup on Express versions of SQL Server?
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
It can't, two reasons, SSIS is not available in Express and maintenance plans are SSIS packages, and there is no SQL Agent in Express. There are some workarounds but you have to "roll your own".

CEWII
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: 17603 Visits: 32267
Please don't cross post questions between forums. As you can see, we all go to many (most?) of the forums to answer. Cross posting just confuses the conversation.

----------------------------------------------------
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
Your answers are here. ^.^

.
dwalker-543874
dwalker-543874
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 190
I just completed setting up an automated backup of SQL Express databases, so I'll share what I did.

The Maintenance Plans and SQL Agent are not available in SQL Server Express. You will not have a GUI to work with, so knowledge of TSQL scripting is a must. The good news is that if you are not strong in TSQL, if you have access to SQL Server Standard or higher, you can setup similar plans within the GUI and script them out. Then you can edit to fit your express instance(s). There is also a wealth of information out there on "the net", just Google away.

First, create a blank text file to hold the TSQL commands. I called mine "SQLExpressBackups.sql". Then edit that file and place the maintenance commands you want to run in it. For example...

USE [master]
GO
BACKUP DATABASE MyDatatbase TO DISK = N'X:\Backups\MyDatabaseBackup.bak' WITH NOFORMAT, INIT, NAME = N'MyBackup';
GO

After you have all of the TSQL commands you need, save the file and create another blank text file. This next text file will actually be a BAT file, so be sure to save it with the .bat extension. I named mine "sql_backup.bat". Edit that file and place the SQLCMD command that will execute the backup/maintenance commands you created in the first step. Example:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S(local)\your_instance_name -UBackupOperatorUserName -PBackupOperatorPassword -i c:\backup\SQLExpressBackups.sql

The final step is to create a task in Windows' Task Scheduler that will execute the *.bat file you created on the schedule you need.

One "gotcha" with any of this is permissions. Be sure the operator you specify in the bat file after -U has permissions to perform the jobs you are scheduling. Additionally, be sure the user you specify in Task Scheduler has permissions needed to execute the bat file.

Good luck, I hope this works for you or you found your answer elsewhere.
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
http://www.dbaonthego.com/2013/12/powershell-backups-and-post-frequency.html

this is what I have running on my express box. It also deletes older backups for me after a certain amount of time. It should be easy to copy and make it work for what you need.

.
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