/*Przygotowanie wzorcowej kopii bazy danych*/

use master
--BST001 N'D:\MSSQL2008R2\Backup\forMaster.bak'
--METAL N'D:\Data\Backup\forMaster.bak'
declare @CRM0001_MASTERBackupPath nvarchar(max) =N'D:\MSSQL2008R2\Backup\forMaster.bak' ---

--tworzenei aktualnej: FORMAT - nadpisanie
	BACKUP DATABASE [CRM0100_METALBUD] TO  DISK =  @CRM0001_MASTERBackupPath   WITH FORMAT;

	ALTER DATABASE [CRM0001_MASTER] SET SINGLE_USER WITH ROLLBACK IMMEDIATE --upewnic sie ze baza nei zostanei wykorzystana w trakcie zanim nei zostanie oczyszczona

	RESTORE DATABASE [CRM0001_MASTER]
	FROM  DISK =  @CRM0001_MASTERBackupPath
	WITH  FILE = 1,
	MOVE N'xCRM' TO N'D:\MSSQL2008R2\DATA\CRM0001_MASTER.mdf',
	MOVE N'xCRM_log' TO N'D:\MSSQL2008R2\DATA\CRM0001_MASTER.ldf',
	NOUNLOAD,  REPLACE,  STATS = 10

/*Oczyszczenie z danych*/
	use	 CRM0001_MASTER

	drop table #counts
	CREATE TABLE #counts
	(
		table_name varchar(255),
		row_count int
	)

	EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

	delete from #counts where
	 table_name  in (
	'[accounting].[salesconfiguration]',
	'[accounting].[warehouseconfiguration]',
	'[configuration].[applicationRole]',
	'[configuration].[component]',
	'[configuration].[configuration]',
	'[configuration].[screen]',
	'[configuration].[printout]',
	'[common].[dictionary]',
	'[mail].[mailTemplate]',
	'[user].[rolePermission]',
	'[common].[cgs]',
	'[common].[cgsparameter]',
	'[common].[xlsConfiguration]',
	'[common].[AmountInWords]',
	'[document].[documenttype]',
	'[document].[documentdefinition]',
	'[user].[group]',
	'[user].[user]',
	'[user].[rolePermission]',
	'[user].[usergroup]'
	) and row_count >0

	delete from #counts where row_count = 0
	select * from #counts
	DECLARE @table_name varchar(max)
	DECLARE @temp1 varchar(max)
	DECLARE @temp2 varchar(max)

	DECLARE MY_CURSOR CURSOR
	  LOCAL STATIC READ_ONLY FORWARD_ONLY
	FOR

	select table_name,a,row_count from (  --zapytanie ma nacelu wielokrotna probe kasowania danych ze wzgeldu na klucze obce kotre moge niepozwolic suusnac bez skasownie
		SELECT  1 a, table_name,row_count
		FROM #counts
		union all
		SELECT  2,table_name,row_count
		FROM #counts
		union ALL
		SELECT  3,table_name,row_count
		FROM #counts
	) x order by 2 ,3 desc

	OPEN MY_CURSOR
	FETCH NEXT FROM MY_CURSOR INTO @table_name,@temp1,@temp2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--Do something with Id here
		PRINT @table_name
		BEGIN TRY
			exec (N'truncate table '+ @table_name)
		END TRY
		BEGIN CATCH
			print N'ERROR '  + @table_name
		END CATCH;

		BEGIN TRY
			exec (N'delete from  '  + @table_name)
		END TRY
		BEGIN CATCH
			print N'ERROR '  + @table_name
		END CATCH;
	FETCH NEXT FROM MY_CURSOR INTO @table_name,@temp1,@temp2
	END
	CLOSE MY_CURSOR
	DEALLOCATE MY_CURSOR

	drop table #counts

/*usuniecie slownikow ktore nei sa systemowymi*/
	delete   from common.dictionary where dictionaryname not like 'system%'
	and dictionaryname not in ('itemunit','countrycode','currency','firmtype','commenttype','contactposition','language','opportunitystage','deliveryWay', 'paymentType','organisationUnit')
	ALTER DATABASE [CRM0001_MASTER] SET MULTI_USER
	DBCC SHRINKDATABASE (CRM0001_MASTER, 10);
	BACKUP DATABASE CRM0001_MASTER TO  DISK =  @CRM0001_MASTERBackupPath   WITH FORMAT;

/*SPRAWDZENIE WIELKOSCI*/
	SELECT
		'[' + s.Name + '].[' + t.NAME + ']'  AS p1,
		cast(p.rows as varchar) AS p2,
		cast(SUM(a.total_pages) * 8 as varchar) AS p3
	FROM
		sys.tables t
	INNER JOIN
		sys.indexes i ON t.OBJECT_ID = i.object_id
	INNER JOIN
		sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
	INNER JOIN
		sys.allocation_units a ON p.partition_id = a.container_id
	LEFT OUTER JOIN
		sys.schemas s ON t.schema_id = s.schema_id
	WHERE
		t.NAME NOT LIKE 'dt%'
		AND t.is_ms_shipped = 0
		AND i.OBJECT_ID > 255
		and p.rows >0
	GROUP BY
		t.Name, s.Name, p.Rows

/*
RESTORE DATABASE [PLATFORMA_JBB]
FROM  DISK =  N'D:\MSSQL2008R2\Backup\forMaster.bak'
WITH  FILE = 1,
MOVE N'xCRM' TO N'D:\MSSQL2008R2\DATA\PLATFORMA_JBB.mdf',
MOVE N'xCRM_log' TO N'D:\MSSQL2008R2\DATA\PLATFORMA_JBB.ldf',
NOUNLOAD,  REPLACE,  STATS = 10

Opcjonalnie możliwe nadpisanie danych wrażliwych

update [firm].[firm] set
firmName = firmNR + ' test',
firmNameShort =firmNR + ' test',
taxCode = '9660666980',
phonenumber = '+48 123 45 67',
streetname ='Test'

update wms.[item] set
itemname = itemcode  + ' test',
itemNameShort =itemcode + ' test',
itemNameCommercial=itemcode + ' test'

*/

/*Instalacja
select * from [user].[user]

--2. Uzytkwonik inicjalny
delete from [user].[user] where   userid not in (select top 1 userid  from [user].[user])
update  [user].[user]  set
userName='demo' ,password ='demo', passwordhash=null,
userNameShort='SYS',
[role]='sys',
defaultgroupid = (select groupid from [user].[group] where groupshortcut  ='sys'),
usernamefull='SYS', externalAuthentication =null

--3. Wszystkie GRupy i uprawnienia
delete from [user].userGroup
declare @userid uniqueidentifier = (select top 1 userid  from [user].[user])
insert into [user].userGroup (groupid,userid)
select groupid,@userid  FROM [user].[group]

--4. Ddoanie wpisu w konfiguracji

*/
