Import and Export SQL 2005 Maintenance plans

The way SQL 2005 produces Maintenance plans is very different from SQL 2000. Instead of using the extended stored procedure xp_sqlmaint to execute and the system tables of the MSDB database to store the package – SQL 2005 uses SSIS (SQL Server Integration Services) to create and store the Maintenance plans.

To Export an existing maintenance plan from an SQL Server 2005

• Click connect in the Object Explorer
  
• Select "Integration Services"
  
• Enter credentials for the server
  
• Open the folder: "Stored Packages" -> "MSDB" -> "Maintenance Plans"
  
• Right click the package you want to export
  
• Select Export package
  
• If you want to export the package to another server or edit it – using BIDS (SQL Business Intelligence Studio) – select "File system" in the Package Placement dropdown menu.
  

To Import or copy an existing maintenance plan from one server to another

• On the server you want the maintenance plan copied TO.
  
• Click connect in the Object Explorer
  
• Select "Integration Services"
  
• Enter credentials for the server
  
• Open the folder: "Stored Packages" -> "MSDB"
  
• Right Click the folder "Maintenance Plans" and select "Import Package"
  
• In the dropdown "Package Placement" select SQL Server
  
• Enter the name of the SQL server where the source maintenance package exists.
  
• Click the … besides the Package Placement box and select the package you want to import from the Maintenance Plan folder.
  

Moving system databases

When installing SQL Server 2000/2005/2008 many often forget where the system databases go on the filesystem. They are by default placed into C:\Program Files\Microsoft SQL Server \.... – Which we properly all can agree is not the optimal placement.

Note that to move the system databases require SQL server downtime, it doesn't take long though (depending on the size of course)

DISCLAIMER:

Please test this recipe on a test server before trying it in production – failing at any step will invalidate or totally stop you SQL server installation.

 

To move the system databases do as follows:

1. Shut down the SQL Server services – all of them
   
2. Start a command prompt (and keep it open)
   
3. Start the SQL server service in this nonstandard trace mode that skips recovery of the databases (except master)
   
4. NET START MSSQLSERVER /f /T3608 (or NET START MSSQLSERVER$Instancename /f /T3608 if you have a named instance)
   
5. Now modify the logical file placement by script:
   DO NOT MOVE THE FILES PHYSICALLY YET
   

Adapt the new file location to match YOUR filesystem

ALTER DATABASE MODEL MODIFY FILE (NAME = modellog , FILENAME = 'G:\SQLLogFiles\modellog.ldf')
GO
ALTER DATABASE MODEL MODIFY FILE (NAME = modeldev , FILENAME = 'H:\SQLDataFiles\model.mdf')
GO

ALTER DATABASE TEMPDB MODIFY FILE (NAME = templog , FILENAME = 'G:\SQLLogFiles\templog.ldf')
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME = tempdev , FILENAME = 'H:\SQLDataFiles\tempdb.mdf')
GO

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBlog , FILENAME = 'G:\SQLLogFiles\MSDBlog.ldf')
GO
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBData, FILENAME = 'H:\SQLDataFiles\MSDBData.mdf')
GO

ALTER DATABASE master MODIFY FILE (NAME = master , FILENAME = 'H:\SQLDataFiles\master.mdf')
GO
ALTER DATABASE master MODIFY FILE (NAME = mastlog, FILENAME = 'G:\SQLLogFiles\Mastlog.ldf')
GO

As Per SQL Server 2008 the MSSQLSYSTEMRESOURCE database cannot be moved ! But in SQL Server 2005 the database location must be the same as the Master database.


For SQL 2005 use:

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'H:\SQLDataFiles\mssqlsystemresource.mdf')
GO
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'H:\SQLDataFiles\mssqlsystemresource.ldf')
GO

The MSSQLSYSTEMRESOURCE database file and log file must remain on the same drive !

 

1. Now stop the MSSQLSERVER Service: NET STOP MSSQLSERVER (or NET STOP MSSQLSERVER$Instancename)
   
2. Move the files physically to the new location you have specified in the script
   
3. Change the startup parameters of the the SQL Server service in SQL Server Configuration Manager to match the new location of the master database. Eg:
   -dH:\SQLDataFiles\master.mdf
   -eH:\SQLDataFiles\log\ErrorLog
   -lG:\SQLLogFiles\mastlog.ldf
   
4. Start the MSSQLSERVER services normally
   

Please test this recipe on a test server before trying it in production – failing at any step will invalidate or totally stop you SQL server installation.