Tag Archives: Database backup and restore in SQL Server

Automating Backup And Restore using SSIS

This particular post will throw light on how to perform backup and restore using T-SQL code and later describe the process to automate the same using SSIS.

Let’s begin

–Execute below code on Source Server Master Database


DECLARE @sourceDatabase nvarchar(100),

@backUpFileLocation nvarchar(100)

–T-SQL to perform backup

DECLARE @sql nvarchar(max),

@backUpFileName nvarchar(500)

SELECT @backUpFileName = @sourceDatabase + ‘_bac.bak’  –preparing backup file name

SET @sql =

‘BACKUP DATABASE [‘ +  @sourceDatabase + ‘]

TO DISK=N”’+ @backUpFileLocation + @backUpFileName +”’







Till this point, our backup is done and .bak file will be placed at backup file location specified in parameters.

Now it’s time to restore the back up. Here goes the code:

–Execute below code on Destination Server Master Database


DECLARE @DestinationDatabase nvarchar(100),

@backupfilelocation nvarchar(100), –same as specified while taking backup

@sourcemdffile nvarchar(100),

@sourceldffile nvarchar(100),

@backupfilename nvarchar(100), –same as specified while taking backup

@destinationMDFfile nvarchar(100),

@destinationLDFfile nvarchar(100)

–T-SQL code to perform Restore operation

DECLARE @targetDB nvarchar(100),

@sql nvarchar(max)

SELECT @targetDB = @DestinationDatabase

SELECT @sql = ‘

RESTORE DATABASE [‘ + @targetDB +’]

FROM DISK = N”’ + @backupfilelocation + @backupfilename + ”’


,MOVE ”’ + @sourcemdffile + ”’ TO ”’ + @destinationMDFfile + ‘\’+@targetDB +’.MDF”

,MOVE ”’ + @destinationMDFfile + ”’ TO ”’ + @destinationLDFfile + ‘\’ +@targetDB +’_1.LDF”




Automating Backup and Restore:

To avoid manual execution of queries on 2 servers, you can follow below steps to automate both operations:

1. Develop SSIS package and wrap T-SQL code in ‘Execute SQL Task’.

2. Use variables to pass parameters to ‘Execute SQL Task’ for fetching source\ destination servers\ database names, backup file location dynamically.

3. Develop a job that will pass parameters to your SSIS package variables and use these parameters in the package you developed.

4. Now just specify parameters in the job you developed and run it. No need to go to source and destination servers to run code and your entire process is automated.

Happy Coding!

If you have any constructive opinion, please write to me or post comments