restore database in SSIS using Extended stored procedure

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

restore database in SSIS using Extended stored procedure

Postby byong » Mon Nov 19, 2007 6:36 pm

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,
byong
 
Posts: 1
Joined: Mon Nov 19, 2007 6:08 pm

Postby Brian Donahue » Tue Nov 27, 2007 4:14 pm

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: Select all
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
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby wolfsvein » Thu Mar 17, 2011 1:25 pm

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.
wolfsvein
 
Posts: 1
Joined: Thu Mar 17, 2011 1:23 pm


Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests