/*
	Datafiles verplaatsen
	Script geschreven door Wilfred van Dijk
	http://www.wilfredvandijk.nl
	
	Voor reacties of opmerkingen mail naar wfvdijk@gmail.com

	DE SOFTWARE WORDT GELEVERD "ZOALS HET IS", ZONDER ENIGE VORM VAN GARANTIE, EXPLICIET OF IMPLICIET, 
	INCLUSIEF MAAR NIET BEPERKT TOT DE GARANTIES VAN GESCHIKTHEID VOOR EEN BEPAALD DOEL EN NIET-INBREUK. 
	IN GEEN GEVAL ZAL DE AUTEURS OF AUTEURSRECHTHOUDERS AANSPRAKELIJK VOOR ENIGE CLAIM, SCHADE OF ANDERE 
	AANSPRAKELIJKHEID, HETZIJ OP BASIS VAN CONTRACT, ONRECHT OF ANDERS, ALS GEVOLG VAN UIT OF IN VERBAND 
	MET DE SOFTWARE OF HET GEBRUIK OF ANDERE HANDELINGEN IN DE SOFTWARE.
*/

set nocount on
go

use master
go

declare @nieuwe_data_locatie varchar(256)
declare @nieuwe_log_locatie varchar(256)
declare @welkedatabase sysname
declare @rc int
declare @DOSCmd varchar(1000)
declare @SQLCmd nvarchar(1000)
create table #sqlcmd(Cmd nvarchar(max))
create table #doscmd(Cmd nvarchar(max))

-- pas onderstaande variabelen aan:

set @nieuwe_data_locatie = 'f:\temp\'
set @nieuwe_log_locatie = 'f:\temp\'
set @welkedatabase = 'wdi_biztalk'
/*
	start
*/
print '/* Datafile copy mbv OFFLINE en ONLINE */'
print ''
print '-- database: ' + @welkedatabase
print '-- datafiles worden verplaatst naar pad ' + @nieuwe_data_locatie
print '-- logfiles worden verplaatst naar pad ' + @nieuwe_log_locatie
print ''
/*
	Stap 1a: creeer SQL statements
*/
raiserror('-- Creeer SQL statements',10,1) with NOWAIT

insert 
into	#sqlcmd
select	'ALTER DATABASE '+ quotename(b.name) + ' modify file (name = "'+a.name+ '", filename = "'+ @nieuwe_data_locatie + right(a.physical_name, charindex('\', reverse(a.physical_name))-1) + '");'
from	master.sys.master_files a
join	master.sys.databases b
on		a.database_id = b.database_id
where	b.name = @WelkeDatabase
and		a.type_desc = 'ROWS'
and		a.physical_name not like @nieuwe_data_locatie + '%'
union
select	'ALTER DATABASE '+ quotename(b.name) + ' modify file (name = "'+a.name+ '", filename = "'+ @nieuwe_log_locatie + right(a.physical_name, charindex('\', reverse(a.physical_name))-1) + '");'
from	master.sys.master_files a
join	master.sys.databases b
on		a.database_id = b.database_id
where	b.name = @WelkeDatabase
and		a.type_desc = 'LOG'
and		a.physical_name not like @nieuwe_log_locatie + '%';

if (select count(*) from #sqlcmd) = 0
	begin
		print '-- Database bestaat niet, of pad klopt niet'
		set @rc = 1
		goto einde
	end
/*
	Stap 1b: Creeer copy statements
*/
raiserror('-- Creeer DOS copy statements',10,1) with NOWAIT

insert 
into	#doscmd
select	'copy "' + a.physical_name + '" "' + @nieuwe_data_locatie + '" /v'
from	master.sys.master_files a
join	master.sys.databases b
on		a.database_id = b.database_id
where	b.name = @WelkeDatabase
and		a.type_desc = 'ROWS'
and		a.physical_name not like @nieuwe_data_locatie + '%'
union
select	'copy "' + a.physical_name + '" "' + @nieuwe_log_locatie + '" /v'
from	master.sys.master_files a
join	master.sys.databases b
on		a.database_id = b.database_id
where	b.name = @WelkeDatabase
and		a.type_desc = 'LOG'
and		a.physical_name not like @nieuwe_log_locatie + '%';
/*
	Stap 2: wacht totdat database OFFLINE is
*/
print '-- Alle statements staan klaar. Zet de database nu (handmatig) OFFLINE, of breek het script nu af.'
print '' 
while not exists(select 1 from master.sys.databases where name = @welkedatabase and state_desc = 'OFFLINE')
	begin
		raiserror('-- Wachten totdat database [%s] OFFLINE is ...',10,1, @welkedatabase) with NOWAIT
		waitfor delay '00:00:15'
	end
/*
	Stap 3: Kopieer bestanden
*/
declare c_lus cursor local static for
	select * from #doscmd
	
open c_lus
fetch next from c_lus into @DOSCmd
while @@Fetch_status = 0
	begin
		raiserror(@DOSCmd,10,1) with NOWAIT
		exec @rc = master..xp_cmdshell @DOSCmd
		if @rc > 0
			begin
				print '-- FOUTMELDING tijdens kopieren'
				break
			end
		fetch next from c_lus into @DOSCmd
	end
close c_lus
deallocate c_lus

if @rc > 0
	goto einde
/*
	Stap 4: Voer wijzigingen door in SQL
*/
declare c_lus cursor local static for
	select * from #sqlcmd
	
open c_lus
fetch next from c_lus into @SQLCmd
while @@Fetch_status = 0
	begin
		raiserror(@SQLCmd,10,1) with NOWAIT
		exec (@SQLCmd)
		fetch next from c_lus into @SQLCmd
	end
close c_lus
deallocate c_lus
/*
	Stap 5: wacht totdat database ONLINE
*/
set @SQLCmd = 'ALTER DATABASE '+ QUOTENAME(@welkedatabase) + ' SET ONLINE'
raiserror(@SQLCmd,10,1) with NOWAIT
exec (@SQLCmd) 

if not exists(select 1 from master.sys.databases where name = @welkedatabase and state_desc = 'ONLINE')
	begin
		set @rc = 1
		print '-- ERROR: Database komt niet ONLINE'
	end
	
einde:

if @rc = 0
	print '-- Success! (files op oude locatie kunnen gewist worden)'	
else
	print '-- Fouten opgetreden'
		
drop table #doscmd
drop table #sqlcmd