SQL Clone
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-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24034 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
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98415 Visits: 33014
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)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1247 Visits: 599
Your answers are here. ^.^

.
dwalker-543874
dwalker-543874
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1247 Visits: 599
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