Question: I have recently tried a big merge of 2 databases. We recreated the schema from Database 2 into Database 1 and created a script to transfer all data from database 2 into Database 1. This script takes about 35 min to run and have transaction handling with:

```
BEGIN TRANSACTION
...
IF(@@error<>0)
    COMMIT TRANSACTION
ELSE
    ROLLBACK TRANSACTION

```

The full script is a bit sensitive but here is some SQL that have the same structure: <http://pastebin.com/GWJ3ZnkF>

We ran the script and all data was transfered without errors. We tested the systems running with the new combined database (removed access rights to the old database). 

But as a last task we wanted to take the old database offline to make sure no one used that database. To do this we used:

```
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

```

This was bad. After this line of SQL code all data in the combined database that we just copied was suddenly gone. I first asumed it wasn't really finished so the "Rollback immediate" sounds like it have performed a rollback on my transaction.. 

But why? Wasn't the transaction allready committed?

Also I tried running the same script again a few times but after every attempt no data was copied even if it said the script was successfull. I have no idea why... did it remember my offline rollback somehow? 

What is really happening to my connections?

Answer: 
Sounds like you had a pending transaction uncommitted and you forced it to rollback, loosing some of the work. The rest is explained by how your scripts are structured. Is unlikely your script had a single transaction from start to bottom. Only the last transaction was rolled back, so the database was left now in a state in which it is 'half copied'. Probably your script does various checks and this intermediate state sends the script on the 'ELSE' branches where it does not do the proper work (ie. apparently does nothing).

W/o posting the exact script, is all speculation anyway.

Right now you need to restore the database to a consistent state, the one before your data copy. Use the backup you took before the data move (you *did* take a backup, right?). for extra credit, make sure your script is [idempotent](http://en.wikipedia.org/wiki/Idempotence) and works correctly on a half-updated database.
> 
> **WITH ROLLBACK IMMEDIATE:**
> 
> 
> All incomplete transactions will be rolled back and any other
>  connections to the database will be
>  immediately disconnected.
> 
> 
> 

Sounds like someone got the 2 databases mixed up or maybe there is an outstanding transaction?.... Can you post your entire script? 

[Ref: ALTER DATABASE](http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx).

Rather than only checking `@@ERROR`, inspect `@@TRANCOUNT` as well.