Restore Snapshot works in SSMS not in SSIS

Question

Restore Snapshot works in SSMS not in SSIS

jon.lunn

SSC Veteran

Points: 246
More actions
December 17, 2014 at 8:18 am

#397787

Hi,
I have an SSIS package running under a proxy. the basic process is
1) Take Database Snapshot
2) Container that loads Csv Files
3) if there is an error, it calls an SP that Restores the snapshot back to how the DB was
IN SSMS the Restore snapshot works, in BIDS and when running the job via SQL agent, the SP doesn't work. The SP first sets the database into single user mode, then attempts to restore the DB.
In BIDS the SP get to the set since user mode bit, then doesn't get any further than that. It works in SSMS when running the code or the proc itself
The SP is stored in the master db. master.dbo.Restore snapshot.
I've tried added extra permission to the proxy, and it should be referencing the same windows account i use to connect to SSMS. The error i get from my error logging it that RESTORE DATABASE is terminating abnormally. I've checked google most post refer to open connections, but i'm setting the db to single user mode 'SINGLE_USER WITH ROLLBACK IMMEDIATE'
Any ideas of permissions or other issues?
thanks
Jon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sqlmunkee SSCommitted Points: 1949 More actions · Answer 1

I've always had problems with SINGLE_USER and geting the right session 🙂 If you SET OFFLINE WITH ROLLBACK IMMEDIATE, you should still be able to do the restore (but the proc would need to be in another DB) ?

[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]

jon.lunn SSC Veteran Points: 246 More actions · Answer 2

Hi Tahnks for the update,

I've tested the proc with single user mode and the offline mode, and nope it doesn;t work on the restore part

thanks

Jon

sqlmunkee SSCommitted Points: 1949 More actions · Answer 3

Have you tried splitting the proc in two, and offline then restore ?

[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]

jon.lunn SSC Veteran Points: 246 More actions · Answer 4

Thats a good idea, but when i was writing the code noticed that the SSIS package connection was for the database in question, not the Master database! changed the SQL task to point to a newly created master database connection.. bingo it worked.... now going to stand in the corner for a bit

Thanks for taking the time to respond! it's appreciated. Have a good xmas/ new year/ holidays!

Jon