Automating Restore using extended Stored Procedure with SSIS

iDevThings

Hello All, 

I am looking to  create an SSIS package which calls the extended stored procedure "dbo.sqlbackup"  but running into some problems, are there any examples of how its being done? 

I  am using a Foreach loop container to retrieve the file name of the log I am looking to restore, then using execute SQL task to call the stored procedure. 

Any help would be appreciated!

Current errors that I am getting are - 

11/22/2019 2:27:23 PM: VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. Check that the SQL Server instance is running, that you have the SQL Server System Administrator server role; and that no other processes are blocking the backup or restore process; or try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\[InstanceName]

11/22/2019 2:27:23 PM: Also check that the database is not currently in use.

petey2

The error suggests that the database was in use at the time of the restore.

Have you considered using the SQL Backup features that help to automate restores of multiple transaction log files?  For e.g. something like this:

EXEC master..sqlbackup '-sql "RESTORE LOG AdventureWorks_COPY FROM DISK = [g:\backups\log\AdventureWorks_*.sqb] WITH NORECOVERY, MOVETO = [g:\backups\log\restored\], DISCONNECT_EXISTING"'

- disconnects all existing users to the AdventureWorks_COPY database (DISCONNECT_EXISTING),
- finds all files matching the AdventureWorks_*.sqb pattern in the g:\backups\log\ folder
- sorts and restores those files in the correct order
- moves each of those files to the g:\backups\log\restored\ folder after the restore so that they are not picked up to be restored again later