SQL Server related thoughts

SSIS package to Sync databases between “like” environments, for instance production and test or acceptance.

This package was created in VS2008 and may have functionality only available in 2008 but will work against SQL2005 servers

There is some reliance that the environments will be the same – drive letters, file locations. Users are not synced as part of this package as generally these will be static (hopefully you are using groups) The job uses the admin UNC paths so the package needs to be run with administrative credentials of both servers or shares will need to be created with the appropriate permissions.

1. The package first queries msdb on the source server and retrieves the paths of the last full backups.
2. It passes the database name, path and full unc path into a foreach loop container.
3. The file system task copies the backup to the path defined in the FullDestPath variable
4. An execute SQL task sets the database (if it exists) to single user
5. An execute SQL task restores the database using the full admin unc path of step 3 – This could be changed to a share to allow the process to run with less privileges
6. The loop completes this for all non system or reportserver databases (this is defined in the sql script in step 2)
7. Success email is sent on completion
8. There is also an On error event handler at the root of the package that emails any package failure

Package Overview

1. Parameters

   1. 
   2. 

2. Retrieve Database name and last full backup Path

   1. Create an Execute T-SQL Statement task  against your Source server (msdb database)

1. With a full result set to an Object data type – here I have used rowset1
2. 
3. Use the SQL statement below,

WITH    BACKUPS
   AS (
 SELECT
    a.Server
    ,a.database_name
    ,B.physical_device_name
  FROM
  (
  SELECT
     CONVERT(NVARCHAR(MAX), SERVERPROPERTY('Servername')) AS Server
     ,msdb.dbo.backupset.database_name
     ,MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM
     msdb.dbo.backupmediafamily
     INNER JOIN msdb.dbo.backupset
     ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE
     msdb..backupset.type = 'D'
    AND msdb.dbo.backupset.database_name NOT IN (
    'Master', 'msdb', 'model', 'reportserver',
    'reportservertempdb' )
   GROUP BY
     msdb.dbo.backupset.database_name
  ) AS A
    LEFT JOIN (
  SELECT
     CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server
     ,msdb.dbo.backupset.database_name
     ,msdb.dbo.backupset.backup_finish_date
     ,msdb.dbo.backupmediafamily.physical_device_name
   FROM
     msdb.dbo.backupmediafamily
     INNER JOIN msdb.dbo.backupset
     ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE
     msdb..backupset.type = 'D'
  ) AS B
    ON A.[server] = B.[server]
   AND A.[database_name] = B.[database_name]
   AND A.[last_db_backup_date] = B.[backup_finish_date]
 )
SELECT
   [database_name]
   ,[physical_device_name] AS databasePath
   ,'\\' + [Server] + '\' + REPLACE([physical_device_name], ':', '$') AS FullSourcePath
 FROM
   [BACKUPS]

   

1. Set your result set
2. 

1. Use a ForEach Loop Container

   1. Set the enumerator to Foreach ADO Enumerator with your object source as your object variable .

1. 1. Map your variables

1. Add a File System Task

   1. Configure with your source and destination variable and a Copy File operation

1. Create T-SQL Statement Task against your destination server (master database)

DECLARE @databaseName VARCHAR(50)
SET @databaseName = ?
IF  EXISTS
(SELECT name FROM sys.databases WHERE name = @databaseName)
EXEC('ALTER DATABASE ' + @databaseName +  ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

1. Map your parameter like this

1. Create T-SQL Statement Task against your destination server (master database)

DECLARE @databaseName VARCHAR(50)
DECLARE @FullDestPath VARCHAR(MAX)
    SET @databasename = ?
    SET @FullDestPath = ?
EXEC(
        'RESTORE DATABASE ' + @databaseName + ' FROM DISK =
N''' +  @FullDestPath + '''WITH RECOVERY, FILE = 1, NOUNLOAD, REPLACE, STATS = 10')
GO

1. Map your parameters like this

1. Add a Send Mail  task using the following expressions

1. On the even Handlers tab

1. Create an onError handler at the root of the package

• Add a Send mail task with the following expressions

1. I also create a config file to allow the package to be used in different locations without editing the package itself
   1. databaseName – value
   2. DestPath – value ( this is either a share or UNC – it does NOT contain the server name – it uses the DestServer variable so it should be in the format “C$\sql\backups” or “mybackupshare”
   3. DestServer – value
   4. SMTPFailureTo – value
   5. SMTPFrom – value
   6. SMTPServer – value
   7. SMTPSuccessTo – value
   8. SourceServer – value