- Check Database Integrity
- Shrink Database
- Reorganize Index
- Rebuild Index
- Update Statistics
- Clean Up History
- Execute SQL Server Agent Job
- Backup Database (Full, Differential, Transaction Log)
- Maintenance Cleanup Task
The easier GUI way
1. Open SQL Server Management Studio
2. In Object Explorer (left pane), click "Connect", choose "Integration Services..."
3. Login into the SQL Server with credentials
4. Expand SERVERNAME (Integration Services), and you'll see the below structure
- Running Packages
- Stored Packages
- File Systems
- MSDB
- Maintenance Plans <- this is what you want
The harder script way
USE MSDB
SELECT name
,PlanXML=CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
FROM sysdtspackages90
haha, can't stop laughing at how hard the harder way is :)
ReplyDeletedont you have to edit the connection and stuff when importing into the new sql server machine?
ReplyDeleteit seems like "the harder script way" is not finished. what do you need to do after the select?
ReplyDelete2008 doesn't offer a "Package Placement"....only Package Path which won't let you use an unc or other tree path....only existing sql folders.
ReplyDeleteIn SQL 2008 SP1 you may need to use sysssispackages table instead.
ReplyDeleteThanks,
Rob Kostecki
@RobK...thx for tip on new table name.
ReplyDeleteOk...so I ran query and it returns, amongst a few other records, the maintenance plan I seek to export to another SQL Server. So do I now just need to insert that value into the other server? As in:
INSERT INTO sysssispackages (packagedata)
VALUES ('<DTS:Executable...')
It seems like I will probably first need to modify the packagedata value to fit the params of the new server (chiefly, the server name).
Then I guess it might be wise to manually create the maintenance plan (so SS creates all the IDs etc), with the same name, using SSMS. Then instead of the INSERT, do an UPDATE query with the new packagedata value.
Sound about right? Or should I skip the step to create the MP manually? And if so, has anyone developed an INSERT that takes care of adding the required IDs as well?
- anthonyx26
Ha, even myself don't use Import/Export as I just manually create a new MP with the GUI
ReplyDeleteI couldn't recall why I needed to export or posted this article
@anthonyx26 - I guesst trial and error! no harm in inserting when you can delete it easily
Have you seen and used Ola Hallengren's award winning solution?
ReplyDeleteI use it across 18 Servers and it works well for us. Offcourse, test it on some Development Server in your environment before you roll out. You can obtain it from: http://ola.hallengren.com/downloads.html
The MaintenanceSolution.sql script contains and creates all the Jobs. I modified it slightly for our environment, not to create the Backup Jobs, since I have already put in place our own custom backup solution.
Hello,
ReplyDeleteA SQL maintenance plan for rebuilding indexes and setting the index free space percentage to 10% within the Symantec/Arellia databases should be scheduled to run at least monthly (preferably weekly). This maintenance plan should also be configured to update column statistics (index statistics are updated during the index rebuild process).Server Maintenance
Awesome it works "GUI"
ReplyDeleteserverbuddies provides Quality remote server management including troubleshooting, Server Management, Plesk Suppor, Server Maintenance, Server Monitoring, Server Troubleshooting and support for a large variety of customers at affordable rates. Here are option for Server Management, Server Maintenance, Server Monitoring, Server Troubleshooting, Server Optimization, Plesk Suppo, Linux Support, cPanel Support and Plesk Support.
ReplyDelete.........