Založení MSSQL databází pro systém Palstat

Tento script Vám pomůže se založením databází systému Palstat CAQ včetně uživatele "pal" před jeho spuštěním věnujte pozornost přečtení komentářů v konfigurační sekci na začátku souboru.

 

USE master;
GO

DECLARE @DB1 VARCHAR(200), @DB2 VARCHAR(200), @DB3 VARCHAR(200), @DB4 VARCHAR(200), @DB5 VARCHAR(200), @DBNAME VARCHAR(200), @COMMAND NVARCHAR(1000);
DECLARE @USR VARCHAR(100), @PASS VARCHAR(100), @PATH_DATA VARCHAR(200), @PATH_LOG VARCHAR(200), @RECOVERY VARCHAR(50), @COMPANYPREFIX VARCHAR(100);
DECLARE @DBS TABLE ( DBNAME VARCHAR(200));

--   ****************          SETTINGS / NASTAVENI          ****************

SET @PATH_DATA = 'D:/MSSQL_DATABASES_DATA_FOLDER/';   -- the location of MS SQL databases data files / umisteni datovych souboru MS SQL databazi
SET @PATH_LOG = 'E:/MSSQL_DATABASES_LOG_FOLDER/';     -- the location of MS SQL databases log files / umisteni logu MS SQL databazi
SET @USR = 'NAME_OF_SQL_USER';                        -- the MS SQL user used to access databases (have to always be the owner of our databases) / MS SQL uzivatel pouzivany pro pristup k databazim (musi byt vzdy vlastnikem nasich databazi)
SET @PASS = 'PASSWORD_OF_SQL_USER';                   -- the MS SQL user password / heslo MS SQL uzivatele
SET @DB1 = 'pal3g_attach';                            -- the Attachments database name / nazev databaze priloh
SET @DB2 = 'pal3g_qsd';                               -- the QSD module database name / nazev databaze modulu QSD
SET @DB3 = 'pal3g_archiv';                            -- the Archive module database name / nazev databaze modulu Archiv
SET @DB4 = 'pal3g_data';                              -- the name of the main Palstat database / nazev hlavni databaze Palstatu
SET @DB5 = 'pal3g_datatest';                          -- the name of the test database / nazev testovaci databaze Palstatu
SET @COMPANYPREFIX = '';                              -- the common prefix added to all databases / spolecna predpona pridana vsem databazim
SET @RECOVERY = 'SIMPLE';

--   ************************************************************************

IF (@COMPANYPREFIX != '') 
BEGIN
SET @DB1 = @COMPANYPREFIX + '_' + @DB1;
SET @DB2 = @COMPANYPREFIX + '_' + @DB2;
SET @DB3 = @COMPANYPREFIX + '_' + @DB3;
SET @DB4 = @COMPANYPREFIX + '_' + @DB4;
SET @DB5 = @COMPANYPREFIX + '_' + @DB5;
END

INSERT INTO @DBS VALUES (@DB1)
INSERT INTO @DBS VALUES (@DB2)
INSERT INTO @DBS VALUES (@DB3)
INSERT INTO @DBS VALUES (@DB4)
INSERT INTO @DBS VALUES (@DB5)

SET @COMMAND = 'CREATE LOGIN ' + @USR + ' WITH PASSWORD = '''+ @PASS +''',CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
EXECUTE(@COMMAND);

WHILE EXISTS (SELECT TOP 1 DBNAME FROM @DBS)
BEGIN
  SELECT TOP 1 @DBNAME = DBNAME FROM @DBS

  SET @COMMAND ='CREATE DATABASE ' + @DBNAME + '
  ON
  ( NAME = ' + @DBNAME + ', FILENAME = ''' + @PATH_DATA + @DBNAME + '.mdf'')
  LOG ON
  ( NAME = ' + @DBNAME + '_log, FILENAME = ''' + @PATH_LOG + @DBNAME + '_log.ldf'')
  COLLATE Czech_CS_AS
  ALTER DATABASE ' + @DBNAME + ' SET RECOVERY ' + @RECOVERY + ';'
  EXECUTE (@COMMAND);

  SET @COMMAND = 'ALTER AUTHORIZATION ON DATABASE::' + @DBNAME + ' TO ' + @USR + ';'
  EXECUTE (@COMMAND);

  DELETE FROM @DBS WHERE DBNAME = @DBNAME
END