Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Express Does Not Provide A Way to Setup Maintenance Plans Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:03 PM
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?
Post #1523439
Posted Monday, December 16, 2013 3:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
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
Post #1523461
Posted Monday, December 16, 2013 8:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,752, Visits: 28,150
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1523512
Posted Tuesday, December 17, 2013 9:54 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
Your answers are here. ^.^

.
Post #1523936
Posted Thursday, January 2, 2014 2:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:20 PM
Points: 8, Visits: 167
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.
Post #1527291
Posted Thursday, January 2, 2014 3:18 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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.


.
Post #1527306
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse