SQL 2008 R2 Databases Automated Restores

hi all,

i'm trying create ssis package automate restoring multiple existing backups our san, different sql server instance originated from.

i've created foreach loop, configured follows:
folder: \\backupserver\sql backup\
files: *.bak
retrieve file name: qualified
variable mapping configured user::bak , string data, , index of 0, no value or expression defined.

use tempdb    declare @bak nvarchar (max)  set @bak = '?'    declare   	@path varchar(1000)= ''+ @bak +'',  	@restoredatapath nvarchar(max)='\\sqlserver\r$\usrdata',  	@restorelogpath nvarchar(max)='\\sqlserver\s$\usrlog'    declare @table table  (  	logicalname varchar(128) ,  	[physicalname] varchar(128) ,  	[type] varchar ,  	[filegroupname] varchar(128) ,  	[size] varchar(128) ,  	[maxsize] varchar(128) ,  	[fileid] varchar(128) ,  	[createlsn] varchar(128) ,  	[droplsn] varchar(128) ,  	[uniqueid] varchar(128) ,  	[readonlylsn] varchar(128) ,  	[readwritelsn] varchar(128) ,  	[backupsizeinbytes] varchar(128) ,  	[sourceblocksize] varchar(128) ,  	[filegroupid] varchar(128) ,  	[loggroupguid] varchar(128) ,  	[differentialbaselsn] varchar(128) ,  	[differentialbaseguid] varchar(128) ,  	[isreadonly] varchar(128) ,  	[ispresent] varchar(128) ,  	[tdethumbprint] varchar(128)  )    declare   	@logicalnamedata varchar(128) ,      @logicalnamelog varchar(128)  insert  @table  	exec   (   	'  	restore filelistonly   	from disk=''' + @path + '''      '  )    declare @restorescript nvarchar(max)='restore database '+(select top 1 logicalname @table )+'   disk =''' + @path + ''' file = 1 '    select  @restorescript +=char(10) + ',move  ''' +  logicalname + ''' ''' +           @restoredatapath  + logicalname + right(physicalname,4) + ''''  	from   @table  	where  type = 'd'     select  @restorescript += ' ,move  ''' +  logicalname + ''' ''' + @restorelogpath  + logicalname + '.ldf'''  	from    @table  	where   type = 'l'    set @restorescript += ' , nounload, replace, stats = 10 '  exec (@restorescript)    

when running dtsx package job on sql server instance, completes databases in sql backup directory aren't restored.
when using package execution utility server, reports 'error: cannot open backup device 'p:\?'. operating system error 123...'
p:\ drive isn't utilised of backups, or the script, default backupdirectory of sql server instance trying restore onto.
can offer advice on how resolve issue?

thanks

 



the backups local (where server is) drives, package cannot operate remotely on them. proper approach issue backup move files target, restore.

---

arthur my blog

SQL Server  >  SQL Server Integration Services