use SQLDBControl;
go

create procedure MoveDatabase
(
	@server_db_id		int, 
	@project_server_id	int --destination server
)

as

set nocount on;

declare @new_server_name varchar(128);
declare @original_server varchar(128);
declare @original_db varchar(128);
declare @error int;

select @new_server_name = ServerName
from ProjectServer
where ProjectServerId = @project_server_id;

select @original_server = ServerName, @original_db = DatabaseName
from ProjectServerDatabase psd
	join ProjectServer ps on ps.ProjectServerId = psd.ProjectServerId
where ServerDbId = @server_db_id;

begin tran

	update ProjectServerDatabase
	set ProjectServerId = @project_server_id
	where ServerDbId = @server_db_id;
	
	select @error = @@error
	if @error <> 0
	  begin
		if @@trancount > 0 rollback tran;
		return;
	  end
	
	update DatabaseObject
	set ServerName = @new_server_name
	where ServerName = @original_server
	and DatabaseName = @original_db;
	
	select @error = @@error
	if @error <> 0
	  begin
		if @@trancount > 0 rollback tran;
		return;
	  end
	
	update DeployTarget
	set ServerName = @new_server_name
	where ServerName = @original_server
	and DatabaseName = @original_db;
	
	select @error = @@error
	if @error <> 0
	  begin
		if @@trancount > 0 rollback tran;
		return;
	  end
	
	update ProjectUserQuery
	set ServerName = @new_server_name
	where ServerName = @original_server
	and DatabaseName = @original_db;
	
	select @error = @@error
	if @error <> 0
	  begin
		if @@trancount > 0 rollback tran;
		return;
	  end
	
	update PackageSearch
	set ServerName = @new_server_name
	where ServerName = @original_server
	and DatabaseName = @original_db;
	
	select @error = @@error
	if @error <> 0
	  begin
		if @@trancount > 0 rollback tran;
		return;
	  end
	
	update ScriptConnection
	set ServerName = @new_server_name
	where ServerName = @original_server
	and DatabaseName = @original_db;
	
	select @error = @@error
	if @error <> 0
	  begin
		if @@trancount > 0 rollback tran;
		return;
	  end
	
	update LinkedDatabase
	set ServerName = @new_server_name
	where ServerName = @original_server
	and DatabaseName = @original_db;
	
	select @error = @@error
	if @error <> 0
	  begin
		if @@trancount > 0 rollback tran;
		return;
	  end
	
	update ReferenceDatabase
	set ReferenceServer = @new_server_name
	where ReferenceServer = @original_server
	and ReferenceDatabase = @original_db;
	
	select @error = @@error
	if @error <> 0
	  begin
		if @@trancount > 0 rollback tran;
		return;
	  end

commit tran

go

grant execute on MoveDatabase to [SQLDBControl_Role]

go