Restoring SQL Server Backups using SSIS from one server to another.

The best way of checking SQL Server backups is to restore them and run DBCC CHECKDB on the restored database. To do this regularly means that you need to automate the task. I will show you how, with SSIS. You’re a good DBA, and you know that the first thing a DBA has to do is make sure that backups are done.  Every one of us knows that our backups are the first most important thing we’re responsible for, so we make sure they’re done.

So my next question to you is ‘have you tested them?’

The first thing we want to do is move the backup file from the production instance to the development instance. In my case, I use LiteSpeed For SQL Server (awesome tool) for my backup strategy.This solution will also work with native SQL server backups. After the file transfers to the development instance. We will restore the database and run DBCC CHECKDB.


OK, let’s get started.


This my overall SSIS Solution that we will work on. I will describe in detail every step in the solution.  You will also need two connection managers one pointing at your Production Server and Development Server.



The first step: Copy database from Production to Dev

DECLARE @databasename varchar (200)
DECLARE @cmd varchar (300)
DECLARE @pathfile varchar (150)
DECLARE @destinationfile varchar (150)
SET @destinationfile=’\\DEVELOPMENTSERVER\Databases’
–set @cmd=@databasename + (SELECT CONVERT(char(10), GetDate()-2,126))+’.ls’
–select @databasename + (SELECT CONVERT(char(10), GetDate()-2,126))+’.ls’
set @cmd=@pathfile+@databasename + (SELECT CONVERT(char(10), GetDate(),126))+’.ls”‘+SPACE(1) + @destinationfile
select @cmd
exec master.dbo.xp_cmdshell @cmd



The second step: Kill all the connections to restore database


Declare @dbname sysname

Set @dbname = ‘DATABASENAME’

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Execute (‘Kill ‘ + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid

The Third step: restore Database to Dev using LiteSpeed for Sql server store procedure.

DECLARE @databasename VARCHAR (50)
DECLARE @filenamelocation VARCHAR (200)
DECLARE @databasename1 VARCHAR (200)
SET @databasename1=’databasename’
SET @DATABASENAME=’databasenameFull_’
SET @filenamelocation=’F:\Databases\’+@databasename + (SELECT CONVERT(char(10), GetDate(),126))+’.ls’+SPACE(1)
exec master.dbo.xp_restore_database @database = @databasename1,
@filename = @filenamelocation,
@filenumber = 1,
@with = N’REPLACE’,
@with = N’STATS = 1′,
@with = N’MOVE N”database_Data” TO N”F:\mssql\data\database_Data.mdf”’,–logical name goes here
@with = N’MOVE N”da_log” TO N”G:\mssql\logs\database_log.ldf”’,—-logical name goes here
@affinity = 0,
@logging = 0


The Fourth step: DBCC CHECKDB- I use Ola Halgreen’s script. 

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘database_name’,
@CheckCommands = ‘CHECKDB’



The Fifth step: Remove old backup files older than 2 days. I will be using powershell.

–Deletes files ending in .ls extensions in a folder
DECLARE @Dos_cmd VARCHAR(1000)
DECLARE @Powershell_cmd VARCHAR(1000)

SET @Powershell_cmd = ‘”& ‘+
‘Get-ChildItem ”\\DEVELOPMENTSERVER_NAME\Databases” | ‘+
‘where {$_.lastWriteTime -lt ((Get-Date).AddDays(-2)) -and ($_.Extension -match ”LS”) } | ‘ +
‘Remove-Item -force ” ‘

— create the full DOS comand
SET @Dos_cmd = ‘ “”C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe” ‘+ @Powershell_cmd

–Run it using SQL command shell xp
exec master..xp_cmdshell @Dos_cmd



The Final step is scheduling a SQL Server Agent job that will be ran weekly . By running this SSIS package on a regular basis you can test to make sure your backups are sound and usable, and that your production databases don’t have hidden corruption, without impacting your production environment.

Please feel free to comment.