Red Gate forums :: View topic - restore database in SSIS using Extended stored procedure
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product support center
SQL Backup Previous Versions
SQL Backup Previous Versions forum

restore database in SSIS using Extended stored procedure

 Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
byong



Joined: 19 Nov 2007
Posts: 1
PostPosted: Mon Nov 19, 2007 6:36 pm    Post subject: restore database in SSIS using Extended stored procedure Reply with quote
I'm creating SSIS package to restore database using extended stored procedure format (of regate backup). The package was run successfully but the database is not restored. Do you know why?

How can I restore redgate backup in SSIS package?


Thanks,
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6348
Location: Red Gate Software
PostPosted: Tue Nov 27, 2007 4:14 pm    Post subject: Reply with quote
Hi,

I'm not well-versed in SSIS but I suspect that the restore operation most likely encountered a failure. The reason why SSIS would probably report success is because your restore script does not raise an error to SQL Server's execution engine. SQL Backup does not do this for you so it's necessary to perform error trapping and handling in your own SQL script task, for instance:
Code:
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "... backup command goes here ..."', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)  /* raises error condition */
END


To check that your restore was successful (or failed) you may want to have a look at the SQL Backup log files in the %allusersprofile%\application data\red gate\sql backup\log.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521
Back to top
View user's profile Send private message
wolfsvein



Joined: 17 Mar 2011
Posts: 1
PostPosted: Thu Mar 17, 2011 1:25 pm    Post subject: Reply with quote
This code works well, but for the SSIS side all you would need to do to capture the error is to use a ADO.net connection on your execute sql task.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group