-- move all mydbs from one machine to another - this needs to be run on the batch admin system
-- the machines have not been parameterised so the two copy commands would need to be chanegd 
-- for this to work on another machine

declare idcursor cursor for select webservicesid from batchadmin.batch.users where mydbhost like '%004%'
open idcursor

declare @count int
set @count=0;
declare @theid varchar(20);

-- grab ids from the user table 
fetch next from idcursor into @theid 
print 'Starting loop .. '
while (@@FETCH_STATUS=0 ) 
begin
	set @count = @count +1;
	
	declare @mydb varchar(50);
	declare @cmd VARCHaR(200);
	declare @localpath varchar(200);
	declare @chng nvarchar(500);
	declare @stat int;
	
	
	
	set @mydb = 'mydb_'+@theid;
	set @localpath = 'd:\sql_db\mydb\'+@mydb
	
	set @chng =  'use '+@mydb+'; EXEC dbo.sp_change_users_login ''Update_One'', ''test'',''test'''
	
	print 'Detaching '+@mydb 

	exec sdsssql004.master.dbo.sp_detach_db @mydb
	
	print 'copying files  ' 	
	set @cmd='copy \\sdsssql004\d$\sql_db\mydb\'+@mydb+' d:\sql_db\mydb\'
	print @cmd
	exec  @stat=xp_cmdshell @cmd
        if (@stat = 1) 
		print 'Failed to detach '+@mydb
	else 
	begin
		
		set @cmd='copy \\sdsssql004\d$\sql_db\mydb\'+@mydb+'_log.ldf d:\sql_db\mydb\'
		print @cmd
		exec xp_cmdshell @cmd
		
		print 'Attaching ' + @localpath 
		exec sp_attach_db @mydb, @localpath
	
		print 'Changeing mydb host for '+@theid	
		update batchadmin.batch.users set mydbhost= 'SQL001DBHost' where webservicesid=@theid
		
		print 'Link up test user  with login '+@chng
		exec sp_executesql @chng

	end 	
	print ' Done '+cast(@count as varchar(10))
	print ' ' 
	fetch next from idcursor into @theid 

end 
close idcursor
deallocate idcursor
