December 16, 2013 at 2:37 pm
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?
December 16, 2013 at 3:28 pm
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
December 16, 2013 at 8:16 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 2, 2014 at 2:50 pm
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.
January 2, 2014 at 3:18 pm
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.
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply