SSIS Backup and Restore

Backup and Restore task is to get backup a database and restore in another database or different server.


Below is an example package design which is explained step by step

Start with connection Managers as shown below:

Master: This is the "Master" database of our server through which all the tasks functioning in SQL Server.

Work: This is the database of server where we do all the tasks and need to get backup.

Workbackup: This is the database where we do restore of our existing database.

Transfer: This is to select and test the server connection for transferring database.

SMTP Connection Manager: This is your connection of Email Server for send mail task

Below is the step to connect with server:

Right Click in the Connection Managers box - Click "NewConnection" and select the type as "SMOServer" as shown below:

Click "Add" to get into "SMO Connection Manager Editor" and select the server name. Test the server connection using the "Test Connection" option and click "OK". See below:

And below is the step to connect with SMTP server:

Right Click in the Connection Managers box - Click "NewConnection" and select the type as "SMTP" and click "Add" to get into the "SMTP Connection Manager Editor" and enter the information as shown below:

Need to create variables to get parameter for all the tasks. See below:

For variable "BackUpfile", the location should be given as where the backup.bak details need to be saved.

Also for variable "WorkDataFile and WorkLogFile", use below query to get the log details of database

USE [xxx] --DB Name
GO
EXECUTE sp_helpfile

Start package with Setting SQL Server Name:

Add variable as shown below:

and enter script in the Sql Statement as "select ? = @@ServerName" to get the system server name.


Next is to Get Current time as shown below:

Add variable as shown below:

and enter script in the Sql Statement as "Select  ? =  Left(CONVERT(VARCHAR,GETDATE(),108), 5)" to get the system current time.

Now get two connector from the previous SQL task. Double click the Left side connector and update as shown below:

And connect the left connector to the "SQL BackUp work" task as shown below:

Now double click the SQL Backup task and Add variable as shown below:

and enter the script in the Sql Statement as below:

Declare  @backupFile nvarchar(100) = ?
BACKUP DATABASE [WORK] TO DISK = @backupFile WITH NOFORMAT,
 INIT, NAME = N'Full backup WORK', SKIP, REWIND, NOUNLOAD, STATS = 10 


*Need to mention the DB which we want to take backup to disk (Here it is work).


From SQL BackUp Task, connect to the SQL Restore Backup Task as shown below:

Add variables as shown below:

and enter the script in the Sql Statement as below:

DECLARE @WORKDataFile NVARCHAR(100);
DECLARE @WORKLogFile NVARCHAR(100);
SET @WORKDataFile = ?;
SET @WORKLogFile = ?;
RESTORE DATABASE [workbackup]
FROM DISK = ?
  WITH 
FILE = 1,
MOVE 'Work' TO @WORKDataFile, 
MOVE 'Work_log' TO @WORKLogFile,
NOUNLOAD,  
REPLACE,
STATS = 10
GO

*Need to mention the DB where we want to Restore the existing DB (Here it is workbackup)

Now Go back to the Get Current time and Double click the Right side connector and update as shown below:

And connect the right connector to the "Send Mail Task" as shown below:

Update the Send Mail Task as shown below:

*Can update the fields as it is required.

*****************************************************************

The below task will be created if required.  To get the permission to perform the task.

Enter the script in the Sql Statement as below: Modify it according to the requirement.

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE LOGIN [MIS\svc_CS_SS2008Agent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE LOGIN [MIS\svc_CS_SS2008Agent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO


/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE USER [MIS\svc_CS_SS2008Agent] FOR LOGIN [MIS\svc_CS_SS2008Agent] WITH DEFAULT_SCHEMA=[dbo]
GO


/*Apply permissions*/
EXEC sp_addrolemember N'db_owner', N'MIS\svc_CS_SS2008Agent'


/*server level permissions*/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_MDR_SpreadsheetS')
CREATE LOGIN [MIS\svc_MDR_SpreadsheetS] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO


/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_MDR_SpreadsheetS')
CREATE USER [MIS\svc_MDR_SpreadsheetS] FOR LOGIN [MIS\svc_MDR_SpreadsheetS] WITH DEFAULT_SCHEMA=[dbo]
GO


/*Apply permissions*/
EXEC sp_addrolemember N'db_datareader', N'MIS\svc_MDR_SpreadsheetS'
/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_MDR_SpreadsheetS')
CREATE USER [MIS\svc_MDR_SpreadsheetS] FOR LOGIN [MIS\svc_CS_SS2008Agent] WITH DEFAULT_SCHEMA=[dbo]
GO