Moving SSIS packages

To copy IIS packages that are stored in MSDB from PROD to UAT I connected to the ‘Integration Services’ object of both servers within SSMS.

To ‘pull’ the package from PROD into UAT I first drilled down both trees (EG: server stored packages msdb ) to see which folders were missing.

The first one was called ‘Press’ so I right-clicked on the UAT msdb icon  and created a new folder with that name.

I right-clicked on the new folder and chose ‘import package’. This opened a config-box, within which I just filled in the ‘server:’ and Package path:’ values. The first contained the name of the PROD server (including instance).

The ‘package path’ box contained one parent folder (‘SSIS Packages’) and subfolders for each package. I opened the ‘Press’ folder and selected the package within. Once I clicked ‘ok’ twice the package was copied over.

Simple Log-shipping of ‘Actions’ database from London to Paris using SQL2000 Std


All the code below is freely available on the internet. I first saw it in the SQL2000 SDK from where it has been improved many-many-times-over. Rather than rehash the implementation here is my handover notes.


Transaction-logs (Tlogs) from the London database ‘Actions’ are backed up every hour to a warm standby in Paris and restored there.


Hourly a SQL Job in London (‘Log ship Incidents to Paris’) executes a stored procedure (‘sp_ShipLogOnce’) that backs up the Tlog directly to Paris (to E:Backups shared as ‘Backup$’) then runs the Paris SQL Job (‘sp_ApplyStandByLog’) that restores it.


There is a Windows batch file on Paris (‘PurgeFilesExceptRecent.bat’) that will delete old Tlogs every 2am. It has been initially set to ignore the newest 720 (being one per hour for 30 days).

Logins on Paris need to be regularly updated from London (at least monthly). The stored procedure ‘sp_help_revlogins’ will script out the London logins, from which any new, sql, non-svc, entries should be added to Paris.

The Tlogs need to be restored in strict order. After a temporary network failure all outstanding Tlogs can be restored by running this command at Paris …

C:ApplyStandbyLogs.bat E:Data S:Backups

Alternatively individual Tlog can be restored using the stored procedure …

exec Sp_ApplyStandByLog


  1. Disable the London LogShip job
  2. Apply final Tlog (sp_ShipLogOnce with @LocalUndoFile)
  3. Change Paris ‘Actions’ database to read/write, multi-user
  4. Enable the Paris LogShip job


If London is unavailable, only step-3 above is needed. IE:-

exec sp_dboption 'actions', 'dbo use only', 'false'
exec sp_dboption 'actions', 'read only', 'false'
restore database actions with recovery

Mirroring of BESMgmt from London to Paris using SQL Server 2008 r1 Std


  • First I changed the recovery model of the BESMgmt database to use the Full Recovery Model.
  • To manage the BESMgmt Tlog files generated from this I created a Maintenance Plan to backup the Tlogs every 6 hours to the E:Backups folder. These will be automatically deleted after two weeks (NOTE: The size of the TLogs can be reduced by increasing the backup frequency).
  • I reinstalled SQL Server 2008+SP1 on Paris ensuring that …
  •           The SQL service account used was SVC_SQL
  •           The SQL agent account used was SVC_AGT_SQL
  •           The collation was set to LATIN1_GENERAL_CI_AS
  •           The default location for datafiles was set to S:DATA
  •           The default location for logfiles was set to T:LOGS
  •           TempDB was located on the U-Drive

– Minimised potential connectivity delays by …

o Setting up Paris as a linked-server on London
o Ensuring the SQL Browser Services was active and working on both servers


To initiate mirroring I …
– Backed up the BESMgmt database and restored it on Paris with no recovery
– Backed up the TLOG and restored it on Paris with no recovery
– Configured Mirroring of BESMgmt on London GUI without a witness.


There are three methods of failing over / swapping the Mirror-Live (Principal) and Mirror-Standby (Partner) roles.
1. There is a ‘Failover button’ on the Principal SSMS GUI mirror-configuration screen. This should be used for testing, and for planned maintenance of the Mirror-Live server.
2. I believe ‘BES Manager’ can be configured to automatically failover the database. This should be the normal way failover occurs.
3. A sql-command can be executed on the Mirror-Partner as a last-ditch option should the Mirror-Live server become unavailable ( IE: alter database besmgmt set partner force_service_allow_data_loss). Data loss may occur.


The Mirror Monitor (screen within SSMS) can be viewed from either server by right-clicking the BESMgmt database and choosing Tasks / Launch database Mirroring Monitor. I have initiated mirror warnings within this tool which will log an error to the application log if Mirroring becomes slow.