186 lines
4.1 KiB
Transact-SQL
186 lines
4.1 KiB
Transact-SQL
/*
|
|
Server: samnb705db01.amavita.ch
|
|
Format: GCM
|
|
Business: TPPHAR
|
|
type: VALI
|
|
Version: 24.1.21007.00072
|
|
|
|
08.04.2024, TSC
|
|
*/
|
|
USE master
|
|
|
|
SET XACT_ABORT ON;
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @dbName NVARCHAR(255);
|
|
DECLARE @query_bkp NVARCHAR(MAX)='';
|
|
DECLARE @query_rest NVARCHAR(MAX)='';
|
|
DECLARE @query_perms NVARCHAR(MAX)='';
|
|
DECLARE @dump NVARCHAR(255);
|
|
DECLARE @users TABLE (login VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL);
|
|
INSERT INTO @users ([login],
|
|
[name])
|
|
VALUES
|
|
('svc-delphix-masking','sql login'),
|
|
('CENTRALINFRA\ua253440', 'Oliver'),
|
|
('CENTRALINFRA\ua253450', 'Franck'),
|
|
('CENTRALINFRA\ua210510', 'Wyn');
|
|
|
|
DECLARE @tplRight NVARCHAR(MAX) = N'
|
|
USE @dbName@_delphix
|
|
BEGIN TRY
|
|
CREATE USER [@login@] FOR LOGIN [@login@];
|
|
ALTER ROLE [db_owner] ADD MEMBER [@login@]
|
|
END TRY
|
|
BEGIN CATCH
|
|
ALTER ROLE [db_owner] ADD MEMBER [@login@]
|
|
END CATCH
|
|
';
|
|
|
|
Declare @DataDir nvarchar(4000),
|
|
@LogDir nvarchar(4000),
|
|
@BakDir nvarchar(4000),
|
|
@FTDir nvarchar(4000)
|
|
|
|
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
|
|
N'Software\Microsoft\MSSQLServer\MSSQLServer',
|
|
N'DefaultData',
|
|
@DataDir output,
|
|
'no_output'
|
|
|
|
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
|
|
N'Software\Microsoft\MSSQLServer\MSSQLServer',
|
|
N'DefaultLog',
|
|
@LogDir output,
|
|
'no_output'
|
|
|
|
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
|
|
N'Software\Microsoft\MSSQLServer\MSSQLServer',
|
|
N'BackupDirectory',
|
|
@BakDir output,
|
|
'no_output'
|
|
|
|
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
|
|
N'Software\Microsoft\MSSQLServer\MSSQLServer',
|
|
N'FullTextDefaultPath',
|
|
@FTDir output,
|
|
'no_output'
|
|
|
|
DECLARE csrDb CURSOR FAST_FORWARD READ_ONLY FOR
|
|
SELECT [d].[name]
|
|
FROM sys.databases d
|
|
WHERE LOWER([d].[name]) IN ( 'arizona', 'arizonarep', 'hcitools', 'arizonacust', 'activepos_read', 'activepos_write' );
|
|
|
|
OPEN csrDb;
|
|
|
|
FETCH NEXT FROM csrDb
|
|
INTO @dbName;
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SET @dump = @dbName+'_dump';
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM sys.[backup_devices]
|
|
WHERE name = @dump
|
|
)
|
|
BEGIN
|
|
RAISERROR ('No dump device named %s exists', 16, 1, @dump);
|
|
END
|
|
ELSE
|
|
IF @@SERVERNAME IN ('SWAMA707VM01\APSSQL','SWSUN004VM01\APSSQL')
|
|
BEGIN
|
|
SET @dump = replace('DISK = N''g:\@dbName@.bak''','@dbName@',@dbname);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @dump = '['+@dbName+'_dump]';
|
|
END
|
|
BEGIN
|
|
SET @query_bkp = @query_bkp + REPLACE(REPLACE('
|
|
BACKUP DATABASE [@dbname@] TO @dump@ WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N''@dbname@-Full Database Backup'', COMPRESSION, SKIP, NOREWIND, NOUNLOAD, STATS = 10;
|
|
'
|
|
,'@dbname@'
|
|
,@dbName)
|
|
|
|
,'@dump@'
|
|
,@dump
|
|
);
|
|
|
|
DECLARE @file_row VARCHAR(255);
|
|
DECLARE @file_log VARCHAR(255);
|
|
|
|
SELECT @file_row = name
|
|
FROM sys.master_files F
|
|
WHERE DB_NAME([F].[database_id]) = @dbName
|
|
AND type_desc = 'ROWS'
|
|
|
|
SELECT @file_log = name
|
|
FROM sys.master_files F
|
|
WHERE DB_NAME([F].[database_id]) = @dbName
|
|
AND type_desc = 'LOG'
|
|
|
|
IF @@SERVERNAME IN ('SWTSTDB01\APSSQL')
|
|
BEGIN
|
|
SET @dataDir = 'e:\SQLDatabase\';
|
|
END
|
|
|
|
SET @query_rest = @query_rest +REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
|
|
RESTORE DATABASE [@dbname@_delphix] FROM @dump@ WITH FILE = 1, REPLACE, MOVE N''@file_row@'' TO N''@dataDir@\@dbname@_delphix.mdf'', MOVE N''@file_Log@'' TO N''@logDir@\@dbname@_delphix_1.ldf'', NOUNLOAD, STATS = 5;
|
|
ALTER DATABASE [@dbname@_delphix] SET RECOVERY SIMPLE WITH ROLLBACK IMMEDIATE;
|
|
'
|
|
,'@dbname@'
|
|
,@dbName)
|
|
|
|
,'@dump@'
|
|
,@dump)
|
|
|
|
,'@file_Log@'
|
|
,@file_log)
|
|
|
|
,'@file_row@'
|
|
,@file_row
|
|
)
|
|
|
|
,'@dataDir@'
|
|
,@dataDir
|
|
)
|
|
|
|
,'@logDir@'
|
|
,@logDir
|
|
)
|
|
;
|
|
|
|
--permissions
|
|
SELECT @query_perms = @query_perms + REPLACE(REPLACE(
|
|
@tplRight
|
|
,'@dbName@'
|
|
,@dbName
|
|
)
|
|
,'@login@'
|
|
,u.[login]
|
|
)
|
|
FROM @users u
|
|
|
|
FETCH NEXT FROM csrDb
|
|
INTO @dbName;
|
|
END
|
|
END
|
|
|
|
CLOSE csrDb;
|
|
DEALLOCATE csrDb;
|
|
|
|
|
|
PRINT @query_bkp;
|
|
--PRINT '--------------------------------'
|
|
--PRINT @query_rest
|
|
--PRINT '--------------------------------'
|
|
--PRINT @query_perms
|
|
|
|
--EXEC(@query_bkp)
|
|
--EXEC(@query_rest)
|
|
--EXEC(@query_perms)
|
|
|
|
|